Here's my situation: I have a Staff Table, an Expense Table and a Task table. Staff has a 1 to many relation with both the Task & Expense table, and I've linked both tables to the Staff table through the UUID of the staff record.
For reference, this is a basic Relationship layout
Staff --< Expense Through Staff::uuid & Expense::STF_uuid
Staff2 --< Task Through Staff::uuid & Task::STF_uuid , Where Staff2 is a table occurrence of Staff
When I create a task or expense record, I have their respective STF_uuid fields auto enter to calculate an SQL query where it pulls the UUID of the Staff member that is currently logged in ( SELECT uuid FROM Staff WHERE Staff::Username = Get(AccountName) ). This works fine in the Staff2/Task relationship. The correct name shows up upon record creation, and I can change it at any time through a value list of staff members. Great.
The problem arises in the Staff/Expense relationship.
1) I like to display the Staff::Name_Full field on the Expense record (employees don't know their uuid #, they know each other's staff names). When I create a record, this field is blank, so at first I assumed Expense::STF_uuid was empty. It was not, it had calculated correctly. I had to engage the STF_uuid field manually (click in and out of it) for the Name_Full field to populate. I thought that was weird.
2) Some users need to be able to change the staff member on this record. Should be simple enough. It's an auto enter field, so it should allow data entry. Well, if I had the the STF_uuid field to replace the existing value, this value would reset to the current user every time I made any changes on the Expense record. This made no difference what field I was modifying, even if it was the STF_uuid field itself. I would delete a digit from the STF_uuid field, and it would repopulate. So it was basically acting like a regular calculation field. Not what I wanted.
So I've been messing with this all morning trying to figure out what is causing both of these issues, and I want to confirm that the solution I've found is correct. As a fix, I created a second Staff table occurrence (Staff3) and I replaced that with the Staff Table, and everything works as I wanted it to. Now the relationship looks like this...
Is there something I inherently missed in my first relationship graph? Why wouldn't the Staff::Name_Full field update at first if the Staff2 & Staff3::Name_Full field's work just fine? Why did the STF_uuid field not allow me to change the value if it was an auto enter calculation? I wasn't changing anything with respect to the calculation ( I wasn't changing Staff::Username or Staff::uuid) when I changed the Expense::STF_uuid field, so why was it forcing a recalculation?