Search simultaneously tables

[marysilvaramos]

Hi Guys:

I would like to know how my DB can search in several tables at the same time based in a request. i.e.

I have the following tables: Medications, Problem List, Insulin Control, and Patients Info.

How can I make a search of all the patients that have had Tacrolimus (medications table) and Headache (problem list) and Insulin>30 (insulin control) and Age=50 (Patient Info)?

The tables are related through Ids. I pasted the fields in the interface table, created a find but they only bring one record not all of the records from all tables.

Let me know if you have a way to bring all the records related by a record from all the tables. The result should be exported to Excel/SAS (or another statistic program) to create graphs.

Thanks in advance,

Mary

[Fenton]

Short method: You must have the PatientID in all tables, and a direct relationship from Patients to all other files, based on that ID. In the Patients file you can then put the relevant related fields on the layout. You don't need portals for this, just the related fields.

It sounds like that's what you did. But I don't understand your "they bring one record not all of the records from all tables" statement.

The Find is going to find any Patients who match all the characteristics. What do the records in the other files have to do with it?

If you took the related fields (above) and arranged them for a list view, you'd see mulitple records, with the data from the other tables (should be the same as the Find criteria in this case). If you exported the related data (with the Patient of course), you would get the data.

Long method:

Capture the Find criteria into global fields.

Do a Find in the Patient file. Copy/paste the Patient IDs into a global field.

Go To Related Record [show, "global ID relationship"] to the next file.

Do the next Find, with Constrain Found Set. Trap for none found error. Use the global criteria from Patients.

Repeat with each file until the bottom of the stack.

Capture the IDs once more, but Go To Related Records back to Patients (reverse relationship).

This method will be faster in really large files, except it will break at the 64,000 character limit, which is 10,666 IDs for a 6 digit ID. To get past this limit you could start at the file you think would have the smallest found set, and work your way back. The order doesn't matter.

Or upgrade to 7, which has a 2 GB character limit, and is also much faster at related Finds (so the short method would be much faster).

[marysilvaramos]

Thanks Fenton for your reply.

"But I don't understand your "they bring one record not all of the records from all tables" statement."

I would like to make a search as SQL does it. I want to set the fields and tables names and bring the records from several tables at the same time. All the tables have the Patients IDs to make relationships among them, however, my users might make searches where the patient name is not the principal key, How these kind of searches can be executed by the user? They want a tool to make any possible "query" in the DB. I do not know if I explain myself well.

Do you have a file that I can download to apply the long version?

Thanks

Mary

[DanBrill]

I think what you are trying to do is called a 'union query' in SQL terms. Say I have 2 tables, one called People, one called Businesses. Each table has a field called "Name". I want to search the Name field of both tables at once for "Bill" and find "Bill Smith" in the People table, and "Bill's Market" in the Businesses table.

Am I stating this correctly?

If so, then I think what you need to do is create a 3rd table called Union. Import all your records from People and Businesses, and perform your find on Union.

You'll want each field in Union to have a unique ID, so I'd create calc fields in People and Businesses that take their unique id's and append a letter "p" or "b" onto them so that you know the source file of any record in Union. So ID's in Union might go something like 1-p, 2-p, 1-b, 3-p, 2-b, etc.

The import into Union is a bit of a trick too since you'll have to update existing records, add new ones, and delete ones that no longer exist in People or Business. Updating and adding records is easy -- just check off these options on the import dialog. The real trick is when you need to get rid of records in a table that don't have a matched record in the dataset you are importing in order to keep 2 data sets in sync. In this case you essentially do the import twice.

First you import the data you need using the "update matching records in current found set" option. You also import one additional dummy field, set to 1 in the data you are importing, and empty in the dataset you are importing into.

Then you create a self join relationship on whatever field you are matching on. That is, each record will be related to its matching record. Create a calc field that is count of the number of records in the self-join relationship. So if a record has no match, the count is 1. If it has a match, the count is > 1. Find all the records where the count <= 1 and delete them. Then find all the records where the dummy field = 1. Delete these.

