AnsweredAssumed Answered

Problems filtering for records based on their relationships to other records

Question asked by ShaneB on Apr 13, 2015
Latest reply on Apr 14, 2015 by ShaneB


Problems filtering for records based on their relationships to other records


I have a slightly complex problem I am currently unable to solve. My FMP database allows users to create "clusters" of "observation" records that are formed by making new records on a join table. My users formerly used to be able to be able to display all of the records that were not part of a larger record "cluster", but a change to the database makes this function not work correctly any longer.

There are now two separate categories of "clusters", that I distinguish between with a flag of 0 or 1 in a field on the observations table. Users can make 2 kinds of cluster records, "themes" and "patterns" and depending on the type of cluster the database automatically populates that field. However, my users only wish to see the observation records that are not currently associated with any "pattern" cluster records. This means my former way of displaying observation records that are not part of cluster records no longer works, because it does not look at the flag field on the observations table, but instead looks at the join table.

My current script for showing un-clustered observation records is:

Go to Layout [original layout]
Enter Find Mode []
Set Field [ObservationsSelfJoin::_fkLinkedObservationID; "*"]
Omit Record
Set Error Capture [On]
Perform Find []
Constrain Found Set [Restore]
If [ not Get ( FoundCount )]
Show Custom Dialog ["No orphans found."]
End If

So I need to be able to ask, essentially: Show me all Observations::__observation_id_pk that are not part of any other observation record clusters, but only where the record cluster ObservationSelfJoin::_fkLinkedObservationID, which is the same as Observations::__observation_id_pk, has the value of "1" on Observations::theme_cluster_flag.

Does this make any sense?