Forum Discussion

BenPonder's avatar
BenPonder
Qrew Trainee
6 years ago

is there a way to control when an app snapshots a field?

I've got an app which is tracking penalty accruals and cutting off further accruals once a max amount has been met.  For this I need the summary of the net penalty amounts before adding the new penalty; since it is taking a summary of the net penalty field, it is including the one that I am currently using in the calculation.

net penalty amount (formula - currency):

    If([Gross Penalty Amount]+[PG CYTD]>[annual max penalty],
    [annual max penalty]-[PG CYTD], 
    [Gross Penalty Amount])

[PG CYTD] is a lookup of the summary field for the total of "net penalty amount" for that particular service level agreement.

in the test case below I've got the annual max set as $10,000; net penalty amount should be $10,000 for the first line and $0 for the second line since the max amount was met.



if I cannot take the snapshot before calculating the current penalty, any thoughts on a workaround?  Thanks everyone for taking a look at this.



App builders
Tables and fields
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    I have not thought this all the way through, but when the record is in Add mode it can have a look up of the current total penalty, which obviously would not include the amount on the as yet unsaved record.

    You can have a form rule to capture that value in a data entry type field whoever that data entry type field is blank. Ie as soon as the form loads, you grab the total penalty and it will be before the record is included.
  • BenPonder's avatar
    BenPonder
    Qrew Trainee
    6 years ago
    part of the issue though is that these are all being done through automation; I'm importing a spreadsheet with around 400 different metrics to the "results" table, the system is determining for which of them we have missed the target and then generating records in the "penalties" table for each of those, and part of the penalties table records is this formula.  

    on the summary field it allows for some filtering, I'm going to try and grab the time stamp from the results table and see if I can filter before "now"... maybe that will do the trick?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    An Automation can run a saved table to table import.  Perhaps you can import the detail records into themselves merging on Record ID# to record the total penalty summary just before you import new records?  I don't really understand your tables and relationships, but a saved table to table import can do what the form rule would have.