I have Contact Information and Notes in separate tables; is it possible to import data into separate tables at once?
You can only import into one table at a time. When I have a complex import to make, I'll create a temporary table and then write a series of scripts to put the data into the correct places in my database. For example, if the data to import contains a note field in the contacts table, I'll move it into the temporary table that has contacts and notes, then after the import I'll move the notes to the notes table.
Hope that helps,
This is helpful, but I'm totally new to this stuff. I have an excel document that contains contact information (First Name, Last Name, Addresses, etc.) and a unique note about each contact. I'd like to add this information to FMP such that the contact information is correctly entered into the contact table, and the notes (as tied to each contact) are correctly entered into the notes table.
Do you have a sample script I could use to accomplish this?
Is this import something you'd be doing only once or on a regular basis?
kelpgriffin wrote: I have an excel document that contains contact information (First Name, Last Name, Addresses, etc.) and a unique note about each contact.
I have an excel document that contains contact information (First Name, Last Name, Addresses, etc.) and a unique note about each contact.
That's not quite clear: is it possible for a contact to have multiple notes and if so, how? If not, why would the notes be in a separate table?
You'll need to create primary keys for all the contacts in FM and then populate your notes table with them to link them up as Nancy said is this one time deal or something on going? You can script the whole thing and import as many tables as you want one time with as scripted process but it's not a "beginner" type of thing to set up.
As Michael asks, we need to understand a bit more before suggesting the best approach. However if you have an Excel spreadsheet with a single contact's information (each Contact listed only once in the spreadsheet and a single note as a column) but you anticipate multiple notes in FM in future or you understand that Notes are best in related table even if 1-to-1, and if this is for one-time migration then you can handle it pretty simply this way:
In Excel create a column and serialized it. This will tie the Contact to the Note when you import although you still need two imports. Import each into their tables and also import the serial. This can then BE your auto-enter serial in Contacts and would be the foreign ContactID in Notes . If there are prior contact records then import that temp serial into new field called TempID then relate the tables on TempID and in Notes replace the ContactID with the NEW auto-enter which is automatically generated in the Contacts table.
If this temp Contact serial is going to become the real unique ID then be sure to then establish the auto-enter (serial, increment by 1) on the ContactID and set the 'next value' to your last serial + 1. And always back up first. :-)
First of all, thank you all so much for your help. I typically work in audio, which is (process-wise) very different from database upkeep & cleanup!
The typical process for my (new) organization is this:
1) Host an event at which clients fill out basic contact information for us (First Name, Last Name, Phone, E-mail, etc.)
2) We digitize the information in Excel, and add unique notes about each client in one "notes" column.
3) We import the data into FMP.
This is an on-going process, as my organization is constantly hosting new events. A typical events ranges from 100-10,000 participants, some of whom are already in our database.
Ideally, what we'd like to set up is an automated system (or series of scripts) that
1) Imports new contact data and notes from Excel (which are found in different tables in FMP). Some contacts have multiple notes in FMP, but we will only be importing one note at a time from Excel.
2) Sorts duplicates along with original/pre-existing records so that we can compare information manually before deleting some or all of the duplicate information
Sorry for any confusion, and thank you for your patience!
My goal is simply to take Excel data (As simple as First Name, Last Name, and a Note, each one column) and import it into FMP such that the correct Note matches up to the correct contact in FMP.
My process for accomplishing this is as follows:
1) Import Excel data (First Name, Last Name) into Contact Management Table/Fields
2) Import Excel data (Note) into Notes Table/field
3) Relate Contact Management & Notes fields, matching on "E-mail," or by creating a unique ID number for each contact.
And then in order to deal with duplicates...
4) Find, sort, and visually/manually compare duplicates
5) Manually merge duplicate data from Contact Management and Notes fields
6) Manually delete unwanted/duplicate data
Why not just capture this data on ipads using FM GO? Why put the data into excel in the first place? It just seems likes it's an extra step you don't need. If you can type data into Excel you can type it into FileMaker it would seem. Only import data from third party sources when you have no other choice, otherwise just place the information directly into the database. If you have no other choice then you must create key fields in your excel file to match up the contacts to the notes and then import these into a temp key field join the tables on them so you can then grab your internal keys to join the records properly after import. Not that hard to do, but seems a bit unecessary if you can just skip Excel all together. Good luck.
I agree with Lemmtech, the process the way it is currently configured is too complicated. Are you getting this information at the events? or from some sort of survey/registration software program? Are you attendees filling out slips of paper? Filemaker Go is one solution. Web publishing your database is another. The best solution is to have the attendees fill out the form directly into filemaker.
The challenge is identifying unique contacts, and matching them if they appear multiple times.
A good option is to match on cell phone number or e-mail address.
Thank you for the advice--we will move toward a FM GO system at events. This will hopefully be our method from here on out, but we still have to import our current 15,000+ contacts found in excel databases. I will do my best to use temp key field to join tables. Thank you again for your help and advice!
We will do our best to move toward using FM GO at events. Thank you for your helpful feedback!
Sounds good. BTW I've done ALOT of this sort of the thing over the years if you want my help to clean up the backlog of data please contact me here: http://www.lemmtech.com/about-us/contact-information
Retrieving data ...