3 Replies Latest reply on Feb 19, 2013 8:46 AM by AshleyWard

    Creating a union of multiple tables?



      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.


        • 1. Re: Creating a union of multiple tables?

               Option 1:

               Take a look at the Import Records script step. Not only can it move data between tables in separate files, it can move data between tables in the same file.

               You can use your separate tables to process your data and when you are ready, Import Records can copy the data from each such table into your "union" table, mapping only the fields that you want.

               Option 2:

               Link each of your separate tables via relationship to your "union" table. Define your "final" fields only in this related "union" table. Use your looping scripts, replace field contents etc operations to move data directly into this related table without ever having to enter the data into fields in the separate tables.

          • 2. Re: Creating a union of multiple tables?

                 Thanks, PhilModJunk.  That really helps to narrow down my choices: I can stop wondering whether I'm missing something now!  I'll give those ideas a try and report back.  (I'll defer this for a bit, though, as right now I only have two data sources and so this isn't critical.)  Much appreciated... Ashley.

            • 3. Re: Creating a union of multiple tables?

                   Just to report back as promised... I've used a combination of option 1 and 2, and the solution is working quite well, in initial stages at least.

                   Some details, just in case anyone (now or in the future) is interested... I have my separate source tables, each with different fields. I added a few standard fields to each, most crucially a primary key. Then I have my "union" table, where I have one record corresponding to each single source table record. In the union table, I have fields for "data source number", "data source 1 primary key", "data source 2 primary key" and so on: each of these primary keys being related to the relevant field within a source table. Then, for the "standard" fields I mentioned (eg STD_X), I have a calculation in the "union" table in the form: Choose(data source number; data source 1::x; data source 2::x) etc. Finally, I populated the "data source primary key" fields in the union table, using File > Import Records > File and selecting the current file (thanks again PhilModJunk, I never would have thought of trying that.) All good!