Sign in to follow this  
Followers 0

Finding records across many tables

23 posts in this topic

Posted

I need to create a database holding the results from various tests on scientific samples.

The only data that is common to the test results is the sample they were carried out on so I have placed the results from each test into individual tables, in my test database I've set up these tables but now have a problem finding records.

In order for the database to meet it's purpose I need to be able to identify particular subsets of samples from results across all the tables, For example I need to find any samples that have value x in a particular field of table 1, value y in a particular field of table 4 and value z in a particular field of table 9 (i.e any combination of fields in any table).

 

As I understand it in find mode FM can only search records in one table, if this is the case does anyone know if I can get FM to carry out the type of search I outlined above? Is there a particular structure I must put in place to achieve this?

0

Share this post


Link to post
Share on other sites

Posted

As I understand it in find mode FM can only search records in one table

 

That's almost correct. More precisely, in Find mode FM can only search for records from one table. The search criteria, however, can include related fields. In your case you are trying to find samples, so you can go to a layout of the Samples table, enter Find mode and enter your various criteria into fields of the various related tables.

 

 

That said, the search would be faster if carried out in a single table. Are the tests really that different from each other (in terms of fields necessary to describe them) to justify separate tables?

0

Share this post


Link to post
Share on other sites

Posted

Hi,

 

For this search method, to search for specific subset sample across multiple tables, can use the Execute SQL statement.

 

Here we can use the %LIKE% operator to get the values from the fields for the Subset of the samples.

 

We can get the values from different tables for different samples using the separate Execute SQL statements and concatenate all the values into a single list of value.

0

Share this post


Link to post
Share on other sites

Posted

That's almost correct. More precisely, in Find mode FM can only search for records from one table. The search criteria, however, can include related fields. In your case you are trying to find samples, so you can go to a layout of the Samples table, enter Find mode and enter your various criteria into fields of the various related tables.

 

 

That said, the search would be faster if carried out in a single table. Are the tests really that different from each other (in terms of fields necessary to describe them) to justify separate tables?

Generally the tests are very different, there may be scope to combine some into one table but probably not all. I could try to combine everything into one table if it offered advantages or was the only way of achieving the goal though.

 

If the all the tables were related though, say a participant ID, present on all tables could I create a "find" layout that had the fields from all the tables required to drill down into the data enter the search criteria and display the found set in a portal?

Hi,

 

For this search method, to search for specific subset sample across multiple tables, can use the Execute SQL statement.

 

Here we can use the %LIKE% operator to get the values from the fields for the Subset of the samples.

 

We can get the values from different tables for different samples using the separate Execute SQL statements and concatenate all the values into a single list of value.

 

I'm new to trying to develop FM in a serious way and not at all familiar with how to use SQL statements, although willing to learn if it's the only solution. Is it something that is fairly straightforward to learn?

0

Share this post


Link to post
Share on other sites

Posted (edited)

If the all the tables were related though, say a participant ID, present on all tables could I create a "find" layout that had the fields from all the tables required to drill down into the data enter the search criteria and display the found set in a portal?

 

You cannot display the found set in a portal. And there is no need to relate all the test tables among them - the "star" relationship that you have now is sufficient.  But you can create the said "find" layout, based on the Samples table, showing "the fields from all the tables required to drill down into the data".

 

The found set will be samples having the test results specified by the find criteria, so you display them right there in the layout of the Samples table. Note that if you have portals to any of the test tables, they will keep on showing ALL related tests - not only those that were used to find the records.

Edited by comment
0

Share this post


Link to post
Share on other sites

Posted

Thank you for your reply comment, I'll play about with a "find" layout.

Because there is potentially a large number of fields to place on this layout, are there tips for tidying them up, tabs or drop downs maybe?

0

Share this post


Link to post
Share on other sites

Posted

Hard to say without seeing the data and understanding the workflow. Offhand, tabs sound like the most reasonable approach.

0

Share this post


Link to post
Share on other sites

Posted

I must be completely missing something here!

I've set up a test layout under the samples table and put a couple of fields from the related test results tables, along with value lists based on the records field values.

 

On entering find mode, selecting a value and performing the find I'm not getting the correct found set. The pie chart seems to indicate the correct number of found records but when clicking through them they are not the correct set.

 

Does anyone have any suggestions on what I might be doing wrong?

0

Share this post


Link to post
Share on other sites

Posted

It's not possible to make suggestions without knowing more. How do you conclude that a record should have been found but wasn't - or vice versa?

 

 

If I had to guess, I'd say that you haven't read this part carefully enough:

 

Note that if you have portals to any of the test tables, they will keep on showing ALL related tests - not only those that were used to find the records.

0

Share this post


Link to post
Share on other sites

Posted

I think I've got something fundamentally wrong.

I've not set up any portals in this test layout, just a field from each of the related tables. When I search just one field on this layout the found set indicates that 36 records have been found, but when clicking through them the selected value is not present in all of them.

When performing the same search on the default layout for the table the value is found I get 36 records all with the selected value present in the found set.

 

Could it be that I've set up the primary and foreign keys incorrectly?

0

Share this post


Link to post
Share on other sites

Posted

I think I've got something fundamentally wrong.

 

No, this is the same problem I suspected, only in a slightly different form. Actually, it's not a problem at all, just a cosmetic issue: the fields from the related tables that you have placed on the layout display the data from the first related record.

 

