(GetField) get field value from (undirectly) related table
I would like to pull/get/read some values from a related table and store it into a local text field. Important of me is:
1) I don't want to use a (unstored) calculation / formula field because I want to "store" the value ... e.g. in order to let my audit log recognize changes made.
2) the two tables are not directly related through one shared link but indirectly through a third table. I'll describe why in the example below.
Now here is my problem:
I can get a field value (let's say "seatNumber") from a direct relation, e.g. via the GetField() function. So my "Sold tickets" table can get the value from the "customer theater seat" table. But the function doesn't seem to work once I have that third table as a linker or hub. Meaning, I can't get the "seatNumber" stored in the "customer" record.
Any idea how to solve that?
Explanation of the example provided:
Assume I want to sell tickets for some seats (movies, theater, whatever). Everytime I sell a ticket, I'll create a record in the ticket table storing the customer id and the seat id - and I will make sure that each of those two values are unique in the "ticket" table, so I don't get some overbooking. Once I delete some customer (e.g. decided to do something else ..) or remove a seat (..found out it's in a bad condition) , the ticket will be deleted automatically via the links. Or if I just delete the ticket, in all cases each entity (customer, seat) will be released automatically without complex scripting of record updates.
Now I want to audit log my customer records. But formula fields / unstored calculations are obviously not recognized. So I need to store my seat number directly in the customer record via some calculation ...
.. and here starts my problem described above.
PS: I have an example database that I can provide. Is it possible to upload such files here in the forum?