Hello Reuven! do you have other fields that come in with this import? for example, there might be a table for the import (all fields) and then a related table (one record for each of the colors). Or do you want to repeat the other fields in each new record?
for each unique record ID, I am looking for a straightforward way to generate the number of records necessary to have only one color in the "Color" field.
Do you want to generate them in Filemaker? Or only when exporting?
You might substitute the commas for linefeeds and use GetValue() and ValueCount() as drivers in a loop.
eg. some freehand code - quite untested but should give you the idea
SetVariable [$Colours; substitute(table::colour;","; "¶")] //swap commas for pilcrows
SetField[table::colour; GetValue($Colours;1)] //fix the original record
SetVariable[$Loops; ValueCount($Colurs)] // get a count of the # of colours
If($Loops > 1)
SetVariable[$Count;$Count + 1]
ExitLoopIf[ $Count > $Loops ]
I just want to repeat the Unique ID for the record and the color.
1 of 1 people found this helpful
you need to write two scripts:
the first scripts extracts the color values step by step for one record, by creating records in a separate table
the second script loops through all the records of your original table and calls the first script
We do something similarly to create a master table of authors for books that we have. In our case, author names are separated by semicolons, and there are additional tags if authors are editors, PhD advisors etc. So, in essence, our first script does the following:
store the author field in variable $work and add an additional ; separator at the end ; in addition do some clear up of carriage returns, wrong separators etc. using TrimAll and Substitute functions
remove the tags (in your case not needed)
Extract from $work the first author name using Left() and Position() of the first separator
Create the author record (in our case, more difficult, since it may already exist, there may be variants, order of first names may be important, etc. So we work with a join table and two find steps (precise and fuzzy) since there is a m:n relation between titles and authors.)
Store the remainder after the first separator in $work
if $work is empty (or has a given length), exit loop
This gives you the general idea, and there are other ways (e.g. substituting the commas by carriage returns and then using LeftValues()). YMMV. It all depends on the quality and information content of the data; colors may be easier than authors.
Of course you can put everything into one script, but my experience has shown that it is better to have this modular.
Dr. Martin Braendle
Informationszentrum Chemie Biologie Pharmazie
HCI G 5.3
+41 44 632 29 48 Telefon
+41 44 633 12 87 Fax
Datum: Wed, 15 Feb 2012 07:09:45 -0800
Betreff: exporting values separated by commas in a single field
Thanks very much Stephen and Martin. I'll try your suggesions.