Please describe this in more detail. Do you have a set of fields or a repeating field that should receive this data or separate records in a related table. If a related table, what is the relationship you have defined between them?
Hi Phil,Thanks for the response, the imported excel file has couple of columns that has multiple data within the cell similar to the example above and when imported to filemaker it place it the same way. I'm a novice to filemaker and i cant seems to find a way separate these data without creating a database from scratch and re-entering these numbers manually. Creating the fields relationship in filemaker, I think I can figure out when I separate these data.Thank you in advance,Cesar
Yes, but how do you want to "separate these data"?
If you want to put the data in a related table (The most flexible approach), you'll need a field in your first table that serves as a primary key. Then we can devise a script that takes the comma delimitted data, loops through it and uses one value from this list and the current record's primary key to create a related record for this data.
If you want to use several fields defined separately in the same record of the same (not how I'd do it, but I don't know all the details of your design either), a similar script can put one such value in each field.
And a slightly different script could load repetitions of the same repeating field, but I don't recommend that approach as a related table is almost always a better way to do this.
Here's how to do this in order to move the imported values into records in a related table.
Define a field in the table into which you will import the data that auto-enters a serial number. I'll call it _PrimaryKey here. When you import the data into this table, be sure to enable the auto-enter option so that a new serial number is assigned to each new record. If, for any reason, you can't/don't do this, you can use Replace Field contents with the serial numbers option to put values in this field immediately after the import or for any data that you have already imported into your table.
Define a new table, (I'll call it RelatedDate and your first table Maindata), with at least two fields: _ForeignKey and Value.
Link your two tables in this relationship in Manage | Database | Relationships:
MainData::_PrimaryKey = RelatedData::_ForeignKey
Now you can run this script after importing the data:
Go To Record [first]
Set Variable [$I ; Value: 1 ]
Set Variable [$Values ; Substitute ( MainData::Values ; "," ; ¶ ) ] //Values is the field that recieves the list of values from Excel
Exit Loop If [ $I > ValueCount ( $Values ) ]
Set Variable [ $Pkey ; value: MainData::_PrimaryKey ]
Go To Layout [ RelatedData (RelatedData) ]
Set Field [RelatedData::_ForeignKey ; $Pkey ]
Set Field [RelatedData::value ; GetValue ( $Values ; $I ) ]
Go To Layout [Original Layout]
Go To Record [ Next ; Exit after last ]
I'm sure I had asked this question but it must have been lost ...
In Excel, what is the relationship between a row and these comma-separated values (3416, 3572, 6708, 1245 ). Also, what do these numbers represent.