11 Replies Latest reply on Mar 27, 2015 1:26 PM by philmodjunk

    Multiple Value Frequency Search

    ScottM_2
    ScottM_2 Mar 24, 2015 7:54 AM

      Title

      Multiple Value Frequency Search

      Post

      I have tried searching for this, but have come up empty.  I apologize up front if I am not describing my requirements well enough, so please ask for clarification where needed.  

      I have a Table, "VISIT_CLINIC", that contains a patient's visits to a Medical Clinic (often many visits) and the associated treatment code.  This table is related to the "CLINIC" table via the clinic's unique ID, "clinic_fk" (and each new patient creates a new clinic UID for other reasons).  To allow for multiple treatments on a single visit, a "Visit Number" field is required which contains the visit number (1, 2, 3, etc.).  This, along with a treatment code field, "CPTCode_fk", (which is related to a treatment table that contains the treatment description) and other pertinent fields make up the Visit Table.  

       

      What I need to do now is generate a Sub-Summary report (summarized by Visit #)  that shows how often a set of codes, "CPTCode_fk", are ALL billed on the same visit, "Visit Number", for each Patient, "clinic_fk". Example:  How often to ALL the codes 123, 567, 910, and 1112 show up under the same visit number for any Patient.  

      Thank you in advance for you assistance!

       

      The Database is setup as below (table names are the first line, the following lines are the <Primary Key>--<Foreign key> that define the relationships.  

      Patient >-----------<CLINIC>--------<VISIT_CLINIC>-------<TREATMENT_CLINIC

      Patient_UID>---<patient_fk>

                              <clinic_UID>----------<clinic_fk>

                                                       <CPTCode_fk>--------------<CPT Code>

                                                                                                               

       

      • 115 Views
      • Tags:
        • 1. Re: Multiple Value Frequency Search
          philmodjunk
          philmodjunk Mar 24, 2015 8:05 AM (in response to ScottM_2)

          So you want to find all records with the codes: 123, 567, 910, or 1112?

          This is normally described as an Or type find.

          There are two basic approaches:

          Enter find mode
          Specify code 123
          New request
          specify code 567
          New Request
          Specify code 910
          new Request
          specify code 1112

          or:

          Perform find for code 123
          Return to find mode
          Specify code 567
          Extend found set
          Return to find mode....
          and so forth

          Both approaches can be done manually or in a script.

          For examples of scripted finds, see: Scripted Find Examples

          • 2. Re: Multiple Value Frequency Search
            ScottM_2
            ScottM_2 Mar 24, 2015 9:45 AM (in response to ScottM_2)

            Sorry, 

            I need to find all records with the codes 123, 567, 910, and 1112.

            Examples:

            If Patient 1 has 123, 567, 910, and 33423 on the same visit (with a matching "Visit Number Field") - this would NOT be counted

            If Patient 2 has 123, 567 and 910 on the same visit (with a matching "Visit Number Field") - this would NOT be counted

            If Patient 3 has 123, 567, 910, AND 1112, but not on the same visit (one or more codes had a different value in the "Visit Number Field") - this would NOT be counted

            If Patient 4 has 123, 567, 910, AND 1112 on the same visit (with a matching "Visit Number Field") - this WOULD be counted

             

            Does that help or make things worse?

            • 3. Re: Multiple Value Frequency Search
              philmodjunk
              philmodjunk Mar 24, 2015 9:57 AM (in response to ScottM_2)

              Then take the second example and use Constrain Found set instead of Extend Found set.

              • 4. Re: Multiple Value Frequency Search
                ScottM_2
                ScottM_2 Mar 24, 2015 11:17 AM (in response to ScottM_2)

                Thank you for your time so far - I truly appreciate it!

                When viewing the results of my Script in Table View, the only value in CPTCode_fk in the found set is that which is last entered into the Constrain Found Set command.  

                Here is what I have:

                       
                • Enter Find Mode[]
                  •      
                • Set Field [VISIT_CLINIC::CPTCode_fk; 123]
                  •      
                • Enter Find Mode[]
                  •      
                • Constrain Found Set [Restore]      
                              
                  • Specify find requests is checked and set to:           
                                     
                    •  Action: Find Records
                      •                
                    •  Criteria:  VISIT_CLINIC::CPTCode_fk: [567]
                      •           
                              
                    •      
                       
                  •      
                • Perform Find[]      
                              
                  • Specify find requests is NOT checked
                    •      
                       

                When viewing in Table View, the only results are those in which the CPTCode_fk=567.  I only tested 2 of the 5 values I need, which is why there are no other Constrain Found Set commands.  Did I miss something?

                • 5. Re: Multiple Value Frequency Search
                  philmodjunk
                  philmodjunk Mar 24, 2015 3:10 PM (in response to ScottM_2)

                  There should only be on perform find step in your script and it should be near the beginning of your script. It's also a lot easier to keep track of the find criteria used in a script if you don't store the criteria inside an enter find mode, perform find or constrain found set step. Do it this way:

                  enter FInd mode []
                  Set field
                  Set Field
                  Perform find, constrain found set or extend found set here.

                  The set field steps set up the criteria for the find/extend/constrain.

                  It will also make a difference whether you use this method to find Patient records or visit records. I have been assuming that you enter only one such code for a given record in one of your tables--either a visit table or a treatment table.

                  • 6. Re: Multiple Value Frequency Search
                    ScottM_2
                    ScottM_2 Mar 26, 2015 6:56 AM (in response to ScottM_2)

                    I fear I may not be describing my requirements and/or table setup well.  

                    The table these searches are being performed in has the following fields:

                    Vist_UID - unique number for every entry in the table

                    Visit Number - user entered value

                    CPTCode_fk - a code that refers to a related table to pull in the treatment description

                    Clinic_fk - Foreign Key that distinguishes between patients

                     

                    ALL visits for ALL patients are placed in this table, so there are hundreds of entries for each patient (which are distinguishable by a unique Clinic_fk for each patient).  For each patient (Clinic_fk) there could be multiple, duplicate entries in the "Visit Number" field since a visit can contain more than one treatment.  What I need is a search that details how often 5 specific codes (CPTCode_fk) show up together in the same visit (Visit Number) for each Patient (Clinic_fk). 

                     

                    If I have Multiple SET FIELD commands in the script for the same field (CPTCode_fk), each subsequent SET FIELD command on the same field essentially negates the one before it, correct?  I only say this because you have multiple SET FIELD commands in your example above, which I assume is for different fields, but I wanted to make sure.  

                    If I use one SET FIELD command for the first CPTCode_fk then make the next command a CONSTRAIN FOUND SET with the second of the 5 codes, it is only the code contained in the CONSTRAIN command that is shown, the number in the SET FIELD command appears to be discarded or ignored.  

                    I hope this helps clarify what I am looking for.  I have tried a few iterations of your suggestions above, but I cant seem to get it to show me more than one CPTCode_fk at a time in my results.  Thank you again for your patience and assistance!

                    • 7. Re: Multiple Value Frequency Search
                      philmodjunk
                      philmodjunk Mar 26, 2015 7:14 AM (in response to ScottM_2)

                      Yes, in my scripted outline the different set field steps would be for specifying criteria in a different field of the same record.

                      Here's the key problem:

                      For each patient (Clinic_fk) there could be multiple, duplicate entries in the "Visit Number" field since a visit can contain more than one treatment.

                      Please explain what you mean by "multiple, duplicate entries". Does that mean that you create several records in the same table, each with a different value in CPT_K code, but the same visit and clinic number? Or are you entering a list of values into one field of one record?

                      • 8. Re: Multiple Value Frequency Search
                        ScottM_2
                        ScottM_2 Mar 26, 2015 7:44 AM (in response to ScottM_2)

                        Please explain what you mean by "multiple, duplicate entries". Does that mean that you create several records in the same table, each with a different value in CPT_K code, but the same visit and clinic number? 

                        Correct - Visit Number and Clinic_fk can contain duplicate information with the only difference being the CPTCode_fk) there are other fields that could be different, but aren't pertinent.  For example:

                        Record 1:  Visit_UID=1    Clinic_fk=1      Visit Number=1     CPTCode=123

                        Record 2:  Visit_UID=2    Clinic_fk=1      Visit Number=1     CPTCode=567

                        Record 3:  Visit_UID=3    Clinic_fk=1      Visit Number=1     CPTCode=910

                        Record 4:  Visit_UID=4    Clinic_fk=1      Visit Number=2     CPTCode=123

                        Record 5:  Visit_UID=5    Clinic_fk=2      Visit Number=1     CPTCode=567

                        Record 6:  Visit_UID=6    Clinic_fk=3      Visit Number=1     CPTCode=123

                         

                        Does this help?

                        • 9. Re: Multiple Value Frequency Search
                          philmodjunk
                          philmodjunk Mar 26, 2015 4:43 PM (in response to ScottM_2)

                          I would say that your visit table is misnamed, it's really a treatment table and you don't have the table that you need. A table of visits with one record for every patient visit linked to a table of 0 to many treatment records--which appears to be the actual table that you have here.

                          Patients----<Visits----<treatments

                          If that were the case, you could use my original suggestion to find records on the visits layout by performing a find for all visits with the first cod, then returning to find mode and constraining on each additional code. The resulting found set would be a list of Visit records that list all four treatment records.

                          But how to do that with your current structure?

                          Set up a self join:

                          Visits-----<visits|SameVisitNumb

                          Visits::clinic_fk = visits|SameVisitNumb::clinic_fk AND
                          visits::VisitNumber = visits|SameVisitNumb::VisitNumber

                          visits|SameVisitNumb is a new Tutorial: What are Table Occurrences? of your existing Visits table.

                          Then your find would look like this:

                          Enter Find mode []
                          Set Field [Visits|SameVisitNumb::CPTcode ; 123 ]
                          Perform Find []
                          enter Find mode[]
                          Set Field [Visits|SameVisitNumb::CPTcode ; 567 ]
                          Constrain Found Set []
                          Enter Find Mode []
                          Set Field [Visits|SameVisitNumb::CPTcode ; 910 ]
                          Constrain Found Set []
                          Enter Find Mode []
                          Set Field [Visits|SameVisitNumb::CPTcode ; 1112]

                          From there you may want to sort your records by clinic_fk and visit number to group the visits in meaningful groups. A sub summary layout part--possibly used to replace the body so that you get one record per visit instead of 4 records per visit might be helpful.
                           

                          • 10. Re: Multiple Value Frequency Search
                            ScottM_2
                            ScottM_2 Mar 27, 2015 12:17 PM (in response to ScottM_2)

                            Then your find would look like this:

                            Enter Find mode []
                            Set Field [Visits|SameVisitNumb::CPTcode ; 123 ]
                            Perform Find []
                            enter Find mode[]
                            Set Field [Visits|SameVisitNumb::CPTcode ; 567 ]
                            Constrain Found Set []
                            Enter Find Mode []
                            Set Field [Visits|SameVisitNumb::CPTcode ; 910 ]
                            Constrain Found Set []
                            Enter Find Mode []
                            Set Field [Visits|SameVisitNumb::CPTcode ; 1112]

                             

                            I set this up, as outlined above, but once the first SET FIELD and PERFORM FIND is performed for "123", the rest of the entries in CPTCode_fk have been filtered out so all that shows are rows that have "123" in the CPTCode_fk field.  In other words I don't see how (and the results seem to confirm) once you have already set a field to a specific code further constraining the search cannot expand on the results.  I freely admit I may not fully grasp these commands (this is my first Database), but when looking at the results of the script above my thoughts appear to be confirmed. 

                            However, I tried to "reverse" your recommendation by setting the CPTCode_fk field then using using an EXTEND FOUND SET.  This "works" but this seems sensitive to the order the CPTCode_fks are entered into the EXTEND commands.  It isn't perfect since I am looking for ONLY results that include ALL 5 codes.  Since the script performs the EXTENDs in the order outlined in the script and a "clinic_fk" meets the first 4 EXTEND criteria, but not the last, it is still listed. 

                            Here is how my script looks for the EXTEND

                            SHOW ALL RECORDS

                            ENTER FIND MODE []

                            SET FIELD [Visits|SameVisitNumb::CPTcode ; 123 ]

                            PERFORM FIND[]

                            ENTER FIND MODE []

                            EXTEND FOUND SET [RESTORE] - the find is set to Visits|SameVisitNumb::CPTcode ; 567

                            ENTER FIND MODE []

                            EXTEND FOUND SET [RESTORE] - the find is set to Visits|SameVisitNumb::CPTcode ; 910

                            ENTER FIND MODE []

                            EXTEND FOUND SET [RESTORE] - the find is set to Visits|SameVisitNumb::CPTcode ; 1112

                            SORT RECORDS

                             

                             

                            If you haven't lost patience yet, I would love to attempt your method, but I haven't been able to grasp the approach.  

                            • 11. Re: Multiple Value Frequency Search
                              philmodjunk
                              philmodjunk Mar 27, 2015 1:26 PM (in response to ScottM_2)

                              That sounds like you may not have set this up to work from the correct layout or with an incorrectly defined self join relationship.

                              What I specified tells FileMaker: "Find all records in visits that have at least one related record in Visits|SameNumber with treatment ID 123". This should find all visits with that number, yes, but you have indicated that you want all visits where all four treatments were specified for the same patient at the same visit. So this should find all patients that fit your criteria but also should find some that received treatment 123 , but did not receive treatments with the other 3 codes in the same visit. The subsequent "constrains" on the resulting found set should pare the group of records down until you only have records where the same patient in the same visit received all 4 treatments.