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.
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.
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.
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.
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.
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:
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.
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) ]
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.
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 ) ]
Set Field [ObservationsSelfJoin::_fkObservationID ; GetValue ( Observations::__Keyholder_gk ; $K ) ]
Set Field [ObservationsSelfJoin::_fkLinkedObservationID ; $ClusterID ]
Set Field [ Observations::__Keyholder_gk ; "" ]
Go to Layout [
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.
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 ]