AnsweredAssumed Answered

Conditional Updating of Related field

Question asked by fmtuner on Dec 4, 2015

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.

Outcomes