converting to 'repeating' fields 
Author Message
 converting to 'repeating' fields

I have established a database from imported (tab-separated) values that
contains records of a store's sales transactions. Each transaction is
one record. In one (non-repeating) field - call it A - I list the items
bought, separated by return characters. In another (B), the quantity of
each; in the next (C) the price of each, and in another (D) the sales
value of each.

So each of these fields contains a column of numbers or text. The
database is not required to calculate the sales values - these were
supplied to me in the original text file.

What I can't do is calculate the total sales value of the transaction
(i.e. the total of the numbers in column D). I won't do this manually
because there are 6,800 records.

I believe that I need to reorganise the data so that field D (at least)
is a repeating field. Then I can use the 'sum' function.

But I cannot figure out whether and how it is possible to reconfigure
this database (once populated) so that each item in (D) is a separate
repetition, instead of merely appearing on a separate line with a return
between lines. Am I missing something really simple?

If not, can I still export field D, manipulate it externally and then
re-import it so that it will pop[ulate a newly created repeating field
E? What would the required separators be, for the external file to fill
into the repeating field successfully?

Any help much appreciated...TIA

Gareth John



Sun, 24 Feb 2002 03:00:00 GMT
 converting to 'repeating' fields

Quote:

> I have established a database from imported (tab-separated) values that
> contains records of a store's sales transactions. Each transaction is
> one record. In one (non-repeating) field - call it A - I list the items
> bought, separated by return characters. In another (B), the quantity of
> each; in the next (C) the price of each, and in another (D) the sales
> value of each.

> So each of these fields contains a column of numbers or text. The
> database is not required to calculate the sales values - these were
> supplied to me in the original text file.

> What I can't do is calculate the total sales value of the transaction
> (i.e. the total of the numbers in column D). I won't do this manually
> because there are 6,800 records.

> I believe that I need to reorganise the data so that field D (at least)
> is a repeating field. Then I can use the 'sum' function.

> But I cannot figure out whether and how it is possible to reconfigure
> this database (once populated) so that each item in (D) is a separate
> repetition, instead of merely appearing on a separate line with a return
> between lines. Am I missing something really simple?

> If not, can I still export field D, manipulate it externally and then
> re-import it so that it will pop[ulate a newly created repeating field
> E? What would the required separators be, for the external file to fill
> into the repeating field successfully?

> Any help much appreciated...TIA

> Gareth John

Hi Gareth!

I would export the repeating filed to a new related FileMaker Pro file, splitting the repeating fields to different records. This will make the calculations children's play.

Hope this will help!

Andy



Mon, 25 Feb 2002 03:00:00 GMT
 converting to 'repeating' fields
Andy you need to read the original more carefully.  He does not have a
repeating field, he wants to create one for field populated as a list ie:

Field A      Field B            Field C          Field D.
Product 1  Quanity of P1   Price of P1    Sales of P1*
Product 2  Quanity of P2   Price of P2    Sales of P2*
Product 3  Quanity of P3   Price of P3    Sales of P3*
Product 4  Quanity of P4   Price of P4    Sales of P4*

*I assume he means the quanity x price

All of this being in one record with returns after each number.  Gareth if this
is not correct, please let us know what you meant.

Gareth,

I would first create a serial number field if there is not one already.  Then
export it and the four field to a tab delimited file.  You will then have to go
into it in a wordprocessor and massage them in to this:

Serial No.[tab]Product 1[tab]Quanity of P1[tab]Price of P1[tab]Sales of P1

Then import it into a new file and relate that back to your main database.

If you are still confused feel free to call me.

Steven Scharf
SCS Media Services
57 East 11th Street, 9th Floor
New York, New York 10003
212-822-8555

Andy replied :

Hi Gareth!

I would export the repeating filed to a new related FileMaker Pro file,
splitting the repeating fields to different records. This will make the
calculations children's play.

Hope this will help!

Andy

Quote:

> I have established a database from imported (tab-separated) values that
> contains records of a store's sales transactions. Each transaction is
> one record. In one (non-repeating) field - call it A - I list the items
> bought, separated by return characters. In another (B), the quantity of
> each; in the next (C) the price of each, and in another (D) the sales
> value of each.

> So each of these fields contains a column of numbers or text. The
> database is not required to calculate the sales values - these were
> supplied to me in the original text file.

> What I can't do is calculate the total sales value of the transaction
> (i.e. the total of the numbers in column D). I won't do this manually
> because there are 6,800 records.

> I believe that I need to reorganise the data so that field D (at least)
> is a repeating field. Then I can use the 'sum' function.

> But I cannot figure out whether and how it is possible to reconfigure
> this database (once populated) so that each item in (D) is a separate
> repetition, instead of merely appearing on a separate line with a return
> between lines. Am I missing something really simple?

> If not, can I still export field D, manipulate it externally and then
> re-import it so that it will pop[ulate a newly created repeating field
> E? What would the required separators be, for the external file to fill
> into the repeating field successfully?

> Any help much appreciated...TIA

> Gareth John



Mon, 25 Feb 2002 03:00:00 GMT
 converting to 'repeating' fields
I suggest you post a few records of the raw data.

A tab delimited file cannot have the format you describe; (multiple values
within one field separated by returns) Returns are the record delimiters. So
somehow you seem to have made matters worse by how you handled the data during
import.

What does the raw data look like? How exactly did the columns of data get
created in the first place?



Mon, 25 Feb 2002 03:00:00 GMT
 converting to 'repeating' fields


Quote:
> Andy you need to read the original more carefully.  He does not have a
> repeating field,

OK, so before using the Repeating field technique, just put the data into a repeating field first. One extra step is required but the idea is clear (hopefully anyway).

Andy

Quote:
> he wants to create one for field populated as a list ie:

> Field A      Field B            Field C          Field D.
> Product 1  Quanity of P1   Price of P1    Sales of P1*
> Product 2  Quanity of P2   Price of P2    Sales of P2*
> Product 3  Quanity of P3   Price of P3    Sales of P3*
> Product 4  Quanity of P4   Price of P4    Sales of P4*

> *I assume he means the quanity x price

> All of this being in one record with returns after each number.  Gareth if this
> is not correct, please let us know what you meant.

> Gareth,

> I would first create a serial number field if there is not one already.  Then
> export it and the four field to a tab delimited file.  You will then have to go
> into it in a wordprocessor and massage them in to this:

> Serial No.[tab]Product 1[tab]Quanity of P1[tab]Price of P1[tab]Sales of P1

> Then import it into a new file and relate that back to your main database.

> If you are still confused feel free to call me.

> Steven Scharf
> SCS Media Services
> 57 East 11th Street, 9th Floor
> New York, New York 10003
> 212-822-8555

> Andy replied :

> Hi Gareth!

> I would export the repeating filed to a new related FileMaker Pro file,
> splitting the repeating fields to different records. This will make the
> calculations children's play.

> Hope this will help!

> Andy


> > I have established a database from imported (tab-separated) values that
> > contains records of a store's sales transactions. Each transaction is
> > one record. In one (non-repeating) field - call it A - I list the items
> > bought, separated by return characters. In another (B), the quantity of
> > each; in the next (C) the price of each, and in another (D) the sales
> > value of each.

> > So each of these fields contains a column of numbers or text. The
> > database is not required to calculate the sales values - these were
> > supplied to me in the original text file.

> > What I can't do is calculate the total sales value of the transaction
> > (i.e. the total of the numbers in column D). I won't do this manually
> > because there are 6,800 records.

> > I believe that I need to reorganise the data so that field D (at least)
> > is a repeating field. Then I can use the 'sum' function.

> > But I cannot figure out whether and how it is possible to reconfigure
> > this database (once populated) so that each item in (D) is a separate
> > repetition, instead of merely appearing on a separate line with a return
> > between lines. Am I missing something really simple?

> > If not, can I still export field D, manipulate it externally and then
> > re-import it so that it will pop[ulate a newly created repeating field
> > E? What would the required separators be, for the external file to fill
> > into the repeating field successfully?

> > Any help much appreciated...TIA

> > Gareth John



Mon, 25 Feb 2002 03:00:00 GMT
 converting to 'repeating' fields

Quote:


> > Andy you need to read the original more carefully.  He does not have a
> > repeating field,

> OK, so before using the Repeating field technique, just put the data into a repeating field first. One extra step is required but the idea is clear (hopefully anyway).

> Andy

Oops, may not be clear...  to move the data from a non repeating field such as "globaltemp" to a repeating filed, you may want to use John Mark's technique which you can get from the Resources section of; http://www.databasepros.com/index.html
Just perform a find on; "to repeating"

Hope this will help!

Andy

Quote:

> > he wants to create one for field populated as a list ie:

> > Field A      Field B            Field C          Field D.
> > Product 1  Quanity of P1   Price of P1    Sales of P1*
> > Product 2  Quanity of P2   Price of P2    Sales of P2*
> > Product 3  Quanity of P3   Price of P3    Sales of P3*
> > Product 4  Quanity of P4   Price of P4    Sales of P4*

> > *I assume he means the quanity x price

> > All of this being in one record with returns after each number.  Gareth if this
> > is not correct, please let us know what you meant.

> > Gareth,

> > I would first create a serial number field if there is not one already.  Then
> > export it and the four field to a tab delimited file.  You will then have to go
> > into it in a wordprocessor and massage them in to this:

> > Serial No.[tab]Product 1[tab]Quanity of P1[tab]Price of P1[tab]Sales of P1

> > Then import it into a new file and relate that back to your main database.

> > If you are still confused feel free to call me.

> > Steven Scharf
> > SCS Media Services
> > 57 East 11th Street, 9th Floor
> > New York, New York 10003
> > 212-822-8555

> > Andy replied :

> > Hi Gareth!

> > I would export the repeating filed to a new related FileMaker Pro file,
> > splitting the repeating fields to different records. This will make the
> > calculations children's play.

> > Hope this will help!

> > Andy


> > > I have established a database from imported (tab-separated) values that
> > > contains records of a store's sales transactions. Each transaction is
> > > one record. In one (non-repeating) field - call it A - I list the items
> > > bought, separated by return characters. In another (B), the quantity of
> > > each; in the next (C) the price of each, and in another (D) the sales
> > > value of each.

> > > So each of these fields contains a column of numbers or text. The
> > > database is not required to calculate the sales values - these were
> > > supplied to me in the original text file.

> > > What I can't do is calculate the total sales value of the transaction
> > > (i.e. the total of the numbers in column D). I won't do this manually
> > > because there are 6,800 records.

> > > I believe that I need to reorganise the data so that field D (at least)
> > > is a repeating field. Then I can use the 'sum' function.

> > > But I cannot figure out whether and how it is possible to reconfigure
> > > this database (once populated) so that each item in (D) is a separate
> > > repetition, instead of merely appearing on a separate line with a return
> > > between lines. Am I missing something really simple?

> > > If not, can I still export field D, manipulate it externally and then
> > > re-import it so that it will pop[ulate a newly created repeating field
> > > E? What would the required separators be, for the external file to fill
> > > into the repeating field successfully?

> > > Any help much appreciated...TIA

> > > Gareth John



Mon, 25 Feb 2002 03:00:00 GMT
 converting to 'repeating' fields

Many thanks to Andy, Steven and BFR for your insights and help. I've
solved the problem for now, by adopting the (seems to me) dumber route
of defining a separate field for every line and column on the sales
transaction. This meant building a 4x60 array of individual fields.

It works, but it is deeply inelegant.

Thanks again,

Gareth John



Mon, 25 Feb 2002 03:00:00 GMT
 converting to 'repeating' fields
I highly doubt they intended for you to create 240 new fields.  That
certainly is "deeply inelegant".  What you need is a separate related
database with the line items fields (I assume the 4 from your 4x60 array)
along with a key field to base the relationship upon (the sales order
number?).  You then have 1 sales order record in your main database and 1
related record in the line items database for each line in the sales order.



:
: Many thanks to Andy, Steven and BFR for your insights and help. I've
: solved the problem for now, by adopting the (seems to me) dumber route
: of defining a separate field for every line and column on the sales
: transaction. This meant building a 4x60 array of individual fields.
:
: It works, but it is deeply inelegant.
:
: Thanks again,
:
: Gareth John
:



Mon, 25 Feb 2002 03:00:00 GMT
 converting to 'repeating' fields
If you would actually answer the questions I asked, and show us example data
(even dummied up with false information) , it would be possible to help you
come up with a better solution.

For instance, this does the whole job, if in fact the values in each field are
return separated.

But I still want to know what the original data looked like, as I said in my
previous comment.

Bruce Robertson

tell application "FileMaker Pro"
        set AppleScript's text item delimiters to return

        copy field "Source" to temp
        copy {} to target
        repeat with x in temp
                copy text items of x to end of target
        end repeat
        copy target to field "Target"
end tell



Tue, 26 Feb 2002 03:00:00 GMT
 converting to 'repeating' fields
The same thing works for portals also, with a very slight syntax change:

tell application "FileMaker Pro"
        set AppleScript's text item delimiters to return

        copy field "Source" to temp
        copy {} to target
        repeat with x in temp
                copy text items of x to end of target
        end repeat
        copy target to field "RelationName::Target"
-- where the portal is set to allow creation
-- of related records
end tell

I 'm sending you example files by email.



Tue, 26 Feb 2002 03:00:00 GMT
 
 [ 10 post ] 

 Relevant Pages 

1. Can't change # of repetitions in a repeating field back to its original number

2. Lookup's, Calculations & Repeating Fields

3. calculating deltas between repeating fields (status(currentRepetitionNumber) doesn't help)

4. Moving Items to Repeating Field - But there's a catch

5. How to convert a 'text' field to an 'inet' field

6. converting related fields to repeating fields

7. 'Conditional Lookups' and 'Repeating Field Calculations'

8. type field ''money''

9. Incrementing Next Field's Value Based Upon Prior Field's Value

10. **************!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Help me !!!!!!!!!!!!!!!!!!!!!!!!'''''''''''''''''''''''*************

11. DAO 2.5 won't convert zero length string to null in date field

12. need to convert a fpw 2.6 dbf with memo fields to Access '95


 
Powered by phpBB® Forum Software