propagation/replication via relationship?

[dustmop]

I have multiple tables in use, each in their own file. There is ONE instance where data needs to overlap between tables, and there is no way around it.

In table AAAA, I have a field named “AAAA_Incident Number”

In table DDDD, I have a field named “DDDD_Incident Number”

When a user creates a record in table AAAA, and enters something into field “AAAA_Incident Number”, I want that ENTIRE record to automatically propagate into table DDDD. Every field has an identically-named counterpart in the other table (but DDDD has a lot of other fields).

A handful of users only have access to the file holding table AAAA, and certain record entries (those that have an Incident Number) need to propagate to another file/table (DDDD) for the management-level employees. The management-level table (DDDD) has MANY more entries than AAAA does.

Having the records replicate into the second table may seem rather clunky, but these are existing files, with well established data. I have already imported the relevant records into DDDD, but would like for all new records created matching the above criteria to automatically replicate/propagate.

I am using FM9, but using multiple tables in a single file is currently not an option, as state regulatory requirements mandate these databases be independent files with rather rigorous backup requirements. Data integrity and backups are taken EXTREMELY seriously in my industry.

A script is a possibility (import new records from AAAA meeting specified criteria on open of DDDD), but if there is any way to automate this in the background, that would be awesome. I spoke with the state legal consul, and it is within regulations to have one file script to write to another file in this instance (Having AAAA automatically update DDDD via script). It is merely the hosting of all data in one file that is a no-no.

(My apologies if this is answered elsewhere (or in the wrong forum), I apparently don't know what's going on anymore!!)

[UpNorth]

I'm not a 100% on what type of data and how it's related, but it sounds like the lookup function might be an option for you?

You would want to create a relationship with a field that will be the same in both records and then setup your field in DDDD to lookup the info from AAAA table?? Just a thought...