What you are left with is your original set of records less those records that won't have a match in the data you are importing. Then import the data one more time with the "update matching records in current found set" option. Now both sets of data are in sync.

Finally, you can perform your finds on Union.

Think through what this will mean in multi-user settings too. Users may end up lacking each other as they try to create and delete records. It may not matter much though.

I'd love to know if anyone has a more streamlined process than this. This kind of query is a real weakness in the FM relational model, but is something that is pretty easy in SQL. As always, I'm open to suggestions.

Hope this helps,

Dan

[Ender]

Mary, try your search the easy way first. If it runs too slow, then give Fenton's long version a try. The easy way is simply putting the related fields on a layout in Patient, then performing a find and typing your criteria in all relevant fields.

This can of course be scripted to make things easier for your users.

[Fenton]

Ender is right. Just try the related search first (not portals mind you, just plain related fields, related on the PatientID). The long version is way more trouble. I could whip up an example file, if you "find" the other too slow. But otherwise it's not worth it; it is really the same thing.

Here's the basic principle: When you're searching for this data, from the Patients file, you are looking for "patients" whose (related) data matches the criteria you're entering.

That's why I didn't much like the phrase "bringing records from other files." It's misleading. You are not bringing records (of which there may be several matching per patient in each file), you are finding patients who have at least one record in the other files that matches the entered criteria for that file. It's confusing, but if you think about it you'll see that it's so.

If, as you say, they are sometimes searching for records that are NOT about patients per se, that are more about Medications, then they need to be taken to the Medications file/table and search there. They are not going to find the multiple medications records they need by searching in the Patients file. Sometimes they need to go where the data is.

