How to capture the date (or date/time) a summary field reaches a certain number

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
I am trying to capture the date that an agent reaches their commission cap so that commission formulas change after that date. I have two tables: Agents and Transactions - Agents is the parent table and Transactions is the child table. I created a summary field in Agents that totals the agent's commission across the related Transactions records [Total Commission to Broker]. When the summary field hits a certain number then I want the date that it hit the number captured so that my formulas in the Transactions table will adjust to stop collecting commission for the broker after that date. I can't use a form rule because the summary field is in the Parent table but the person entering data will be doing so in the Transaction record. I also tried automations but it won't let me save the automation when I select the same table or a parent table. Any help would be really appreciated!
Photo of AJ

AJ

  • 112 Points 100 badge 2x thumb

Posted 4 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,522 Points 50k badge 2x thumb
I have an alternate suggestion.  Lookup that summary total down to the detail records and call it like [Total sales (lookup)], and then make a new field to be a snapshot called [Total sales at time of sale].

Then your formula can simply key off of what the total sales were at the time of the sale (counting that sale).

Here is some help in setting up snapshot fields.

https://help.quickbase.com/user-assistance/setting_up_snapshot_fields.html
Photo of AJ

AJ

  • 112 Points 100 badge 2x thumb
Thanks very much! That works perfectly. Have a Happy New Year!