Problems filtering for records based on their relationships

[shanebee]

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?

[comment]

I've read this several times and still can't make any sense out of it. Could you (a) name your tables (I believe there would be three of these, since you say you have a join table), (b) explain what does a record in each table represent in real life, and (c) explain what are you trying to accomplish, in terms of (a) and (b)?

[Lee Smith]

(d). attach a copy of your file or a mockup.

[shanebee]

Yes, I understand I am not expert in describing this via text. I might just not understand the proper syntax for doing so on the forum.

The simplest way to describe it is that users make a number of "Observation" records (consider these "level 1") and then group them together into "Cluster" records ("level 2"). They can then view the "Cluster" records and can see the original observation records that make up the cluster through a portal view. They were originally able to click a button that ran a script (that I put in my original post) that would then display any level 1 observation records not grouped into a level 2 observation. However, they desired the ability to create other types of groups of observation records, which are called "Themes", which are level 2 observations just like "Clusters", on the same table, but are differentiated by a field Observations::theme_cluster_flag. When users go to the layout for themes, it only displays records with that flag filled in with "1", and when they go to the layout for Clusters it only displays records without that flag filled in. 

This essentially breaks the ability for users to be able to quickly find out what level 1 observations are not in "Clusters", because if a level 1 observation is in a "Theme" the script considers that grouped. As "Themes" are far broader than "Clusters", the functionality is not very useful for my users. I am searching for a way for users to be able to display level 1 Observations that are not part of "Cluster" level 2 observation records.

Tables involved:
Observations
ObservationsSelfJoin
Observations 2 (a separate instance of the Observations table)

ObservationsSelfJoin links the two instances of Observations, via the Observations::__observation_id_pk on each being foreign keys on the join table.

The users can review "level 1" Observations on one layout, then when they switch to another layout they view "level 2" Observation records, as the layout only shows Observation records with Observation::observation_level "2". A portal shows all the Observation records related to it through ObservationsSelfJoin. This allows users to see the component level 1 records.

Does this make more sense now? I can spend time stripping out any proprietary functionality and details from the database file to upload here, but that will likely take a good bit of time for me.

[comment]

Are "cluster" records records in the Observations table? If yes, why?

 

If I understand your current situation correctly (and that's a big IF), you can find the observations that are not part of any "cluster" by:

Go to Layout [ “New Layout” (Observations 2) ] 
Enter Find Mode [  ] 
Set Field [ Observations::Status; "cluster" ] 
Omit Record 
Perform Find [  ] 

Of course, this will also find any records that are a "cluster" or a "theme" since - presumably - these records are also not part of any "cluster". So you may want to extend this by omitting these records too:

Go to Layout [ “New Layout” (Observations 2) ] 
Enter Find Mode [  ] 
Set Field [ Observations::Status; "cluster" ] 
Omit Record 
New Record/Request 
Set Field [ Observations 2::Status; "*" ] 
Omit Record 
Perform Find [  ] 

 

---

 

In anticipation of your next question: to do the same thing without adding a new layout, you would need to:

Go to Layout [ “Observations” (Observations) ] 
Enter Find Mode [  ] 
Set Field [ Observations::Status; "cluster" ] 
Perform Find [  ] 
Go to Related Record [ Show only related records; Match found set; From table: “Observations 2”; Using layout: <Current Layout> ] 
Show Omitted Only 

Of course, this still includes the "cluster" and the "theme" records. It will also be slower, perhaps significantly so.

Edited April 14, 2015 by comment

[shanebee]

When I designed the database almost 2 years ago, logically I saw Observations and Clusters as the same type of entity, just different levels on a hierarchy. They contain the same type of information, just at different viewpoints. So I believed they should live on the same table. Maybe I am incorrect in thinking this?

The design works quite well normally, but this new request by my users has broken an old functionality.

The observations are grouped into theme clusters and pattern clusters via the join table. It's not simply a list of observations with flags. So there is a foreign key from the Observations table and the Observations 2 instance of the table on the join table. There is a portal on the Clusters layout that shows the related Observation records from the join table. So there is a join table like so:

1114, 1115, and 1116 are the IDs for level 2 observations, which are themes and clusters. The ids in the column on the right are the level 1 observations. 

I need to be able to find all the observations linked to _fkObservationIDs that are not listed on this table, which means they are not associated with any grouping. But I want to have them listed ONLY if the _fkLinkedObservationID is not associated with a record with the Observations::theme_cluster_flag value of "1", which indicates they are in a "Theme" cluster instead of a "Pattern" cluster. Basically I need to know if an observation is not nested into a pattern cluster.

[comment]

When I designed the database almost 2 years ago, logically I saw Observations and Clusters as the same type of entity, just different levels on a hierarchy. They contain the same type of information, just at different viewpoints. So I believed they should live on the same table. Maybe I am incorrect in thinking this?

​Maybe, maybe not. I cannot answer this without knowing what your records represent in real life. But it seems difficult to imagine groups of items having the same properties (i.e. fields) as the items themselves.

 

I am afraid you lost me after that. Does my suggested script not work as intended?

[shanebee]

​Maybe, maybe not. I cannot answer this without knowing what your records represent in real life. But it seems difficult to imagine groups of items having the same properties (i.e. fields) as the items themselves.

 

I am afraid you lost me after that. Does my suggested script not work as intended?

​The script does not work as intended. I believe it is just giving me back all the records in the database. 

However, I figured it out with a final brainstorm! I approached it backwards instead of forwards. That is, on the Observations 2 layout of clusters, I had it show all the related records from Observations 1 from the entire found set of "Cluster" records. That got me all the observation records that are in the cluster groups. I then just flipped the found set to show Omitted Only, and Constrained the Found set to only show Observation (level 1) records, instead of Level 2 records as well.

The final script is very simple:

Go To Related Record [Show only related records; Match found set; From table: "Observations"; Using layout: "Observations"]
Show Omitted Only
Constrain Found Set [Restore]

Once I approached it from that direction, it worked very well. Thank you for showing me the "Show Omitted Only"!

[bruceR]

And what does "constrain found set [ Restore]" do?

What are you restoring?

It is usually about as easy and FAR clearer to use explicit scripted finds:

Enter Find Mode [ no restore ]

Set field [ your field; your value ]

Perform Find [ no restore ]

Or Constrain Find [ no restore ]

[shanebee]

And what does "constrain found set [ Restore]" do?

What are you restoring?

It is usually about as easy and FAR clearer to use explicit scripted finds:

Enter Find Mode [ no restore ]

Set field [ your field; your value ]

Perform Find [ no restore ]

Or Constrain Find [ no restore ]

​It restores with the criteria of Observations::observation_level: [1], because I need the results to only be level 1 observations, not level 2 or higher, which are on the same table.

[bruceR]

Yes.

Case in point. Why did we even need to ask this question?

You are asking for help with your script. And since your script uses restore, we can't read it; and so cannot help you.

Don't make it difficult for people who are giving you free help.