I would imagine you just want to import each file to a temporary holding table, and process it according to find/match rules you specify. You can establish a relationship from your temp table to the permanent table via a foreign key in the temp table that related to the primary ID of the permanent table, allowing you to write/update values across. Make sure to check the box to allow records to be created in the permanent table from the temp table to handle new records.
Set Error Capture [ on ]
Set Variable [ $staff ; get(accountname) ]
Go To Layout [ tempContacts ]
Show All Records
Delete All Record [ no dialog ]
Import Records [ linkedin.csv ]
Go To Record/Request/Page [ first ]
Set Variable [ $email ; tempContacts::email ]
Enter Find Mode [ no pause ]
Go To Layout [ CompanyContacts ]
Set Field [ CompanyContacts::email ; "==\"" & $email & "\"" ]
If [ get(foundcount) > 0 ]
If [ patterncount( CompanyContacts::owner ; $staff ) > 0 ]
Set Variable [ $condition ; 1 ]
Set Variable [ $condition ; 2 ]
Set Variable [ $condition ; 3 ]
Go To Layout [ tempContacts ]
If [ $condition = 1 ]
//Record is in the system and matches the $staff trying to import. Update if needed.
Else If [ $condition = 2 ]
//Record is in the system but does not match $staff trying to import. Update and add $staff to owner list.
Else If [ $condition = 3 ]
//Record does NOT exist, write the new record into permanent contacts.
Go To Record/Request/Page [ Next ]
Exit Loop If [ get(recordnumber) = get(foundcount) ]
Go To Layout [ home ]
Show Custom Dialog [ "Import Complete" ]
thanks for thr great effort!
i will try to make it as you say. give me a few days.. little busy week.
then i will report back.
Or just do an import
Match the fields that make a record unique
Check :update existing records (This will give you only record for a contact even though it occurs 10 time in your import data. with the last occurrence being the data kept.)
Check: Add remaining data as new records.
The disadvantage of this method is it will over-write any data in matching field that you have changed but, it will update any changes in their LinkedIn data.
Of course you can use a temp table or file and import all new lists to it and then import your existing data to it keep your current data intact and then import your temp data to you main table.
thanks GreatGrey for this.
first: all LI info is a in separate table apart from our own added data.
this is done on purpose so that you can import "straight" without having
to worry about other data.
in case a contact exists, the data is always overwritten (no need to check
if it had changed).
therefore the first option seems to be the way to go: if 3 people know the
same contact, then still have 1 occurence.
will read the manual about that option to be sure it works like how I
understand it to. thanks again!
One more thing. Any existing records that don't have a match will be omitted from the updated/new records so you will need to do a show all records step.
However it does give you a chance to look the imported data by its self and the records that weren't updated by themselves.
I see... that is important indeed. need to study that and see how to
Script something like this. The import will only make a match to the current found set. if any are omitted they won't be updated and a duplicate record will be made.
An easy way to eliminate duplicate records from your master file is to export all the records to merge file, delete all records and then import the merge file.
ok it's all on my to-do list ☺
@ mike beargie / great grey
i have been studying both your advice. i am sure that both your ways will work, but i have chosen to do it
mike's way. the reason is that we have about 40% double/triple contacts with our staff.
filtering these out before writing to the permanent contacts table seems a cleaner way to me then
adding them all and removing duplicates after.
however, i am still working on it so i might bother you again for advice
My comment about removing duplicates in the master table, would handy no matter how you choose to add new contacts. Because if somebody added a contact list the wrong way you could have a lot of duplicates to remove.
The only way you get duplicates with standard FileMaker update matching records import option is by not having all records showing. which if Scripted should not be a problem.
i agree with your last statement, but that solution unfortunately doesn't
work (would have been by far the simplest way). it works for new records
(unknown in master table) and it works for known records of staff X. but
where it goes wrong is that it would add as a new contact an already known
contact but from another staff (because the key email+staffID does not
forgot to say.. you can't know but the importing is scripted so nobody can
do anything wrong. (assume import for staff X)
- select all staff X records
- delete them all
- import all the records from the text file
if the staffID is not the same as it finds in the text file, the script
And that why we get the gray hair trying do something simple, that has that oh you need to do this too.
@ mike beargie: everything works and thanks for the help again!