fungafatm
asked on
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.
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.
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.