For simplicity, consider two related tables with the following records:

 

Smith -< {Adam, David, John}

Jones -< {David, Eve, Frank}

Brown-< {Charles, George}

 

Now, if you search the Parent table for Child="David", you will find Smith and Jones. However, the display on the Parent layout will be:

 

Smith  [Adam]

Jones  [David]

 

---

Note: "first related record" means first in the order of the relationship; if you haven't specified a sort order in the relationship's definition, records will be shown in creation order.

1

Share this post


Link to post
Share on other sites

Posted

Is there a solution to this problem or is it a quirk of FM?This is happening with just a single field search, eventually I'd like to be able to perform searches based on a number of related fields.

 

In my scenario when 36 records are returned from my find layout only 14 have the actual value I wanted. On the layout based on the table where the value is held all 36 show the value.

0

Share this post


Link to post
Share on other sites

Posted

I am afraid you are still not with me.

 

 

In my scenario when 36 records are returned from my find layout only 14 have the actual value I wanted.

 

I don't think that is a correct representation of what happens. I believe all 36 records do have a related record with the actual value you have searched for. You can check this by placing the field from the related table inside a portal. Then you will see that the value is there - it's just not in the first row of the portal.

 

So this is merely an issue of display, not a problem (or "quirk") in finding the records.

 

One way to solve this is to keep the search criteria in a global field or variable and use it to filter the portal, so that only (related) records that meet the criteria are shown. However, such solution may not be practical when you have many related tables. One more reason to consider again the possibility of uniting them into a single table, perhaps using the EAV data model.

0

Share this post


Link to post
Share on other sites

Posted

Thanks for taking the time to reply comment.

You're right of course, when the adding the portal the looked up value is there.

 

You're also right in saying I'm still not with you, I don't understand the mechanics behind why it happens, I'm just not "getting it". I'm probably too focused on my vision of how I expect the found set to be displayed and don't have the understanding to make it happen.

 

A single table might be the only answer, but I was was under the impression that would be considered "bad practice". I'd happily upload my test file if you were able to offer any pointers or opinions on it.

0

Share this post


Link to post
Share on other sites

Posted

I think you need to ask yourself what exactly is the "found set" here - and even more importantly: where is it?

 

Suppose you went to Italy and ask some people there: which of you have relatives in the US? And say some 15 people raise their hand. Well, that's your found set - and it contains 15 Italian people. No American people are shown.

0

Share this post


Link to post
Share on other sites

Posted

That makes it a little clearer. I'll ponder how to ask the Italians for more information!

0

Share this post


Link to post
Share on other sites

Posted

(good example by Comment)

 

And if you then ask your set of 15 Italians to show you their address books (portals) you might see the list of relatives.

 

But you're only looking at the address book. Collectively across their address books their might be 136 USA relatives.

 

But when you ask the question, 136 USA people do not suddenly appear before you in the flesh in Italy. Your "found count" does not change.

0

Share this post


Link to post
Share on other sites

Posted

And if you then ask your set of 15 Italians to show you their address books (portals) you might see the list of relatives.

 

if you ask them to show you their address books, you will see a list of all their relatives, no matter in which country they live. So the question is really how to filter their address books (i.e. portals) to show only US residents. And this question is asked separately from the first one.

0

Share this post


Link to post
Share on other sites

Posted

I have said it before but the lack of global search through all or specified tables in a solution is a missing feature in Filemaker. I wonder, with datasets growing rapidly, if database software will not move to storage similar to Google's Big Table. The latter is clearly designed for massive datasets and for very rapid search and retrieval. It also allows for the creation of relationship-like tables on the fly. An example would be, when using Google Maps, to find, say, all dentists in and around the area being displayed.

 

Sorry if this is a diversion from the question but the experts seem reluctant to recognise that this lack of fast, global search is an issue.

0

Share this post


Link to post
Share on other sites

Posted

I don't know about the experts, but I am reluctant to discuss "wants and wishes" in conjunction with specific how-to questions pertaining to the currently available system.

0

Share this post


Link to post
Share on other sites

Posted

Are there any practical options, that anyone could suggest, that I can explore that will help me solve my conundrum? i.e A way of displaying the actual records from the related tables that show the values matching my search criteria from a "find layout".

 

If not, and the only (or best) way is to have a single table of all test results what would be my best options to create this table. Create a new FM table that would populate itself from the existing tables in FM, or create from my starting material (Excel worksheets)?

 

Any advice appreciated, I've attached my basic test FM file that may illustrate my problem better.

0

Share this post


Link to post
Share on other sites

Posted

Opps! forgot to attach file

samcim.fmp12.zip

0

Share this post


Link to post
Share on other sites

Posted

I am currently limited to v.11, so I can't look at your file. In any case, I believe the available options have been presented: either store your search criteria in global fields or variables and use these to filter the portals, or unite the child tables into one. The first option, aside from being laborious, will get progressively slower as the number of portals to filter increases.

 

Note however that if you do unite all you test results into one table, and you search that table, your found set will be test results - not the tested subjects. If the same subject has been tested twice in the same category, and both test results meet the search criteria, you will have two records in the found set representing the same subject. OTOH, if you perform the find in the subjects table, you will face the same problem as you do now - since some found subject may have other test results that do not meet the search criteria.

0

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0