synchronizing tables automatically in FMP

Need help syncrhonizing data between 2 tables automatically. Table A and B have the same data both have the same PK, I want to automatically do the following

1) If PK in A but not in B, automatically add record to B

2) If PK not in A but in B, set the status flag of record in B to inactive

3) If PK exists in both A and B, check a date modified field in A and B and update record in B if appropriate.

A and B hold employeeid, name, department and date fields.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

When you say "automatically"...what do you mean exactly?
Does automatically mean every time a single field value changes?
New record created?
After import?
On a schedule?
Click a button to launch a script?
How/why are these tables diverging? Are you importing regularly, or is one of the files already hooked to several different tables?

I want to know what triggers the sync - or does a script have to run continually or periodically.
I would check the presence of a related record in the other table using a relationship in between A and B (PK to PK).
if the link is true, (tableA::PK = tableB::PK) viewed from B, > set tableB::status = inactive
then find the records in A of which link A to B is not true, then import them from A to B (so from within B)
find ignored records in A and reduce the records set with those where the date is more recent, then do a replace of the necessary fields.
fungafatmAuthor Commented:
problems is that I have an app in FMP that I am importing data from active directory.  When users are deleted they are removed from AD but when the list is imported into fmp I do not want to loose the record.  the fmp application stored employees, software, and software licences assigned to employees.

I do not mind importing on a weekly basis from AD but do not want to have to manually change status of deleted employees since I have no idea when they are deleted.

employee 1-----n assignement n------1 software
this can be done the same way.
which table gets data from AD? A or B
say it's A
from B records having no link to A are obviously deleted/inactive, so after each import from AD, simply run a "replace" function in B to update the status

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.