synchronizing tables automatically in FMP

fungafatm
fungafatm used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.

Commented:
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.

Author

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
Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial