Title

Adding more parents to existing parent-child relationship

Post

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?

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.

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.

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.

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.

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...

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.

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.

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:

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.

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.

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:

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: