10 Replies Latest reply on Apr 14, 2015 2:27 PM by ShaneB

    Problems filtering for records based on their relationships to other records

    ShaneB

      Title

      Problems filtering for records based on their relationships to other records

      Post

      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?

        • 1. Re: Problems filtering for records based on their relationships to other records
          philmodjunk

          Need to see the data model. Wouldn't even know that you had a join table until you mention it in passing. The details in how your tables are currently linked will be crucial in any suggested answer.

          Are you specifically trying to avoid using a portal filter or would a portal filter meet you needs?

          Are you using FileMaker 12 or newer and willing to trying using ExecuteSQL?

          • 2. Re: Problems filtering for records based on their relationships to other records
            ShaneB

            What is the suggested way to share the data model here?

            I think it's best that the results be displayed as a found set on one of the existing layouts.

            I am using FileMaker 12. I have no issues with using SQL if it works! :)

            • 3. Re: Problems filtering for records based on their relationships to other records
              ShaneB

              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.

              • 4. Re: Problems filtering for records based on their relationships to other records
                philmodjunk

                Enter Find Mode []
                Set Field [Observations::theme_cluster_flag ; 1 ]
                New Record/Request
                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

                Note: I removed Go to Layout [ original layout ] as putting this as your first script step does not actually do anything. (In your original script, it was telling FileMaker to change to the layout that is already the current layout.)

                • 5. Re: Problems filtering for records based on their relationships to other records
                  ShaneB

                  Phil: Thanks for your reply. Unfortunately, this is not working correctly, as no observation records come back as found, even though some are definitely not associated with a non-theme cluster. 

                  The original script without the added line still functions to show observation records that are not part of either Themes or Clusters, however.

                  • 6. Re: Problems filtering for records based on their relationships to other records
                    philmodjunk

                    Then you need to check your data.

                    The script first finds all records where the value in Observations::theme_cluster_flag is 1.

                    Then it omits those that have any related records in the join table.

                    From your descirptions of what you want and what you have, this should be all records not labeled as "theme" (them_cluster_flag = 0) that do not have a related join table record.

                    • 7. Re: Problems filtering for records based on their relationships to other records
                      ShaneB

                      Perhaps I am not being clear enough in my description.

                      All level 1 observations do not have anything in the Observations:theme_cluster_flag. Only level 2 observations do, which is either a "1" for Themes or "0" for Clusters. I am trying to find every level 1 observation that is not part of a level 2 observation, but only level 2 observations with "0" in Observations::theme_cluster_flag.

                      So I'd like to find all level 1 observations that do not have a related join table record on ObservationsSelfJoin, but ONLY where the matching ObservationSelfJoin::_fkLinkedObservationID -> Observations 2::__observation_id_pk record does not have the value for Observations 2::theme_cluster_flag of "1".

                      I believe Omitting any Observations with the theme_cluster_flag value of "1" at the point you suggested will not accomplish that.

                      • 8. Re: Problems filtering for records based on their relationships to other records
                        ShaneB

                        The observations are grouped into theme clusters and pattern clusters via the join table. 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.

                        • 9. Re: Problems filtering for records based on their relationships to other records
                          philmodjunk

                          This statement contradicts itself:

                          So I'd like to find all level 1 observations that do not have a related join table record on ObservationsSelfJoin, but ONLY where the matching ObservationSelfJoin::_fkLinkedObservationID -> Observations 2::__observation_id_pk record does not have the value for Observations 2::theme_cluster_flag of "1".

                          If there is no related record in the join table then there is no matching record of any kind in Observations 2 as there is no existing join table record to link a record from Observations to Observations 2.

                          • 10. Re: Problems filtering for records based on their relationships to other records
                            ShaneB

                            I figured it out with a final brainstorm! I approached it backwards instead of forwards, and it clicked into place. 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. It's fairly slow with hundreds of clusters and 3000+ observations, but it's not a very often-used function.