Export Repeating Fields to Excel!
Oops never mind
I found the solution - Yea
After I gave up - I found it
Here it is below if some of you don't know and could not find the answer.
Some file formats (like SYLK, WKS, and DBF) can interpret only one value per field and do not support repeating fields. When you export to these formats, only the first value in a repeating field is exported. An alert message warns you if you're exporting repeating fields to a file format that supports only the first value.
Although you can export repeating fields to XML, if you export related fields that repeat, only the first value in each related repeating field is exported. An alert message warns you if you're exporting related repeating fields to a file format that supports only the first value.
You can work around these limitations by following these steps.
To export repeating field values into formats that don't support repeating fields:
1. In the FileMaker Pro file, find the records you want to export.
2. Make a clone of the file to export.
3. Open the clone, then import the data from the original file.
4. In the Additional Import Options dialog box, select Splitting them into separate records, then click OK.
5. Export from this clone file to SYLK, WKS, and DBF formats.
That works, but I'd just import the data into a new table with the "new" option specified for the target table into my existing file instead of cloning the entire file.
After I've done this once, I can keep that new table for any future exports of this data.
But I'd also take a close look at replacing the repeating field with a table of related records as that can be a better way to go than a repeating field anyway and that also nicely solves this issue with exporting your data.
I could see how that would be a much more graceful way to handle that, keeping it all in one file.
However, I not sure how you handle that. Do i display the repeating feilds as a portal and if so how do you show all of them on one record?
This is how the original file looks and then after importing them into a clone it duplicated the non repeating feidls (date, Task difference)
The date was not a problem and good to have that brought over - the Task Diffeence messes up the sum field in FMP and Excel
Screen shot of Original and Import - hopefully they will load - thanks
Here is the 2nd screen shot of what it looks like after it split imports into a clone - you can see the total gets off becasue of the duplicates
Yes, a portal is often used to take the place of the repetitions of a repeating field.
To move your data from a repeating field into a table of related records:
I really appreciate this.
I will give that a try - so does that mean in general you don't want repeating fields?
It's important to understand the strengths and weaknesses of any given design approach. 9 times out of 10 a set of related records is a better option than repeating fields. There's just a lot more you can do with a set of related records in in far simpler fashion.
I Set up the portal procedure as you mentioned above and have done this succesully before to to other files.
But for some reason it is only showing 1 record in the portal when there are several related ones.
I have a unique ID that matches in each table and that is how it is related.
I have the original table "payments" with a primary key field "AW ID" number field set to auto-enter, serial number - this has 62 recores with id of 1-61
I have the new table "Adjustments" with the imported records that split into 92 records due to the repeatig fields. The "AW ID" fields are 1-62 with duplicate numbers from the fields that were repeating.
In one layout I show the 62 payment records. On this layout I have the portal with 4 rows that are to show the related 92 records.
However it only shows one. I have looked at several previous files of mine that work in this way at some of the starter solutions.
Got an idea what I am missing - Grrrr
Did you select the option during import that splits the data in repeating fields up into separate records?
The 62 records got split up into 92 records with repeating "AW ID" primary keys due to the split.
Better check your data, relationships and any portal filters more closely then. Something isn't allowing your related records to correctly match to the parent record (obviously). An issue in any one of those areas could be the cause.
Thank you for your time.
Will try to sleuth it
Every time somebody tries to use a Repeating Field in a layout, the following warning should pop up:
"Repeating Fields are an artifact left over from decades past when Filemaker was a flat file datbase manager, and not yet relational. In virtually every case, you are better off using a relational table than a repeating field. Or in fewer words, DON'T USE REPEATING FIELDS!!!"
OK, Got it.
They should have that or just drop the feature to force you to use the other layout.
But come to think of it - this is an old file.
Thanks for the comment.
Retrieving data ...