Jump to content

  •  

Photo

Finding records across many tables


  • Please log in to reply
22 replies to this topic

#1 eagleaye  member

eagleaye
  • Members
  • 12 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 11h 33m 55s

Posted 17 January 2014 - 03:29 AM

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

#2 comment  consultant

comment
  • Members
  • 23,969 posts
  • Time Online: 319d 16h 17m 49s

Posted 17 January 2014 - 03:47 AM

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

#3 Priyabrata  novice

Priyabrata
  • Members
  • 35 posts
  • LocationIndia
  • FM Application:12 Advance
  • Platform:Windows 7
  • Skill Level:Intermediate
  • Certification:12
  • Membership:FileMaker Business Alliance
  • Time Online: 1d 7h 20m 52s

Posted 17 January 2014 - 03:52 AM

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

#4 eagleaye  member

eagleaye
  • Members
  • 12 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 11h 33m 55s

Posted 17 January 2014 - 05:45 AM

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

#5 comment  consultant

comment
  • Members
  • 23,969 posts
  • Time Online: 319d 16h 17m 49s

Posted 17 January 2014 - 06:47 AM

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, 17 January 2014 - 06:51 AM.

  • 0

#6 eagleaye  member

eagleaye
  • Members
  • 12 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 11h 33m 55s

Posted 17 January 2014 - 07:24 AM

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

#7 comment  consultant

comment
  • Members
  • 23,969 posts
  • Time Online: 319d 16h 17m 49s

Posted 17 January 2014 - 12:58 PM

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


  • 0

#8 eagleaye  member

eagleaye
  • Members
  • 12 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 11h 33m 55s

Posted 29 January 2014 - 08:27 AM

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

#9 comment  consultant

comment
  • Members
  • 23,969 posts
  • Time Online: 319d 16h 17m 49s

Posted 29 January 2014 - 05:28 PM

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

#10 eagleaye  member

eagleaye
  • Members
  • 12 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 11h 33m 55s

Posted 30 January 2014 - 02:09 AM

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

#11 comment  consultant

comment
  • Members
  • 23,969 posts
  • Time Online: 319d 16h 17m 49s

Posted 30 January 2014 - 04:45 AM

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

#12 eagleaye  member

eagleaye
  • Members
  • 12 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 11h 33m 55s

Posted 30 January 2014 - 06:44 AM

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

#13 comment  consultant

comment
  • Members
  • 23,969 posts
  • Time Online: 319d 16h 17m 49s

Posted 30 January 2014 - 07:21 AM

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

#14 eagleaye  member

eagleaye
  • Members
  • 12 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 11h 33m 55s

Posted 30 January 2014 - 08:29 AM

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

#15 comment  consultant

comment
  • Members
  • 23,969 posts
  • Time Online: 319d 16h 17m 49s

Posted 30 January 2014 - 08:42 AM

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

#16 eagleaye  member

eagleaye
  • Members
  • 12 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Novice
  • Time Online: 11h 33m 55s

Posted 30 January 2014 - 09:10 AM

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


  • 0

#17 BruceR  consultant

BruceR
  • Members
  • 3,283 posts
  • LocationRedmond WA
  • FM Application:13 Advance
  • Platform:Mac OS X Mountain Lion
  • Skill Level:Expert
  • Certification:9, 11, 12
  • Membership:TechNet
  • Time Online: 28d 8h 46m 16s

Posted 30 January 2014 - 10:48 AM

(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

#18 comment  consultant

comment
  • Members
  • 23,969 posts
  • Time Online: 319d 16h 17m 49s

Posted 30 January 2014 - 11:24 AM

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

#19 normanicus  apprentice

normanicus
  • Members
  • 167 posts
  • FM Application:11 Advance
  • Platform:Windows 7
  • Skill Level:Intermediate
  • Time Online: 19d 13h 36s

Posted 30 January 2014 - 11:43 AM

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

#20 comment  consultant

comment
  • Members
  • 23,969 posts
  • Time Online: 319d 16h 17m 49s

Posted 30 January 2014 - 11:54 AM

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




FMForum Advertisers