I'm having a bit of a problem getting a field to update in my database the way it needs to.
The relevant items are the Table "Clients" (parent) and table "Case" (child) and these fields:
- Clients::Expenses (number field)
- Clients::Expenses Due (stored calc, FQL calc that subtracts total of Case::Expenses, in all Cases where Case::Date Paid has a date, from Clients::Expenses)
- Case::Expenses (number field, currently auto-enters Clients::Expenses Due)
- Case::Date Paid (date field)
The logic behind this is, Client A has 3 Cases, Case 1, Case 2, and Case 3. Clients::Expenses can be updated over time, as each Case is pursued. That number needs to be transferred to all Case records (in Case::Expenses) as they are pursued (via auto-enter), and when a client gets notification of each potential Case settlement, Case::Expenses is deducted from the total. The amount in Clients::Expenses can change over time, which updates Clients::Expenses Due, which needs to be entered and updated in all Case::Expenses fields as that happens; this is because not all Cases settle, but the expenses need to be covered. However, once a case settles and is paid, the Case::Expenses field can no longer change, and Clients::Expenses Due will reduce accordingly (and therefore all other Case::Expenses fields will as well).
If Case::Date Paid is empty, Case::Expenses should update any time Clients::Expenses Due updates. (this means that if a Case::Date Paid field is entered into another Case Record, Clients::Expenses Due will update and therefore update Case::Expenses)
If Case::Date Paid is entered, the amount in Clients::Expenses Due should then remain at the amount it was when the date was entered.
So I can't get the Case::Expenses field to conditionally update properly. I've tried the following:
- Auto-Enter: will not properly update due to referencing of a related field
- Auto-Enter: added Expenses Due to the Case table, but it still doesn't update properly due to unstored Calc
- Calculation: will update, but can't figure out how to get it to stop updating when Case::Date Paid is entered. Can't get it to be a stored calc due to related field.
- OnObjectValidate Trigger Script: I can only set the trigger to Clients::Expenses, since Expenses Due is a calc field. This prevents updates when a Case::Paid Date is entered.
Hope this is clear. I'm pretty sure I'm missing something either structurally or logically, so any help is appreciated. I can provide a sample file if needed.