7 Replies Latest reply on Oct 17, 2010 10:51 AM by HowardRathbun

    Combining two tables into a third table



      Combining two tables into a third table


      I defined a new table with about 4 fields.  Now I would like to run a script that copies a bunch of records from Table1 and pastes (inserts?) them into TableNew.  The records to be copied will be determined by some calculation based on one or more fields.  I would then like to do the same thing with Table2 where the recards are APPENDED to TableNew.  I have played around with this but can't find the right combination of script steps to insert (paste?) them into TableNew. I set the script to loop through just 10 records of Table1 and that part works because the selection arrow is on record #10 in Table1 when the script stops.  But nothing shows up in TableNew.

      How to do this?

        • 1. Re: Combining two tables into a third table

          You can use Import records to import a found set of records from Table1 into TableNew and then again from Table2 into TableNew.

          However, if you are trying to create a brand new table everytime a script runs, you may have troubles getting that to work for you. You can create a new table each time the script runs, but you can't really manage the tables much after you create them. You can't, for example, create a new table, use it to print a report and then delete it when you're done.

          If that's the case, please describe why you want to do that and maybe I can suggest a different approach that avoids the problems, but still produces the results you want.

          • 2. Re: Combining two tables into a third table

            I tried importing (or exporting) but can't get the correct script steps to make it happen.  It seems like import and export are designed to deal with tables in two different databases.  But what I want to do is much simpler than that.  It's analogous to combining two customer lists (in two tables) into a new table all in one database.  The error I get when importing has to do with the fact that I didn't design the path to the target table correctly - and I can't figure that out.

            I am not creating a new table in this operation.  I created the NewTable before hand and it starts out empty.

            • 3. Re: Combining two tables into a third table

              Is this a one-time thing or an on-going process?  This much data manipulation is not usually necessary.

              Howard said, "It seems like import and export are designed to deal with tables in two different databases."

              Not so but your terminology might be skewing your understanding (and this misunderstanding is common in the world of FileMaker).  There is no such thing as a database in FileMaker.  Filemaker has files which contain tables (which are similar to databases within databases).  You can import from one table to another (within same file).  You can import from one table in one file to another table in another file.

              Simply, we need to understand the context of what you are doing.  What are the names of these tables and what is the data each represents and why are you attempting to 'merge' partial data from two different tables.  I propose that, if you are designing on on-going process then you have an incorrect data structure and the two tables (or tables within files) should be together in ONE table to begin with.

              Remember we can only see your words on this post - we cannot see your tables.  :^)

              • 4. Re: Combining two tables into a third table

                Thanks for your help LaRetta.  Yes I am confused by the terminology in FileMaker.  The FM opening screen talks about creating a database, importing a database or opening a sample database.  And the 1st chapter of the reference book I am using, "File Maker Pro 11, the Missing Manual" , talks about databases.

                Anyway, everything I am doing, for now at least, is confined to one database or file.  It has several tables and this post has to do with copying records from Table1 to TableNew and from Table2 to TableNew.  Tables1 and 2 can not be combined into a single table because they are basically parent/child tables with a one-to-many relationship.  I think this is the correct data structure for these two tables otherwise there would be a tremendous amount of duplication if they were combined.  Each table has a field called "name" and I wish to combine all the names from the two tables so that I can make an index or complete list of all the names in file (database).  The two tables change as I enter more data so every so often I wish to create a new index and I want to automate that process with a script.  I was able to combine the data from the two tables into TableNew by using the Layout selection window, the Find Icon and the Records -> Import feature.  The Import screen seems to know what the source table is but when I try to replicate that process in a script, I do not know how to specify the source table.  That Import screen asks for the source file which is on the Desktop but I can't figure out how to specify the table within that file.

                I hope this explains my problem.


                • 5. Re: Combining two tables into a third table

                  ROFLMAO, you caught us there, Howard ... FMI DOES still use the term Database mostly because they haven't updated their own documentation properly to reflect the change since vs. 7.

                  What do you plan to do with this new table which has the names from the parent and the names from the child combined?  Even if you have a complete list of all the names, how would you know which database to return to to find the details about that specific name?  I suggest that you don't attempt to keep keep an index (third table) updated this way.

                  What version of FM are you using?  There are times an 'index' table is necessary but I don't see that need indicated yet here.

                  As for importing ... add the 'file on your desktop' into your graph by going to File > Manage > External file reference or by going to your graph, select bottom left icon (new table occurrence) and specifying your new file.  Once it is in your graph, create a layout based upon the table within that file.

                  When you import, specify your EXISTING file as the source and specify that table occurrence (you created the layout on) as the table.  If you want only one each of every name from either table then simply set your field 'name' in third table to validation of unique  and specify always.  Now when importing, you just select ADD only from either of your other tables and you will get unique names because if the name exists, it won't be imported but Howard ...

                  ... what if your first table has name Bill Smith and your second table has name Bill Smith?  Which gets back to my question about what will you do with the information anyway since you won't know which table it will come from.  Why do you feel that you need this index?

                  • 6. Re: Combining two tables into a third table

                    I am making a wild guess that the names are something like:  Main table is Patient and related table is family members ... or ...  ?

                    People are people.  And people should reside in the same table and have included a CATEGORY field or other designation as to how they are different.  You can use a self-join to relate them just as easily as another table.  Your need to now create a third table to 'index' the names keeps pointing to my concern that you are starting to denormalize your structure.  Like entities should reside in same table so there is no need to create a third combining them.

                    • 7. Re: Combining two tables into a third table

                      Thanks for your prompt responses.  ROFLMAO ????

                      Some background:  This a genealogy database which I started over 20 years ago before there were PCs.  I wrote the application in HP BASIC and ran it on one of their desktop computers.  It was rudimentary but it worked.  With advent of the PC and MSACCESS I realized that my application was really a relational database so I moved to MSACCESS and have been using it for the last 15 years at least and am pleased with what I have accomplished in that time.  After the PC was around for a time, commerical genealogy programs became available, but by then I was very well satisfied with what I had.  Besides, I think I have features which these other programs don't have and the thought of moving over 10,000 names with their attendant data gives me the willies.

                      More background:  I keep a printed record of each family (husband, wives (if more than 1) children, dates, biography, etc.) and am now up to over 700 pages.  Yes, I can access all of this on my Macbook, but sometmes it is more convenient to look through my notebooks.  And in order to find an individual in the printed material I need a table-of-contents or an "index" as I call it.   I am not using "index" in the database sense.  And I print the "index"  for the same reason.  It now runs to over 25 pages with 2 columns per page.  And why do I do this?  Because I have been doing it for 20 years, and am used to doing it.  Also I have discovered that scanning the index shows up some strange occurances which I wouldn't have noticed otherwise in spite of extensive automatic error checking.  Pattern recognition at work.

                      Back to the issue at hand.  Table1 is a table of all the husbands and Table2 is a table of all the wives.  So there is no chance that Bill Smith will appear in both tables.  The tables are related together by a number (which I generate) for each family.  The index is a list of all husbands and wives, their family number, and page number in my 700 page notebook.  So I can find any one in my notebooks by using the index.  And I have multiple ways of navigating through my computer database to find a desired family.

                      It makes absolutely no sense to me to combine these two tables into one because of the resulting duplication of data and/or doubling or tripling of the number of fields required.  Remember that a husband can have more than one wife (I have one instance of 7 wives). This is analogous to having a customer table and a parts table.  Besides I have used this structure successfully for over 15 years and I am not about to change now.

                      I am close to getting IMPORT to work and will use your suggestions above to, hopefully, make the script work.  I am about 90% there.

                      Thanks again for all your comments,