synchronizing tables automatically in FMP

Posted on 2008-11-17
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
    LVL 10

    Expert Comment

    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

    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

    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

    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

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
    Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now