6 Replies Latest reply on Jul 17, 2014 4:18 PM by philmodjunk

    Store looked up values into field repetitions?

    ChristopherKessler

      Title

      Store looked up values into field repetitions?

      Post

           I have a table of people and another of marriages. The marriages is basically a pairing of the people (ie, there are two fields, one for the first person, and one for the second), that has a unique serial number associated with it. I have the people table related to the marriages table through each of these two fields, so it's something like the following:

           People Table: Person ID --> Marriages: Person 1

           People Table: Person ID --> Marriages: Person 2

           With these two relationships, I'm looking up the marriage id from the Marriages table to have it present on each person's record in the People table.

           This works; however, at times someone may have switched partners so the marriage ID changes. This updates properly; however, I would like to have a way to automatically preserve the prior marriage for that person, and have this prior marriage be searchable along with the first one.

           The marriages table can support Person 1 being with Person 2, and then having a new entry that has Person 1 with Person 3, etc., so the data of the multiple marriages is there; however, the problem comes with displaying all of these pairings for Person 1 in the record for Person 1.

           So far I have a calculation filed that attempts pulls the marriage IDs to the People records, and it works; however, it only pulls the first-found marriage. So for instance, with Person 1 having been married to Person 2, this is the marriage ID that shows from the calculation, and not the second one.

           I would like somehow to pull both by calculation into Person 1's record. I have tried creating a repeating field for the Marriage IDs in the People table so it can hold multiple values, but cannot seem to get the calculation that finds the marriage IDs to either 1) find multiple values, and/or 2) store them in the separate field repetitions.

           Is there a way to do this, or another approach altogether that, given this scenario, will be able to look up the multiple Marriage records containing Person 1, and then store the various IDs in Person 1's "People" record?

        • 1. Re: Store looked up values into field repetitions?
          philmodjunk

               Instead of a repeating field, List ( Marriages::Person 2 ) can produce a list of all Spouses of the current person record all in one field with returns separating each value.

          • 2. Re: Store looked up values into field repetitions?
            ChristopherKessler

                 Hmmm....looks like it should work, but its not. I have two records in the Marriages database:

                 Fields: Marriage ID, First Member, Second Member

                 Record 1: "ID 1", "Person 1", "Person 2" (basic fields filled out, with no logic or lookups to create them)

                 Record 2: "ID 2", "Person 1", "Person 3" (new marriage to person 3).

                 This table is related to the "People" table through the "First Member" name, so through this relationship, from the People record for Person 1 I should be able to find related records in the Marriage table and reveal both of the above records.

                 In the People table I have records for Person 1, Person 2, and Person 3. I have a field called "Marriages" in this table that has the following calculation:

                 List ( Marriages::Marriage ID )

                 When I do this and look at Person 1's record in the People database, I only see the "ID 1" listed, and not "ID 1\r ID 2" (or other pairing of the two related marriage IDs) as expected/desired.

                 Is there something I'm missing here?

            • 3. Re: Store looked up values into field repetitions?
              philmodjunk

                   Did you notice that I posted that the List function produces a list of values with a RETURN between each value?

                   Expand the field to be more than one row of text tall or click into the field and you should see more than a single marriage given this data.

                   But you can also just use a portal to Marriages to show the same data.

                   If you want to list the marriages in a single horizontal row, use:

                   Substitue ( List ( Marriages::Person 2 ) ; ¶ ; ", " )

                    

              • 4. Re: Store looked up values into field repetitions?
                ChristopherKessler

                     Hmm...for some reason it is not working. When I create a single relationship between tables it seems to work, but I need two relationships so each of the spouses in the marriages are appropriately updated. When I have two relationships assigned so they are updated, it only grabs the Marriage ID of the first relationship and not any others.

                     Here are some screenshots of the situation:

                      

                • 5. Re: Store looked up values into field repetitions?
                  ChristopherKessler

                       Ah found it!!! The calculation needed to be the following:

                       Substitute ( List (Marriages::Marriage ID ) & List (Marriages 2::Marriage ID ) ; ¶ ; ", " )

                       It seems this now properly concatenates separate listings along the two relationships, so they will appear properly.

                       I am curious though, if there is any way for this type of calculation to drop the items from the list into separate repeat fields?

                  • 6. Re: Store looked up values into field repetitions?
                    philmodjunk

                         There is no such way for this calculation. Repeating fields are what were once used to add some ability to associate multiple items of data with a single record back in FileMaker 2.5 and earlier when FileMaker was a Flat File system. The utility of repeating fields today are much more limited.

                         And I'd double check that calcualtion. As posted, it would seem to combine the last entry of the first list with the first entry of the second...

                         Ps. the relationships now show do not match what I assumed that you had here. Matching related by names is usually not a good idea as names are not unique and can be changed (such as due to marriage or divorce...)