Set up your join as an equijoin (equals) between the imported data and the existing data based on the unique ID. Place the unique ID field from the existing data's table on the layout for the imported data. Do a Find for the unique ID using a "*" (asterisk) character on the unique ID in the existing data field on the imported data's layout, but use the Omit switch (instead of Include).
This will cause FileMaker to locate all records in the imported dataset that do not have a matching record in the existing data.
Let me know if this doesn't make sense.
I think they say 'easy when you know how'? Thank you so much I'd have probably spent days thinking about that!
Can I be a real pain and ask a further question?
Now I have my list of new data not in the existing set is there any way of de-duping this so I have a single row for each employee that I can loop through and add to my employee table via script?
I've created a layout using a sub summary that sorts on the Employee ID and shows the new ones but even though I've deleted the 'body' part I still have hundreds of records for each new Employee ID, so what I'm wanting to do is cycle through summary lines not the body records lines? Does this make sense?
Thanks again, very helpful!
you can use the Fast Summary technique to loop group-wise. In a found set sorted by yourSubSummarySortField,
Set Variable [ $sizeOfGroup ; GetSummary ( sCountOfANonEmptyField ; yourSubSummarySortField ) ]
returns the record count of the current group, so that
Go to Record [ Get ( RecordNumber ) + $sizeOfGroup ]
would bring you to the first record of the next group. Effectively, you're jumping from one record that represents a sub-summary line to the next.
Alternatively, if you know that the found set consist of “new” employees only, then this …
Go to Record/Request [ first ]
Set Variable [ $sizeOfGroup ; … ]
Omit Multiple Records [ theCount - 1 ] # omit all but one of current group
Exit Loop If [ Get ( RecordNumber ) = Get ( FoundCount ) // last record ]
Go to Record/Request [ next ] # start of next group
… will leave you with a found set of one record per group (i.e. employee) that you could simply import into the Employees table.
I haven't had chance to implement this yet but I just wanted to say a big thank you. I'll let you know how I get on with this.
Excellent thank you works a treat!!