0 Replies Latest reply on Dec 4, 2015 9:46 AM by fmtuner

    Conditional Updating of Related field

    fmtuner

      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.