6 Replies Latest reply on Feb 18, 2014 2:10 PM by MichelleJoachims

    Portal filtering only on duplicate field values

    MichelleJoachims

      Title

      Portal filtering only on duplicate field values

      Post

           Hi all,

           I am really new to FMP, but am setting up a database from scratch to contain my lab project data and I cannot figure out how to set up a portal filter to display only records with duplicate values in one particular field. I have been able to successfully set up portal filters to display based on other types of field information but this one has me stumped. I am using FMP 11- any insight would really help me. I know I'm missing something very basic- it must be that I cannot come up with the appropriate calculation I need. I appreciate any help in advance!

           Thanks,

           Michelle

        • 1. Re: Portal filtering only on duplicate field values
          philmodjunk

               A portal implies a one to many relationship:

               LayoutTable-----<PortalTable

               Let's say that a field named "Data" is the field that might contain the duplicate values for which you want to filter.

               Go to Manage | Database | Relationships, create a new table occurrence of your portal table and link it like this:

               LayoutTable----<portalTable>----<PortalTable 2

               Set up the match fields to be:

               portalTable::Data = portalTable 2::Data

               then you can set up a filtered portal to PortalTable with this portal filter:

               Count ( PortalTable 2::Data ) > 1

               and then only related portal records that have at least one duplicate value will be visible in your portal.

               A different portal filter can be used with this relationship to omit duplicates.

          • 2. Re: Portal filtering only on duplicate field values
            MichelleJoachims

                 That did the trick!

                 Now, I need to count the number of times each duplicated field occurs in the context of a given portal record (patient)- how can I add this data to the portal? Much appreciated- so much to learn!

                  

                 Thanks so much!!!

                 M.

            • 3. Re: Portal filtering only on duplicate field values
              philmodjunk

                   Hmmm, my solution may be too simple. You may need to include an additional match field so that you only match to portal table records that are linked to the same layout table record.

                   Say you have these relationship:

                   Experiments----<Results>-----<Results 2

                   then your match fields should be:

                   Experiments::__pkExperimentID = Results::_fkExperimentID

                   Results::_fkExperimentID = Results 2::_fkExperimentID AND
                   Results::Data = Results 2::Data

                   And my previous post has the seed of what you want: Count ( PortalTable 2::Data )

                   if you define a calculation field in Results as Count ( Results 2::Data ) , it will return the number of results where Data is the same value that are also linked to the same Experiments record. Be sure to select "Results" and not "Results 2" from the "Context" drop down at the top of the specify calculation dialog.

              • 4. Re: Portal filtering only on duplicate field values
                MichelleJoachims

                     Thanks, Phil- you are awesome! One last thing for ease of viewing these data- I only want to show each "duplicate" value in the portal one time, along with its count number. Is this a matter of adjusting the filter settings to only allow the first representation of the value? If this is too big of a deal, I'll leave it alone.

                     Thanks again!

                     M.

                • 5. Re: Portal filtering only on duplicate field values
                  philmodjunk

                       Add a primary key field to the results table. An auto-entered serial number is simplest in FileMaker databases. I'll call it __pkResultID. For existing records, you'll need to use replace field contents to assign a serial number to this new field.

                       Then modify your portal filter to be:

                       Count ( Results 2::Data ) > 1 And Results::__pkResultID = Results 2::__pkResultID

                  • 6. Re: Portal filtering only on duplicate field values
                    MichelleJoachims

                         Fantastic! Appreciate you sharing your time and expertise with me!! 

                    enlightened

                         Have a great day!

                         M.