Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

Multiple Value Frequency Search

Question asked by ScottM_2 on Mar 24, 2015
Latest reply on Mar 27, 2015 by philmodjunk

Like • Show 0 Likes0
Comment • 11

Title

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>

No one else has this question

Outcomes

11 Replies

philmodjunk Mar 24, 2015 8:05 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
ScottM_2 Mar 24, 2015 9:45 AM
Mark Correct
Correct Answer
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?
Like • Show 0 Likes0
Actions
philmodjunk Mar 24, 2015 9:57 AM
Mark Correct
Correct Answer
Then take the second example and use Constrain Found set instead of Extend Found set.
Like • Show 0 Likes0
Actions
ScottM_2 Mar 24, 2015 11:17 AM
Mark Correct
Correct Answer
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?
Like • Show 0 Likes0
Actions
philmodjunk Mar 24, 2015 3:10 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
ScottM_2 Mar 26, 2015 6:56 AM
Mark Correct
Correct Answer
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!
Like • Show 0 Likes0
Actions
philmodjunk Mar 26, 2015 7:14 AM
Mark Correct
Correct Answer
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?
Like • Show 0 Likes0
Actions
ScottM_2 Mar 26, 2015 7:44 AM
Mark Correct
Correct Answer

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?
Like • Show 0 Likes0
Actions
philmodjunk Mar 26, 2015 4:43 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
ScottM_2 Mar 27, 2015 12:17 PM
Mark Correct
Correct Answer

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.
Like • Show 0 Likes0
Actions
philmodjunk Mar 27, 2015 1:26 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions

Multiple Value Frequency Search

Title

Post

Outcomes

Related Content

Recommended Content