Filemaker does support UNION. Perhaps you could import the three sets into a common table. There may be other options, but we'd need more details.
Thank you for the response.
We want to generate reports by pulling information from various tables that have similar information (that is why we use UNION in SQL).
Do you think we need create tables for reports that require UNION information? There are many.
Any other suggestions?
We want to generate reports by pulling information from various tables that have similar information
Yes, I understood that much, but I still have no idea what this information is and what's the overall purpose here. Generally speaking, similar information (i.e. described by the same fields) should be in the same table to begin with.
I agree with you that similar information should be in the same table. However, I am generalizing my statement for communicating my question.
The "similar information" I am referring to is the key fields existing in various tables of our application. Each table have many items that are not similar among tables.
Thanks for your help.
I am afraid I cannot help you further with virtually nothing to go on.
Why don't you just set FM to join on the common key? You said you got a key that's being shared among the tables. We do the same to join FM & SQL based tables.
Example of problem:
The organization has stored functions of travel, training, etc. in separate tables. Try to determine certain staff's activities can be achieved by using SQL as:
SELECT StaffID, field1 AS Activity_Date, field2 AS DescriptionFROM Travel WHERE StaffID IN (...)
SELECT StaffID, field1 AS Activity_Date, field2 AS DescriptionFROM Training WHERE StaffID IN (...)
SELECT StaffID, field1 AS Activity_Date, field2 AS Description
FROM Special_Function WHERE StaffID IN (...)
Order BY StaffID,Activity_Date
I have exported those tables to Access and use SQL to fill requests. I am trying to see if I can improve the process.
I hope I make my question clearer and not confuse anyone.
I still don't feel comfortable answering this, because:
Filemaker does not support UNION. There are various workarounds (I have already mentioned one, and I will mention another one below). These workarounds are just that - workarounds. They have their own advantages and limitations, they are not easy to explain and sometimes not easy to implement.
When you post an "example problem", you get an "example solution". Then inevitably comes the stage where you say "but my problem is actually a different one, and this won't work for me". That's why we ask posters to give us background information and some sense of their purpose. Without this, I would now have to write a treatise on the different ways to circumvent the fact that Filemaker does not support UNION, their comparative advantages and disadvantages, with examples of when it's better to use this one over another. Perhaps I will do so - but not here and not now.
Now, the example solution to your example problem is to define a supertype table of Activities and a number of subtype tables (Travel, Training, etc.), with a one-to-one relationship between Activities and each of the subtype tables. The fields common to all activities are in the Activities table, and this table can be used to produce a union report, or a portal of all activities on a person's layout.