Results from multiple tables in one single table


I have two related tables that both can be searched. In the results table I'd like to show the found records of both tables in one single list.

As far as I know, I need to have a third table for this; a table that gathers the results from table A and table B. However, I am stuck on that and am looking for a hint where to start. Any idea?

Thank you,
Who is Participating?
cmajkrzakConnect With a Mentor Commented:
Hi Polarpro,

To answer your question about inserting data across a relationship, if I understood this correctly, please review my attached screen shot.  In the example we have Tables 1 and 2, related together, with allow records to be created in this table checked on 2.  While we're in a record in Table 1, if we attempt to set a field in table 2 such as text2, the value of table 1's pkt would be put into table 2's pkt field, creating the related record, and setting the field of data.

Now to do a search between two tables like this you would need to do something like the following to search table A, then Table B, and then perform the find.

-Go to layout that includes the fields you want from table a and table b
-Enter find mode
-Set field .... (set all the fields in table A you wish to find)
-New Record/Request
-Set field .... (set all the fields in table B you wish to find)
-Perform Find

Doing this will search table A with all that you requested, and then it will search table b with all you requested, and then the results will be displayed together thanks to the new request between them.

Take a look at this link and see if it's what you're looking for:
polarproAuthor Commented:
Hey D0N,

Thank you for your answer.

To set up a join table is not the problem. I use them quite often; the join table's records have the other tables' primary keys as foreign keys. Works fine. However, I wonder how you make Filemaker automatically create new records in such a table. Would you mind pointing out a few script steps?
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I'm not sure I'm following.  I'm (hypothetically) thinking of part numbers for company A in one table and those of company B in another table.  You'd like to combine and display a subset of both tables in one list?
polarproAuthor Commented:
Hey D0N,

Thanks for your answer.

Yes: First I'd like to do a search in table A, and then I'd like to do a search in table B. Then I'd like to show the results of both tables in one list. For this list, as far as I know, I need a join table, where each record is either a found record from table A or a found record from table B.

If you think that this way of searching comes along with loads of rocks on the way, let me know; then I should change the setup of the database...
D0NConnect With a Mentor Commented:
As food for thought, there's another approach worth considering.  Rather than use relationships, you could export the found sets from the vendor tables, then import them into a third, all-inclusive table.  The advantages with this is approach is that you could add as many vendor tables at a later date without having to rewrite your whole database.  That and, well, it's simple.
polarproAuthor Commented:
Dear cmajkrzak,

Thank you very much for your detailed answer! I went through it and like it.

However, what I had in mind is something like D0N described in his last message (and like I hoped to describe in my first message...) for the stated reasons.

I think I'll try something out like:
performing search in tableA
creating new records in the results table, based on the found set
performing search in tableB
 creating new records in the results table, based on the found set
displaying all the results from A and B in the results table

The export / import step seems to be quite useful here...
All Courses

From novice to tech pro — start learning today.