Thanks, Richard. I know you spent a lot of time outlining your solution, as I understood it almost on the first read. And thanks to John and Corn for all the great suggestions. And Richard and you are right, I painted myself in a corner with my sloppy design. I have a couple of ideas thanks to each of you for better structuring the data. Now I have to struggle with when to make the changes.
In the meantime I stumbled across the cheap way of solving my immediate problem. Indeed, the IMPORT script step will allow me to copy record fields from one table to the other. What I kept overlooking is that one of the files you can specify for the import file is the Filemaker file you are working on. Once you specify that, the window lets you pick the table to use as the source. It indeed was obvious once you realize you need to think of the database you are working with as an "outside" file too. Regards, Gary From: FileMaker Pro Discussions [mailto:[email protected]] On Behalf Of Richard S. Russell Sent: Thursday, January 01, 2009 3:29 PM To: [email protected] Subject: Re: Copying information from one table to another On 2009 Jan 1, at 12:10, Gary Hunt wrote: This application keeps track of psychiatric test results for children. There are currently 20 some tests implemented with more possible later. Early on I thought about collecting common information in a single table and placing the unique information in their own tables for each test with relations to the common table. However, my problem was that I didn't know yet what all the tests where and what would be common. Even if I knew what was common between all tests, the same question would come up again between different test class-types (mental, hearing, vision, etc.). Hence the decision to treat each test as separate tables and solve the common information problem(s) separately as the understanding of commonality-groupings became apparent. Does this help? Geez, Gary, it sure looks like you've painted yourself into a corner here. I agree with Corn and Jonathan Fletcher that your best long-term solution would be a database redesign. However, that doesn't help you with your immediate problem. So, at the risk of starting you down a track where you'll be spending time on this "union search" technique rather than normalizing your data, here's a kludge you could try: This assumes that each record in each table has a numeric identifying code. If they don't currently have them, you can create them and fill them with values using the "Replace" command. It doesn't matter if a given record has its ID number duplicated in a different table, just so it's unique within its OWN table. Let's assume these codes follow the naming pattern "T1ID" for Table 1, "T2ID" for Table 2, etc. Next create a brand-new table from which you will run all your queries. This table, too, will have unique ID numbers assigned to each record, and we'll call that field "T0ID". The trick here is to make sure that EVERY ID value that's represented in ANY of your 20 tables has a counterpart in the search table. Next, from your search table, create 20 relationships to the other tables: T0ID to T1ID, T0ID to T2ID, etc. Then, for EACH field you want to search on, create a counterpart in the search table that will comprise a concatenation of the corresponding fields from the other tables. For example, assume that you're interested in querying the field "Vision" to see who all is reported as "20/20". The field you'd create within your search table would be a calculation (text result) that looks like this: T0Vision = T1::TableName & " | " & T1::TesteeName & " | " & T1::Vision & "[paragraph symbol]" & T2::TableName & " | " & T2::TesteeName & " | " & T2::Vision & "[paragraph symbol]" & T3::TableName & " | " & T3::TesteeName & " | " & T3::Vision & "[paragraph symbol]" & ... T20::TableName & " | " & T20::TesteeName & " | " & T20::Vision You'd do your Find within "T0Vision", looking for "20/20". It would take quite awhile, because "T0Vision" can't be indexed, due to its use of related fields. And your results would be kind of jumbly, depending on how much overlap of ID values you had in the related tables. But you'd get a list of hits, which would be better than nothing. Sorry I can't come up with anything slicker. Good luck!
