This has been stumping me for a long time, and I am sure I am overlooking something incredibly simple.  Hopefully I can explain this well enough.

 

I have a relationship between several tables (see example ).

 

Whenever a record is created in any of the three related tables (Table1, Table2, Table3), I want a new ExtraData record to be created with the same key as the record in the related table. I also want the userValue to be set to the value of a global variable I have $$curUserValue, only if the ExtraData::userValue is empty. If the ExtraData record exists, the ExtraData::userValue changes, AND the key changes, the ExtraData::userValue should just be updated.

 

Right now I have an onRecordCommit script on the three tables' layouts that creates a new ExtraData record with the current record's key, if such a record does not already exist. It also sets ExtraData::userValue = $$curUserValue. This works fine, but if the related table record's key changes, then a new ExtraData record is created with the new key and $$curUserValue. The problem here is if the ExtraData::userValue field has changed, along with the key, it gets overwritten with the original $$curUserValue.* I want to keep it the same as what it was before the key change.

 

There must be a much easier way to accomplish this, but the solutions escapes me. Any ideas?

 

 

*I am NOT concerned with the orphan records this creates, as I have a script to clean those up.

I suppose when you visit a record you could use a triggered script to store the existing values in global variables; then you'd refer to those values when changing the data and branch your script logic accordingly.

Thanks, I'll give that a try.

I implemented your solution, Fitch, and it works well for most tables, but I came across another problem.

 

I have a few tables that are not directly connected to the ExtraData table -- they are connected to it via another relationship. (See attachment ). ExtraData's key, IntermediateTable's key, and the Tables' key are the same. Note that both ExtraData and IntermediateTable's key field are set to "Auto-enter serial, Unique, Allow Overrride."

 

On the tables that have this arrangement, I get an error that says: "key defined to require a value, but it is not available on this layout. Use another layout to assign a value to this field" when creating a new record (which then attempts to create a new ExtraData record).

 

I think that because the keys on each table have validators, the error is being thrown when IntermediateTable's key is trying to override ExtraData's key. Is this correct? Is there a way to fix this?

I removed the "Unique" validation on IntermediateTable, and everything seems to work now, but I need to keep the "unique" validation for other reasons.