6 Replies Latest reply on Oct 10, 2014 11:42 AM by charliec2

    Denormalized Export

    charliec2

      Title

      Denormalized Export

      Post

      Given:  normalized relational model

      Question:  We have a need to create a denormalized export of one to many results to an Excel worksheet where there is one row per parent record and specific data from child records is placed into "column sets" in the same row as the parent data.

      Anyone have experience with this or ideas?

      CC

        • 1. Re: Denormalized Export
          philmodjunk

          Using some calculation fields to produce your "column sets" of related child data, Execute SQL is one possibility if you are using FileMaker 12 or newer, you should be able to produce such an export.

          (GetNthRecord can access data from the 1st, 2nd, Nth related record...)

          • 2. Re: Denormalized Export
            charliec2

            Thanks PhilModJunk.  I've done some quick testing and see how this could help.  I'm new to FMP and so am not sure how to loop through child records and dynamically supply the next record number to the GerNthRecord function.  Are you away of a sample script that interates through a one-to-many record sets or Layout?

            • 3. Re: Denormalized Export
              philmodjunk

              I don't see a reason for any script at all here.

              Each field would be calculation field defined in the parent record.

              Say you have this Parent record and list of related child records:
              Shipment::ShipmentID  5432
                 Fruit:  Apples
                 Fruit:  Pears
                 Fruit:  Kiwis

              If you defined this calculation in a field in Shipment:

              GetNthRecord ( Child::Fruit ; 1 )

              It will return Apples

              A different calculation field in Shipment would use:

              GetNthRecord ( Child::Fruit ; 2 )

              To return Pears

              This enables you to produce an export to Excel where each row looks like

              5432 | Apple | Pears | Kiwis

              • 4. Re: Denormalized Export
                charliec2

                Thanks again.  Do you know of a way to make this dynamic so you don't have to know how many child records there are in the one-to-many set?  i.e. iterate through the set of related child records, as the number would change with each parent record...get the next record number in the related record (child) set?  

                I very much appreciate the patience with a FMP novice.  I don't see a function to do the child set iteration.  

                • 5. Re: Denormalized Export
                  philmodjunk

                  I could set up a looping script, but I'd still need to define one data field for every value I extract from the set of related child records so I don't see that this is any more "dynamic" in the long run.

                  Perhaps there is a way to configure an XSLT "grammar" to parse such data via an XML export...

                  • 6. Re: Denormalized Export
                    charliec2

                    Yep, I agree.  Not being able to create fields from within a script would keep us from attaining a true dynamic solution.  

                    I wonder if any FileMaker Add-On software companies have addressed that.

                    Thanks again.