Creating a union of multiple tables?
I am collecting data from a variety of sources, about geographical locations. Sadly, each data source provides different fields. For example: one source might give name, X and Y, and another source might additionally give postcode. (This is greatly simplified: in reality the information provided by each source is very different, although we'll always need name, X and Y at least.)
I was hoping that I could do the following. Import the data from each source into a separate table, keeping the original fields they provided, with the original names, set out in an appropriate layout. Then add additional "standard" fields to each table, eg STD_NAME, STD_X, STD_Y, and arrange (via calculations or Replace Field Contents) for these to contain the right information, copied from the relevant original fields. Then, create a UNION table, which contains only these standard fields. Set relationships up so that I can see all the records (from the multiple sources) combined in this UNION table. Then, if I were looking at one of these standardised records, I could go to the related record in the original table (and layout), and see the data in the original context there.
So that's the real context. In case that's a bit confused, here is the same idea, but simpler and more formally presented. I'd like three tables A, B and U, with one field each (A_x, B_x, U_x respectively). I'd like U_x to automatically contain all values from A_x and B_x, via relationships, so that each record in U is related to a single record either in A or B. If both A and B each contain a record with value '42', I'd like two '42' records to show in U.
I have FileMaker Pro 11, and have gradually been understanding and appreciating it more and more as I try it out for various things. It's an amazing product... but can it do this? I've tried experimenting a little with the Cartesian product relationship ('x'), but I'm just confused. I would really appreciate any help.