1 2 Previous Next 25 Replies Latest reply on Sep 11, 2013 12:31 PM by ShaneB

    Adding more parents to existing parent-child relationship



      Adding more parents to existing parent-child relationship


           I have a parent-child record based system where researchers refine records into clusters, which are essentially groups of records. These groups are tracked on a parent-child join table. I have it set up where a single "cluster" record displays on a new layout, showing information from the parent records through a portal.

           I would like users to be able to add additional parent records to an existing child "cluster" record. I am having trouble figuring out the best way to go about emulating or replacing the way the users currently do this, which is essentially just cut and paste rows from a spreadsheet. My thought process is that a script could be run where:

           1) the id of the cluster record could be temporarily stored as a variable (not essential, I guess, it could function with just steps 2 and 3)

           2) users were re-directed to the record list for the parent records

           3) users re-select records from the parent list (this functions off of a global "keyholder" field) and press an "Add to existing cluster" function that displays a list of cluster records with a button next to the clusters that takes the id of the globally selected records and adds them to the join table.

           I believe the "add to existing cluster" script would have to direct to a modified version of the Cluster layout with the additional selection button, to avoid users seeing it in the normal Cluster layout. Does this approach make sense?

        • 1. Re: Adding more parents to existing parent-child relationship

               Do you need to use any script?

               You seem to have a many to many relationship like this:


               Cluster::__pkClustersID = Parent_Cluster::_fkClustersID
               Parent::__pkParentsID = Parent_Cluster::_fkParentsID

               You can place a portal to Parent_Cluster on the Cluster layout to list and select a Parent record for each given Cluster record. Fields from Parent can be included in the Portal to show additional info about each selected Parent record and the _fkParentsID field can be set up with a value list for selecting Parent records by their ID field.

          • 2. Re: Adding more parents to existing parent-child relationship

                 I see where you are going with that, but it might not be ideal. You helped me set up the child-parent function here: http://forums.filemaker.com/posts/7e5f86bf4c

                 I have a new view for clusters, which has a portal to the join table which displays information from the observations that are used as the basis for the cluster record. As there are 1000+ observation records for each project, users use the observation (not cluster) layout to search, select, and modify existing observations, for then refining into clusters. I'm trying to think of a way for users to have a script that, instead of creating a new cluster, allows them to add new records to an existing one in a straightforward way.

            • 3. Re: Adding more parents to existing parent-child relationship

                   Which would still be a case of adding records to the join table to link them to an existing cluster. Once you have found a "parent" (Observation) record or group of recods, the script need only create records in the join table to link them. But you'll need to be careful that the correct cluster record be linked to the new records in the join table.

              • 4. Re: Adding more parents to existing parent-child relationship

                     What script function would I use to take the id field of a cluster record, make it a new variable, and use that variable to populate the join table fields? Right now the script to make a cluster takes a current list of records, makes a new one, stores the ID as a variable, omits it from the list, and then loops through the list to populate the join table. Since users are going to jump from their list of parent observations to the list of child clusters, then select a cluster, I'm going to need to store the selected record id's somewhere to populate the join table, matched with the id of the child cluster.

                • 5. Re: Adding more parents to existing parent-child relationship

                       You can store the cluster ID in a variable. If you need the value to persist after the script completes, you can use a $$globalvariable.

                  • 6. Re: Adding more parents to existing parent-child relationship

                         I've attached my current script. I put the pause in so I can make sure the list being displayed was actually what I wanted. For whatever reason it doesn't add all the selected records to the join table, but always adds unselected records such as 10, 69, and 1233. I'm clueless why this is happening! Probably a syntax issue...

                    • 7. Re: Adding more parents to existing parent-child relationship

                           OK, I've found where the issue is. In the loop, the Go to Layout [original layout] step doesn't go back to the list of selected records, which breaks things. I'm trying to figure out how to make it go back to the list of selected records and loop through the list of selected records.

                      • 8. Re: Adding more parents to existing parent-child relationship

                             I couldn't make heads or tails out of your script so I went back to the previous thread an looked at your relationship graph, but it didn't help as it doesn't show a table occurrences that match those named in your script. I stopped trying to figure this out when I couldn't find occurrences named "observationsSelfJoin" and "Observations2".

                             Perhaps you could upload an updated screen shot of your table occurrences and relatinships?

                             Perhaps you could identify which table occurrences refer to your table of "clusters"?

                             You may also need to explain the exact process you want to use to select observations for linking to an existing cluster (or a new cluster for that matter.) There are a number of possible ways to search out and select records for that purpose so we can work from the approach you choose to use.

                        • 9. Re: Adding more parents to existing parent-child relationship

                               Here is my current database:

                               Clusters are the same as observations, and found on the observation table, but have relationships to the observations that informed them. These relationships are stored on the selfjoin table.

                               Essentially what happens is users create lists of records they want to form into clusters. They do this via searches, omitting records, etc, and clicking a "Select Record" button for the records they want to form into a cluster. The Select Record button runs this function:

                               Case (
                               FilterValues ( Observations::__keyholder_gk ; Observations::__observation_id_pk )
                               ; Substitute ( ¶ & Observations::__keyholder_gk & ¶ ; ¶ & Observations::__observation_id_pk & ¶ ; ¶ )
                               ; Observations::__keyholder_gk & ¶ & Observations::__observation_id_pk

                               What this does is essentially toggle whether or not the records ID is found on the keyholder global key list. Conditional formatting makes it show up as selected or not by changing the color.

                               http://fmlayoutmode.com/wp/?p=126 is what I followed to make this happen.

                               Anyway, users make a list of selected records in this fashion, and then hit a button to send these all to a cluster, which you helped me figure out. Now I want for users to be able to do the same thing, but instead of making a new cluster, matching them to an existing cluster. I made a new layout for adding clusters, and a button that says "Add to existing cluster" on the observations layout. The "Add to existing clusters" button sends users to the cluster adding layout, which has a button that users click to select the cluster they want to add records to. This button runs the script I attached above.

                          • 10. Re: Adding more parents to existing parent-child relationship

                                 so at the time that the users run the script, they have already built a list of selected IDs in the global field.

                                 And on which occurrence of Observations is that layout based? It should be Observations 2.

                                 I don't think Go to Layout [original layout] is returning you to the layout you expect. I think that step needs to be changed to be:

                                 Go To Layout ["Low Level Observations" (Observations) ]

                            • 11. Re: Adding more parents to existing parent-child relationship

                                   The problem I am now facing is that I can't get it to add all the selected records. Something with the syntax of how I have it looping through the records is breaking. It adds every record but one. The long line

                                   Set Field [Observations::__keyholder_gk; etc ] removes the global key from the record so it no longer shows up in the list. However, later in the script I have it Go to Record/Request/Page [Next, exit after last], which I believe is skipping one of the records. I can't figure out how to make it function correctly and gracefully end when there are no more highlighted records to display and add to the cluster.


                              • 12. Re: Adding more parents to existing parent-child relationship

                                     I think that you are skipping the very first record in your list.

                                     Instead of Go To Record/Request/Page[Next,

                                     Replace it with:

                                     Exit Loop IF [Not Get ( FoundCount ) ]

                                     Note: This is based on the incompletely documented fact that if you use GTRR with the <current Layout> option, a lack of related records produces an empty found set where the step fails to execute if you specify a layout by name and there are no related records. You could also check using Get Last Error to detect when you have processed the last record in the list.

                                     On the other hand, you are only moving ID's that you already have listed in a global field. You don't actually need to be moving back and forth between layouts nor removing ID's from the list in the global field one at a time. Here's a simpler script:

                                     Set Variable [$ClusterID ; value: Observations 2::__Observation_id_pk]
                                     Go To Layout ["ObservationsSelfJoin" (ObservationsSelfJoin ) ]
                                        Set Variable [$K ; Value: $K + 1 ]
                                        Exit Loop If [$K > ValueCount ( Observations::__Keyholder_gk ) ]
                                        New Record/Request
                                        Set Field [ObservationsSelfJoin::_fkObservationID ; GetValue ( Observations::__Keyholder_gk ; $K ) ]
                                        Set Field [ObservationsSelfJoin::_fkLinkedObservationID ; $ClusterID ]
                                     End Loop
                                     Set Field [ Observations::__Keyholder_gk ; "" ]
                                     Go to Layout [

                                • 13. Re: Adding more parents to existing parent-child relationship

                                       That is working really well, except it is adding one blank record to the SelfJoin table where the _fkLinkedObservationID is filled in with the cluster record ID but the _fkObservationID is left empty.

                                  • 14. Re: Adding more parents to existing parent-child relationship

                                         Better take a careful look at the value in the global field. I would guess that you have a blank row of data--probably caused by an extra return at the end of your list of values. If that's the case, you can either modify your design to remove that extra return or modify the Exit Loop IF step by 1:

                                         Exit Loop If [$K > ValueCount ( Observations::__Keyholder_gk ) - 1 ]

                                    1 2 Previous Next