You might have to script the export to "transform" the records into a more normalized format in a temporary table(s) and export them from there.
There are 3 approaches to this
1 combine the repetitions as seperate fields in your extract, You would do this by setting up a temp table with a seperate field for each repetition and setting those fields to the coresponding repetitions. (In this case if the repetitions are empty you will have empty fields)
2 creating a seperate "child" table and create linked records for each repetition that has a value. (In this case you would need to populate a field with the orginal records primary key.
3 is a hybrid of the two where you create new records duplicating all other values except the repeating ones. (In this case for each new repeating field value you write out the entire record.
Thank you for your response aammondd. Can you post any sample code for the various choices. How do I create a temp table in FM?
What's the format you need to see for this in the excel spread sheet? One repetition in each row or one repetition in each column?
a temp table is a just the same as any table of field names in Manage Database I call it a temp table because the data is only in it for a temorary time.
Dear Phil and AAmmondd,
I am new with filemaker and would appreciate anything I can get. So, for the repetition, if there is no content, I could ignore it. So, I would want the list of the array which has data in it. Do I create a table in the script and copy the contents and then export that table? Do I have to copy field by field into the new table with the primary key. When you say temp table, can I just create it in the script and dump it after I am done exporting? Or do I need to create the table using ManageDatabase...... menu option?
Thanks for all your help.
The table is created in Manage Database.
Ill get a psuedo code script in a bit.
Since your are new to FileMaker, consider replacing your repeating field with a table of related records. This can make your export to excel simpler--depending on the format you want to see in Excel--as well as being much easier to work with in many other ways within your databaswe.
If you want to see an Excel spread sheet, with one row of data for every repetition in your array, replacing the array with a related table means you can export the data without any temporary table used to make it happen--a much simpler option for you here.
Phil is correct. It is the better way for your database in general.
Thank you all for your help. Even though I am new to Filemaker, the database exists and I am not planning on changing it. So, I will have to go with the option of retrieving only those fields and inserting them into an other excel spread sheet. I will look for the pseudo script.....
Are you unable to add a new table to the database. Adding an export table and creating a script would work for you without changing any of your existing functionality.
You would use 2 loops
A loop of the found set of records
Set Variable [$L1; Value= 0]
Set Variable [$L2; Value = 0]
Goto layout [temptable]
Show All records
Delete All Records
Goto Layout [Maintable]
Perform main find // the main records to be exported.
Set Variable [$Field1; Value = MainTable::Field1] //repeated for each non-repeating field in the record
Set Variable [$L2; Value = $L2 +1]
If [GetRepetition(fieldname;$L1) <> ""]
Set Variable [$RepeatingField; Value = GetRepetition(fieldname;$L1)]
Goto Layout [Temptable]
Set Field [ Temptable:Field1; $Field1] //Repeated for all fields
Goto Laoyt [Original]
Exit Loop if $L2 > //max number of repetitions
Exit Loop if $L1 > Get(Foundcount)
Set Variable[$L1; Value = $L1 +1]
this is very rough psuedo code so dont take it as gospel.
Phil might have a better solution
This will write out one complete record for each repetition.
The alternative is to create a temptable with a field for each repetition and when looping through the repetitions set each temptable field accordingly.
Thank you very much.
Just so you know, converting a repeating field into a related table is one of the easier changes you can make to your database structure. FileMaker provides a very handy tool you can use with Import records to pull the data from the repeating field into new records in the related table, separated into individual records for each repetition.