I'm new to FM and trying create a script that will import data we receive from our parent organization each month. They keep a master membership list with contact info and send us (regional division) an updated list each month that contains all the current member data, while adding new members and dropping those who have resigned.
The initial import is simple enough and setting up a recurring import is simple. However, I don't want to replace all of the data after the initial import. I need to match existing records and update those, while at the same time adding new records that don't yet exist in our database and remove records that have been removed in the .csv source file each month.
The member number is set by the organization and is the ID for each record.
Is there a way to do this in FM? I've tried importing two months as separate tables and then using a layout based on table 1 with a portal from table 2 to do a find or sort that identifies records that don't exist in one of the two tables. Can I script this somehow and automate the process so that I can import the monthly update and then run a script that does the adding, deleting, and updating of the records in a master table?
I'd appreciate any suggestions!