I'm new to FM and have been working to deploy a data solution for our small non-profit organization. I've been trying to create a solution that "protects" our data from user errors and ensures that the data that gets displayed to the end user is always the most current. I set up three tables: Master Table, Sync Table, and User Table. My idea was to import monthly official record changes from our organization's central office into the Master Table each month, showing a modified date. In the report, some records will have minor changes, some remain unchanged, some are deleted, and some are added. I need for the data in this table to always match the official data from our central office, so it's read only and not accessible to any users except me.
For the User Table, the data starts with original data from the Master Table and then users can access and edit the data as needed. So far so good on both tables. My dilemma is how do I compare the records in the two tables, keep the most current information and/or generate a report of changes that can be sent back to the central office? This would happen monthly at the same time I update the Master Table with data from the central office. This way, official records stay official, field data is updated daily, and those changes are exported to personnel at the central office so they can update the official records monthly.
My idea was to use a third table (Sync Table) that is populated with data from a script that compares data from the two others and keeps the most current (all records have auto entered creation and modification date fields). When I tried to create a script to do this, I couldn't find any functions that will compare entire records across different tables so that I can populate the Sync Table with the most current data from both of the other tables. I've already created the same fields in each table and relationships between all 3 tables for those fields. Am I missing something? Is there a better way to accomplish my goal?
P.S. I'm using FM13 Adv Pro and deploying the final solution via FM13 Server via WebDirect