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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
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?
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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...
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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.