14 Replies Latest reply on May 7, 2013 1:45 PM by carolchansen

    Export Repeating Fields to Excel!

    carolchansen

      Title

      Export Repeating Fields to Excel!

      Post

            

           How do you deal with exporting repeating fields to Excel since it won't let you?
           Is there any work arounds?
           Thanks,
           Carol

        • 1. Re: Export Repeating Fields to Excel!
          carolchansen

               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.

          • 2. Re: Export Repeating Fields to Excel!
            philmodjunk

                 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.

            • 3. Re: Export Repeating Fields to Excel!
              carolchansen

                   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

                    

              • 4. Re: Export Repeating Fields to Excel!
                carolchansen

                     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

                      

                • 5. Re: Export Repeating Fields to Excel!
                  philmodjunk

                       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:

                         
                  1.           If you do not already have such a field, define a primary key field in your original table by adding a new number field set to auto-enter a serial number. Use replace field contents to assign a serial number to this field for your existing records.
                  2.      
                  3.           Import the data into your new related table, but do not import from all the fields in the original table. Only import the primary key into the foreign key field and your repeating field or fields that will be split up into related records. During the import, select the option that imports the repetitions into separate tables.
                  4.      
                  5.           Define a relationship linking your original table to the new related table by the primary key to foreign key match up this import makes possible.
                  6.      
                  7.           On your layouts, replace your repeating fields with portals to the related table.

                        

                  • 6. Re: Export Repeating Fields to Excel!
                    carolchansen

                         Thanks Alot,

                         I really appreciate this.

                         I will give that a try - so does that mean in general you don't want repeating fields?

                         Carol

                    • 7. Re: Export Repeating Fields to Excel!
                      philmodjunk

                           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.

                      • 8. Re: Export Repeating Fields to Excel!
                        carolchansen

                             Hi again,

                             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

                             Thanks,

                             Carol

                        • 9. Re: Export Repeating Fields to Excel!
                          philmodjunk

                               Did you select the option during import that splits the data in repeating fields up into separate records?

                          • 10. Re: Export Repeating Fields to Excel!
                            carolchansen

                                 Yes,

                                 The 62 records got split up into 92 records with repeating "AW ID" primary keys due to the split.

                                 Carol

                                  

                            • 11. Re: Export Repeating Fields to Excel!
                              philmodjunk

                                   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.

                              • 12. Re: Export Repeating Fields to Excel!
                                carolchansen

                                     Thank you for your time.

                                     Will try to sleuth it

                                     Best,

                                     Carol

                                • 13. Re: Export Repeating Fields to Excel!
                                  davemill

                                       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!!!"

                                  • 14. Re: Export Repeating Fields to Excel!
                                    carolchansen

                                         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.

                                         Carol