Searching table for no related records

[bcolburn]

I have the following relationship:

Patients -< Patient Diagnoses

Every Patient Diagnosis displays fields to record the diagnosed condition (selected via drop-down) and several related descriptors RE: severity, treatment, etc.

I know how to search the database for all patients who are diagnosed with a particular condition, but is it possible to search for all patients who were NOT diagnosed with a particular condition?

This is a research database and the end goal is to determine the prevalence of a condition among our study patients. Any feedback would be much appreciated.

[Vaughan]

One way is to find record with the condition and omit those records.

However if need something that allows a display of the un-diagnosed conditions:

1) Create a list of all conditions. Best in a table of their own.

2) in each patient record, create a list of diagnosed conditions using the List function on the Patient Diagnoses relationship.

3) Subtract the two lists. A custom function can do this.

This list will be unstored so finds will take some time, and will become significant as the count of diagnoses becomes larger.

The two lists could be stored to speed up searches but an update process will be required to ensure they do not get out of sync as new diagnoses are added to the database, and as patient diagnoses are added and removed.

It could be possible to use the unstored lists to update the stored lists before doing a round of searches.

Testing is required to work out whether the search times really are a problem, they may be OK even with unstored values.

[David Jondreau]

In Find mode set the related primary key to * (wildcard which will find all records with a related record), but Omit that request.

[bcolburn]

Great! Thanks!

[LaRetta]

Hi Ben,

I cannot help but wonder about your overall structure. You should be using a join table. If you do, then finding all patients without a diagnosis will be as simple as going to the Diagnosis table and finding the diagnosis you want to report on and then simply issuing a Go To Related[].

By using a join table, reporting will be piece of pie as well, in any statistical fashion you wish.

UPDATE: I had neglected to add the script to the first button for GTRR to patients with a diagnosis. I have corrected the file.

notFound.zip

Edited November 28, 2011 by LaRetta

[bcolburn]

Vaughan - what is the advantage of / reason for creating a link table and using a separate table for diagnoses as opposed to using using a one-to-many as I have done?

[Vaughan]

Because it's not a one-to-many relationship.

A patient can have many conditions

A condition can apply to many patients

many-to-many

The join table should be Diagnoses.

patients - diagnoses - conditions

[bcolburn]

Well I suppose that's a pretty good reason. Got tangled for a second - thanks for bringing me back to the basics.

Hi Laretta - That did solve my problem, kind of. The thing is the database itself doesn't need to be able to count/analyze people with/without every particular condition. We just need to be able to do so at the end of the research study for statistical analysis. So I was mainly inquiring with that in mind. Do you think your solution is still the best way to anticipate those requests?

[LaRetta]

Do you think your solution is still the best way to anticipate those requests?

Well, you show two tables and I believe you should have three. It appears that Vaughan agrees although I will not speak for him. My file shows three tables utilizing a join table between Patients and Diagnoses (although I named them differently than Vaughan, I see). This center table holds the individual lines, one for each patient/diagnosis. My reason for presenting the demo file was that, if you have this 3-table structure, your current request will be simple (whether you solve it with the idea I presented also in the file or by other means).

You already knew something was wrong when you posted and the answers were not simple. So already, you have been given signs. The goal defines the solution and you said:

This is a research database and the end goal is to determine the prevalence of a condition among our study patients.

If you want to be able to retrieve, report and count your data in multiple configurations, a join table makes most sense. That is my opinion but I am not a Master of Relationships (ask any of my friends, LOL). I hope others provide input as well.

[bcolburn]

Hello again. So all of these solutions were helpful, but I am now wondering how to apply them to several different link tables at once. I have multiple link tables that function this way (prescriptions, diagnoses, etc.) and all of them need a count as described here. In Laretta's solution, she retrieved a list of patient IDs from the link table and used a relationship filter to exclude them from a second TO of patients, thereby creating a portal of patients without records in that particular table. However, it seems this solution requires referencing the specific patient ID foreign key in a link table to work, thus requiring a unique calculation field like LaRetta's for each link table of interest. Is it possible to somehow create a field that can recognize the link table I'm interested in and reference that particular foreign key when necessary? For example, when I enter the layout for a diagnosis or condition. I thought about using a temporary variable to detect which foreign key to count based on the active layout, but that would still require a pretty long case() function, which usually means I'm doing something wrong. I also want to make sure that however I solve this, a count can be exported at any time, even if I am not on the layout. That is to say, I want to be able to get a snapshot of all counts at once and not have to scroll through every single diagnosis record and transcribe a count once the field recognizes the layout/record. Does this make sense? What is the best way to operationalize this idea?

[bcolburn]

No response = unclear?

If you need any clarification or just want me to start a new thread, let me know.

[Lee Smith]

.... just want me to start a new thread, let me know.

NO!

Clarity and brevity are two important things to Keep in mind when posting a Questions.

Try attaching an example or demo file of what you are are trying to accomplish. That way we will be using your fields, tables, scripts etc. when discussing your files. This will let us see what you're doing, and you will be able to relate to what we're saying better..

HTH,

Lee

[Vaughan]

I've stopped replying because you've reached the point where you need to get some professional help in to get things sorted out.

There is only so much we can do through the forums, and only so much time we're prepared to give freely.

[LaRetta]

Hi Ben,

Your opening thread shows a 1:n (one-to-many) and we are suggesting you use a join table as outlined in my file. Forget the added table occurrence for Patients without a diagnosis you have specified - that is specific request you made "is it possible to search for all patients who were NOT diagnosed with a particular condition?" You received many responses.

But the underlying message (and why I even stepped into this thread) was because I wanted to stress again that you needed a many-to-many relationship (which I presented in my file) as: Patients -< PatientDiagnoses >- Diagnoses. And in same file and with that structure, you could then use the Diagnoses table for searching, counting, or displaying patients in portals - I tried to show you the flexibility it would provide. But you can use the Patients table as well (as Vaughan showed). You never told us whether you made that change.

I want to be able to get a snapshot of all counts at once and not have to scroll through every single diagnosis record and transcribe a count once the field recognizes the layout/record.

Why do you think you have to scroll through every single diagnosis? This thread started with a search by a User looking for Patients without a specific diagnosis. Now you want a report - they are different requirements! Reporting can be generated from anywhere really, it all depends upon what you want. With FileMaker, everything is perspective. You made it clear that reporting is "to determine the prevalence of a condition among our study patients." That means that a Diagnoses table is an important perspective in your design although not the only one. Reporting can take place in the join table, leading part based upon PatientDiagnoses::DiagnosisID, sorted and summarized (use summary field in this join table which counts DiagnosisID). You can even delete the body and just show the Diagnosis name and the counts.

But you mention "and several related descriptors RE: severity, treatment, etc." and again, "I have multiple link tables that function this way (prescriptions, diagnoses, etc.) and all of them need a count as described here." I cannot tell you how to solve reporting issues with these tables because I do not know how they are related. And 'reporting' is a huge subject with more configurations and possible solutions than you can imagine and I do not know your specific reporting or exporting requirements.

Each of your tables (prescriptions, diagnoses etc) should be considered individually (and as a whole). And, as Lee suggests, when presenting a question about relationships, it is usually best to provide a sample file showing only fields affected with the keys and how they relate, including a few dummy records if it helps clarify meaning. And be prepared to ask & answer questions such as, "Does each PatientDiagnosis have prescriptions tracked against them or are prescriptions only tracked against the Patient?" These questions are the only way that you can determine where to attach Prescriptions for the primary relationship.

I am not sure I have helped; I hope so.

[bcolburn]

All great feedback. Thanks for your help, everyone!