[You could alternatively capture their "find" criteria in global fields, analyze it, and do your own searches, but that's another "long" version.]

Dan: I don't think she's doing that kind of search. There is a somewhat simpler way than creating a whole separate table. Which is to do the Copy All Records technique in each table, then show the results in 2 portals. Hopefully there would not be so many hits that it would be awkward (maybe "Bill" would). The separate table would work well also; but you'd need something like the Example plug-in to keep them synchronized.

[DanBrill]

Yeah, in carefully re-reading her post, I don't think that is the kind of find she's after. I like your Copy all records idea, as well as the example plug in idea. I use the example plug in to keep data and calculation tables synched in the separation model I'm working on.

Sorry for the red herring.

Dan

[marysilvaramos]

Dear All:

Thanks very much for all your responses. Maybe I did not explain myself well, but Dan's answer gave me some ideas how to do it. I am using the separation model to prevent fields

[DanBrill]

Hi Mary,

I just came up with a nice solution combining my idea with Fenton's about using the example plugin. I've been using this technique elsewhere, but hadn't thought of applying it here. Works great, and is a lot faster and cleaner than the import export method. Then I noticed that you are using version 6. Arrgh. Won't work there.

Any chance you would be using 7?

I'll think of a way to do this in 6. Sorry. I've attached it so ver 7 users can see it.

Union.zip

[Fenton]

I still maintain that whatever tool you use you need to specify WHAT you want to find, and then the criteria. Even in SQL (which I know little about admittedly) you specify that you want to find WHAT whose some field (cell) equals/doesn't equal some criteria.

Are you saying that you want to do Finds that are not finds for patients per se?

The problem is that there are not only different tables, but there is a single record in one (Patients) and multiple records - just for that patient - in other tables. So when you enter criteria from multiple tables into a Find (which is not a problem) the database has to know where to end up, in order to show you want you want to see.

If you ended up in Medications you would see far more records that you would see in Patients, for essentially the same Find; because each patient may have several entries for that particular medication.

This is handled transparently in FileMaker, because the results are shown in the file where you started. If however you want to have some kind of "generic" find, that can return results from any kind of criteria, in any table, and display the results according to WHAT they are looking for, then you'd have to either analyze the criteria first, or let them specify where they want results displayed. Some combinations of criteria and results display may not make sense.

You would have more or less that same problem if you used Dan's idea of a "catch-all" file. Because it would have many records for each patient. If they were really looking for "patients," but entered criteria as you said, for medications, etc. (which is normal), they would find those multiple records/patient; and that's not really how they want to view the results.

The catch-all file/table would involve making sure every record and its relevant data is duplicated into the table; every edit passed to the table. It would be a very large file.

It would be a heck of lot less work if people would just go to the file/table where they want to see the results and do the Find in related fields (if applicable; I don't see how the other files are related other than through Patients). This could be done with a simple radio button list of the relevant tables, like your "dictionary" idea.

Sorry, this answer is kind of confused. I can't see exactly what you're trying to do that can't be done with a simple find in related fields in Patients, or, in other cases, by going to the file where the data is.

It doesn't make any sense to enter criteria for a single Find in unrelated files/tables (in fact it would be difficult to do so). It seems to me that in your solution any finds in more than one file would almost HAVE to be patient-related.

It would be a bit more transparent in version 7, because you could open a new window with the results, leaving the existing tables/windows as they were.

[marysilvaramos]

Dan & Fenton:

Thanks very much for your replies.

Dan: It is a long history, but I cannot move to 7 in this moment. I am making the modifications to do it.

Fenton:

"I still maintain that whatever tool you use you need to specify WHAT you want to find, and then the criteria. Even in SQL (which I know little about admittedly) you specify that you want to find WHAT whose some field (cell) equals/doesn't equal some criteria."

Yes, it is true the users need to know what they want to find. In other DB that I have created, I implemented a tool using SQL and variables (Access and FoxPro). In the interface, the user fill out the variables with the names of the fields and tables, when the search button is pressed, the program replaces the values in the SQL statement and the search is done. I do not need to be in any table to make the search, just use the SQL and that is all. In this case, all the tables have relationships to the patients table, and have the relationships to relate the tables with each other

[Fenton]

Are you using the FileMaker Accounts and Privileges to control access/editing of fields? It's quite powerful, and with the new ability to script the creation of accounts is almost painless (relative to version 6 anyway).

I haven't done much with the Separation model as yet (my clients tend to be small). I imagine it's a good idea for what you're doing. Then you wouldn't have to import the entire data set often.

As far as "unlimited" searches, yes, it's a good idea and doable. But I still maintain (being stubborn) that they need to specify where they want to end up. And I think some analysis of the criteria might be a good idea.

Global fields are where the criteria would go, before the Find(s) are carried out. They can be in any file or table. You would need one for any field that could be searched on (at least for user searches).

Your example:

> How can I make a search of all the patients that have had Tacrolimus (medications table) and Headache (problem list) and Insulin>30 (insulin control) and Age=50 (Patient Info)?

This is actually a fairly simple example in some ways, because it's obviously a "patient" search. Because all your files are directly tied to Patients, this could be done simply by entering Find mode, setting the "patientID::related field"s to the globals and Perform Find. Not speedy, but adequate.

For more speed you could implement the Copy All Records technique, going through the tables, narrowing down the set with each Go To Related Record [show, global multi-line field relationship::Patient_ID]. Tedious, but faster, especially in large files.

Another search example:

How can I make a search of all the medications where the patient also had a Headache (problem list)?

This is NOT a patient search. You would want to view the results in the Medications table. There's no direct connection between medications and problems, except through the patient, who had both.

You would first search the Problems for "headache." This would give you a list which contained the PatientIDs. You'd use Copy All Records (on a layout with only PatientID) to get those IDs, paste that into a global field.

A relationship from that global to the PatientID in Medications would quickly show medications where the patient has also had a headache (at any time). There would be duplicates however. So you could either do summary Preview report to show them, or a Sort & Loop to omit duplicates.

This is perhaps a bad example. Not very scientific information; you'd be better off with an optional field (table) in Problems to note which medication(s) they were taking at the time. Or compare the date of the problem with the date of the medications (yikes).

Maybe (hopefully) you're ALWAYS searching only for patients. But "unlimited" sounds like they could enter any criteria in anything and expect to get meaningful results.

In my example above, you would not know that they wanted to view the results in Medications unless they told you (radio button choice).