Results from multiple tables in one single table

Posted on 2008-11-06
Last Modified: 2012-05-05

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,
Question by:polarpro
    LVL 3

    Expert Comment

    Take a look at this link and see if it's what you're looking for:

    Author Comment

    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?
    LVL 3

    Expert Comment

    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?

    Author Comment

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

    Accepted Solution

    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.

    LVL 3

    Assisted Solution

    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.

    Author Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
    Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now