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.

The beauty of a relational database is not having to duplicate data in different tables. It sounds as though you are running two versions of the same data when you just as easily create a calculated field in one table to set your employee as "Active" or "Inactive."

Perhaps you can give us a better understanding of what you are trying to accomplish in the long run as well as the short run.

I would, also, suggest a little reading material before you get to far in to your design and then things start to fall apart because of structure issues. It is better to invest a few hours now then 100's of hours later trying to fix or redesign your database due to structural problems.

http://www.filemaker.com/help/MacTOC8.html

http://www.foundationdbs.com/ Read the White Papers for FM novices.

hth

Basically I am synchronizing a FMP table wiht active directory. here is the problem, when an employee is terminated their name and record is deleted from AD. However in my FMP database I do not want to lose the history of the employee (and software projects).

If an employee changes departments that is reflected in AD, I want to update the FMP table without having to redo or reimport into the FMP table.

If a new employee joins the company the record will be added to the AD table adn I need to pull it into the FMP table.

Any ideas, the key is when a record is deleted from AD, I have no control over this.

I am pretty familiar with Relational DB having done about 15 years of SQL Server, VB, and .NET. FMP is new to me but pretty powerful from what I am seeing, this is a learning project for me to see if my group should be doing more with FMP

I would use an import with the update matching records option (add remaining records). If you have a field that captures the modification date, with the option for auto-enters on it will update the records and have a new date.

Then AFTER the import, if there are any records that do not have the modification with the current date, I would find those records and set then inactive flag.

Then AFTER the import, if there are any records that do not have the modification with the current date, I would find those records and set then inactive flag.

Or you can also script Show Omitted Only after the import instead of finding those without current modification date. Records not in the found set after the import update will be records which should be set with inactive flag, if I understand correctly.

LaRetta :wink2:

Edited November 17, 200817 yr by Guest

thanks a lot guys, this is helpful. I really appreciate your adivce, this is definitely helpful and I appreciate the fast responses.

Ok the hints worked for the updated and new records. I am pushing the envelope to try to automate everything.

I added a global field to the table as MAX(datemodified) my goal is to do a calculation as follows:

Case ( DateCreated = DateModified; "New"; DateCreated < DateModified; "Updated"; DateModified < Max ( DateModified ); "Deleted" )

Problem is that max and min are both showing the same values. Any ideas?

Edited November 18, 200817 yr by Guest