If the source file is open, import records will import from the other file's found set. Thus you can find just the records that are new in the source file and then open the target file and import them.
When you select Import records from the file menu, filemaker assumes that you are importing into the table of your current layout. To import into a different table, select a layout that specifies this table in the "show records from" drop down in Layout Setup... first, then select Import Records. You'll now see the table of your current layout as the target table for your import.
My Two Cents: Would there be a way to make FM import the entire DB at once if all tables matched up?
Also, would it recognize duplicates if this is possible?
Yes, if you script it. You can also script updating serial number fields, which is something I should have mentioned here.
If you have any kind of serial number serving as a primary key, you may have a real headache here as the different files will have different records with the same serial numbers and this can result in related records from both records in the separate files all showing up as related to both records in the merged file--resulting in chaos.
Before importing, you may need to carefully update serial numbers in one or more files to ensure that they are unique. You might update all serial numbers in both tables of both files to append a ".1" to the end of the serial number in one file and ".2" to another and so forth.
Thanks for the replies!
I have made some progress: I have successfully matched tables, which allowed me to import all the fields I want from the first table.
Allow me to explain the database in a little detail:
I have a list of patients, with birthdates, referring physicians, etc. in one table.
I have a list of MRIs each patient has had, with the referring physician for that MRI, the date of study, and details of the findings of that MRI in another table.
I have an 'Accounting Table', which includes each MRI, the site it was performed, the billing and collection data.
I had one db originally containing patients from site 'A'. I gave a copy of this to two different people. Each then entered patient and other data from site 'B' or site 'C'.
So-I successfully imported from 'MRI' table to 'MRI' table, importing patient data from site 'B' to site 'A' (the original database), then imported 'Patient' table to 'Patient' table. Good so far.
Now I tried to add the site 'C' data: I imported 'MRI' table to 'MRI' table, which worked fine, but when I next imported 'Patient' table to 'Patient' table, some data in patient records was overwritten (for example, the wrong MRI site, or wrong referring physician).
I am using an 'MRI ID' to link the tables together.
This may sound confusing, but as I said, I am a noob, and may not have the vocabulary in tow yet.
The problems you are experiencing are in part due to what I warned about in my last post. Assuming MRI ID is a serial number field, you have records from different sites with the same ID numbers. Until you untangle that, you'll have problems.
If the data was truly overwritten, instead of just getting two sets of data with the same ID (which on some layouts may look like the data was "overwritten"), that indicates the wrong options were selected during import.
You need to:
- Resolve the duplicate ID issue in the separate files. Replace field contents using a calculation to append a decimal is one way to do this.
- Import with the "Add New" import action so that no data is overwritten as these represent new and different records as I understand things.
When working with Import Records, it's a good idea to save a backup copy of your file just before you do the import so you can revert back if it doesn't work the way you wanted it to.
Thanks for the quick reply!
The MRI_ID field is NOT a serial number, but a number, indexed.
The Patient_ID field IS a serial number. I had forgotten this, and thought I was using the MRI_ID as a serial number.
So...I can try to append the serial numbers in one of the files before I import. But I have a few questions about this process:
1. Will this change the relationships between tables in the database I am appending serial numbers?
2. How do I append serial numbers (perhaps a dumb question-I googled it and it seems very complex)?
3. How do I revert the serial numbers to a common format again after importing?
You'll have to change the numbers for both parent and child tables in exactly the same way so that related records stay related.
- Make a back up copy of your file.
- Pull up all Patient records on a layout by using Show All Records
- Put the cursor in the PatientID field
- Select Replace Field Contents from the Records menu and click the "Replace with calculated result" option
- Use an expression like this in the calculation: Patients::PatientID + 0.1
- Do the replace
Now repeat with the matching field from each table of related records so that they all get a "0.1" put on the end as well.
If this will be an ongoing process, you can modify your next serial value setting so that a different letter or decimal such as the above 0.1 is automatically part of the serial number on each computer. Then you won't have to do this again.
I figured out how to append through 'replace field contents'.
I added 'new' records.
But I got a whole bunch of 0/0/00 dates, and nonsense other data.
I believe there is a box right after 'Import' that asks about auto-entry. I left that checked. Is that the problem?
Sorry, I posted right after you. I will follow your instructions, and update with results!
Quick question: I presume I only do this with the source data?
Yes only the source data. Don't use auto-entry for your import as it will mess up your serial numbers among other things.
I cannot gain access to 'Patient_ID' in my 'MRI' view. I thought I was learning how to do this, but now am more confused.
I am enclosing a screen shot of my relationships. Perhaps this will clear up my poor explanation.
No-I can't even figure out how to add a screenshot on this forum....
I feel like a complete loss
You can always add the field to a layout by entering layout mode and using the field tool at the top of the screen. Just make sure the right table is referred to in "show records from" of layout setup... for the layout in question.
Sorry, still confused. When I use my 'Patient' view, I can click in the 'Patient_ID' field, then do the calculation you described.
But when I try to enter the same field in the 'MRI' view, I cannot select the field. The 'Patient ID' field is already in the 'MRI' view, but I cannot enter it. I don't think I made that clear enough.
Also, how can I post a screenshot here?