4 Replies Latest reply on Oct 14, 2014 3:35 PM by PieroF

    Export related records including main table fields

    PieroF

      Title

      Export related records including main table fields

      Post

      Hello, I’d like to know if there is a way in FileMaker Pro 13 (running on MacOS 10.9.5) to export a set of related records that include fields from the main table.

      Let me try to explain with a simple example.

      a) Table1 with 2 fields and 2 records:

      - field1: A, field2: H

      - field1: B, field2: L

      b) Table2 with 2 fields and 5 records:

      - field3: H, field4: X

      - field3: H, field4: Y

      - field3: H, field4: Z

      - field3: L, field4: S

      - field3: L, field4: T

      c) a relation between the 2 tables

      Table1::field2 = Table2::field3

      When I File/Export Records from Table1 in the Export fields dialog I define:

      - Field1

      - Table2::Field4

      This is what I get: 5 records (plus their headings), one for each of the related records as expected:

      - A, X

      - <empty>, Y

      - <empty>, Z

      - B, S

      - <empty>, T

      where however only the first record of each group has values in the fields of the main table (Field1 in the example), while all records have values in fields of the related records.

      Instead this is what I expected - i.e. the same 5 records where however Field1 is filled with its repeated value:

      - A, X

      - A, Y

      - A, Z

      - B, S

      - B, T

      This is what I actually need: creating a new Table based on this relation.

      The above example is made up just to explain my question, and I know in this specific case reverting the tables in the export operation would solve the problem…

      But my real life problem is more complex: the table is just one, the relation is a self relation on Field2, and the main point is that the table is large: roughly 80.000 records, so the self related records to be exported are on the order of magnitude of millions.

      Since so far I could not find a way to get an exported set where all required fields are filled up, at the moment, after the exported set is created, I’m using a simple script looping on all exported records and filling up their missing values. This script was very easily coded, but its running time is comparable with the previous export time: if exporting did the full job I could save half of the time in these operations; hours in case of the 80.000 records.

      Is there a way to get what I'm looking for ?

      Thanks a lot for your help

      Piero

        • 1. Re: Export related records including main table fields
          philmodjunk

          Export your records from Table 2 and include any needed fields from Table 1 and you'll get the results that you need. If you are exporting manually, go to a layout based on Table 2 and export your data. If using a script, specify Table 2 as your source table instead of table 1.

          • 2. Re: Export related records including main table fields
            PieroF

            First of all thanks for answering my question.

            And sorry for the long posts but I believe I can make myself clear only by examples…

            The problem is that (as I wrote) the example I mentioned in my earlier post is just a simplified case I used to explain my need, not the real case, where your proposed solution doesn’t seem to work.

            This is the real case:

            a) TableA with 2 fields and 6 records - this describes a children-father relationship (even if some children like B and C have multiple fathers…).

            - child: A, father: H

            - child: B, father: H

            - child: C, father: H

            - child: B, father: L

            - child: C, father: L

            - child: D, father: L

            b) a self relationSelfTableA between 2 instances of the same table:

            TableA::father = SelfTableA::father

            c) I need to build a new TableB where each record describes a sibling relationship between pairs of TableA children. To get this I use File/Export Records from TableA where in the Export fields dialog I define:

            - child

            - SelfTableA::child

            This is what I get: 12 records (plus their headings), one for each of the each pair of brothers:

            - A, B

            - <empty>, C

            - B, A

            - <empty>, C

            - C, A

            - <empty>, B

            - B, C

            - <empty>, D

            - C, B

            - <empty>, D

            - D, B

            - <empty>, C

            while this is what I need:

            - A, B

            - A, C

            - B, A

            - B, C

            - C, A

            - C, B

            - B, C

            - B, D

            - C, B

            - C, D

            - D, B

            - D, C

            As a summary, your proposed solution is fine for the simplified case in the original example, but doesn’t solve (in my opinion) the above case where the relationship structure is symmetric: whether you export from TableA or from SelfTableA the result is the same and not what I expect…

            Do you have any other suggestion ?

            Thanks again for your time.

            Piero

            • 3. Re: Export related records including main table fields
              philmodjunk

              You apparently have this relationship:

              Father::__pkFatherID = Child::_fkFatherID

              But what you are setting up for c) is not clear.

              What match fields are you using for a "sibling relationship"?  The father ID?

              Your use of the same letters B, and C for children  with a parent H and then again with a Parent L is confusing. Are these the same children listed twice?

              What I describe will work for any many to one relationship where you set up your export context to work from the table that represents the "many" side of the relationship. But I can't tell what kind of relationship you are creating in the second part of your description.

              And the Alphabet soup makes it harder, not easier to analyze what you have and what results that you want to produce.

              • 4. Re: Export related records including main table fields
                PieroF
                Sorry for being a bit confusing.

                As a help to my description I attach a few snapshots of my test DB settings with the input data and the results (actual and expected).

                In fact my SelfTableA definition in my previous post was missing a piece and this was making things even more confusing because the listed results didn’t match the definition.

                Now in the attached snapshots everything is consistent and should be much clearer.

                The whole purpose is to convert father-child links in TableA (see graph on top of the attached picture) into new child-child links (graph at the bottom) in an excel table. Note that the "What I need" table has 2 kind of duplicates (e.g. B-C appearing twice, and pairs A-B and B-A that are the same link in a undirected graph) and some unnecessary A-A links: all these are easily removed after completing the export (see "...after optimization"). This is not the point anyway.

                To address your comments:

                You apparently have this relationship:

                Father::__pkFatherID = Child::_fkFatherID

                    NO: the relationship is that described in the pictures.

                But what you are setting up for c) is not clear.

                    OK. What I want is in the excel output as shown in the picture

                What match fields are you using for a "sibling relationship"?  The father ID?

                    SORRY, the term relationship is misleading here. I just need records in the resulting tables, each describing a link between a pair of children of the same father. 

                Your use of the same letters B, and C for children  with a parent H and then again with a Parent L is confusing. Are these the same children listed twice?

                    YES: A, B, C, D are actual values for the child field, as H and L are values for fathers, B and C appear twice because  each of them has both fathers.

                What I describe will work for any many to one relationship where you set up your export context to work from the table that represents the "many" side of the relationship.

                    OK: this is perfectly clear to me. And in fact this was the case in the very first example, but no longer in the next examples

                But I can't tell what kind of relationship you are creating in the second part of your description.

                    I hope the above notes and the picture explain better what I mean here.  

                And the Alphabet soup makes it harder, not easier to analyze what you have and what results that you want to produce.

                    Sorry for this. They were meant to clarify... but probably pictures are better.

                Finally I really appreciate the time you are spending trying to solve my question. But don't worry, if I'm not able to explain clearly my problem I have a workaround, even if not optimal...

                Thanks again

                Piero