Title
Looking for selective import script
Post
Hello,
I need to selectively import a sizable number of names and addresses from an Excel spreadsheet into our company contact database, which is in Filemaker.
Some names and addresses already exist in the database, others do not.
The proposed methodology is to convert/import the Excel document into Filemaker, and then do the import using a script. Since my scripting expertise in Filemaker is somewhat limited, I'm hoping someone has or could develop a script that would do the following:
Given a record in the source database (the converted Excel spreadsheet),
1) copy the "LastName, FirstName"
2) go to the target database -- the company contacts
3) perform a find using the copied data ("LastName, FirstName") on an analogous calculated field
4) If there is a match, show result "Contact already exists", while displaying found record(s) to allow visual verification of address details
5) If there is no match, show result "No contact found. Import record" and import matching fields from source database to target database, again, displaying imported record for visual verification by operator.
This will be an ongoing process that will need to be repeated from time to time as new contacts accumulate in Excel.
I would be grateful for a response. Also, if there's a different way to accomplish this, I'd appreciate any suggestions.
Vlad Morosan
www.musicarussica.com
The World-wide source for Russian Choral Music
You should not have to convert the excel file to FileMaker, you can import directly from and excel file into a table in your file.
Instead of perfoming a find one record at a time, you can define a relationship between your two tables:
contacts::FirstName = ImportTable::FirstName AND
contacts::LastName = ImportTable::LastName
You can then add as many fields as you need to see and work with from Contacts to your ImportTable layout. If there is a match between the record in ImportTable and a record in ImportTable, these fields will show the contact data from the matching contact record. If you enter Find Mode and enter an * in the contacts::LastName field on this layout, you'll be able to pull up a found set of all records where the first and last names match so that you can edit and evaluate them.
Once you have finished that part of the process, you can use Import Records to Import all or just some of the Record in you ImportTable into contacts. THere are a lot of options to choose from when importing records, so read up on the process in FileMaker help and make frequent back up copies so that you can start over if you don't get the results you expected.
Note, there are also more sophisticated relationships and/or a filtered portal that can be set up to search for matching contacts where the names aren't quite identical such as two forms of the same first name (Phil, Phillip, Philip, for example).