synchronizing tables automatically in FMP

Posted on 2008-11-17
Medium Priority
Last Modified: 2012-05-05
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.
Question by:fungafatm
  • 2
LVL 10

Expert Comment

ID: 22988198
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.
LVL 28

Expert Comment

ID: 22992866
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 Comment

ID: 22992958
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
LVL 28

Accepted Solution

lesouef earned 1000 total points
ID: 22993005
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Integration Management Part 2
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question