filemaker, converting to 'repeating' fields

converting to 'repeating' fields

Author	Message
Gareth Joh #1 / 10	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

Andy Schwarc #2 / 10	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

Steven Scharf. SCS Media Servic #3 / 10	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

BF #4 / 10	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

Andy Schwarc #5 / 10	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

Andy Schwarc #6 / 10	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

Gareth Joh #7 / 10	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

Glenn Schwand #8 / 10	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

BF #9 / 10	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

BF #10 / 10	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

Page 1 of 1

[ 10 post ]