I need help with automatic updates to a date field based off of certain conditions.

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Working on an Account page..  I have a date field titled 'Last Invoice Date'.  That field captures the date that we last invoiced someone.  I'd like the field to automatically update to the current date or the first day of the current month if 'Account Status'=Current.  

For Accounts with 'Account Status'=Former i need to maintain whatever data was input into the field i.e. 11/1/2009.

So I tried creating a form rule:

When 'Account Status' is equal to 'Current'

Change Last Invoice Date to the Current Date

But that Rule only seems to trigger when someone edits the record, i'd like it to automatically change daily (ideally it would default to the first day of the current month but i can live with current date). I checked and unchecked the 'Fire "change" actions only when a condition changes from false to true' but that didn't work. 

What am i doing wrong or should i approach this differently?
Photo of Ringoparr

Ringoparr

  • 854 Points 500 badge 2x thumb

Posted 2 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 69,724 Points 50k badge 2x thumb
No problem, 

you just need a formula date field and not a form rule.

IF([Account Status] = "Current", Today(), [Last Invoice date])  
Photo of Ringoparr

Ringoparr

  • 854 Points 500 badge 2x thumb
Since my existing Last Invoice Date field is an input field, i'll have to create a new Last Invoice Date for Current clients.  I can live with that but i need help with one last formula.  Age.  The 'Age' field is a duration formula field.  It take into account Contract Start Date (date field) and 'Last Invoice Date (Former Clients)'.  My current equation looks like this:

[Last Invoice Date (Former Clients)]-[Contract Start Date]  

But i need the equation to look also look at my new formula field 'Last Invoice Date (Current Clients)'.

So essentially the equation should minus the Contract Start Date from either the Last Invoice Date (Former Clients) or the Last Invoice Date (Current Clients), whichever value is greater.  Can you help update the equation?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,724 Points 50k badge 2x thumb
If you have a relationship where 1 Client has many invoices, you can make a Summary field of the maximum invoice date and that would be your automated Last Invoice date so that you do not need data entry to maintain it.

as for the formula, we can use a formula variable here to make the formula more readable.

var date LastInvoiceDate = Max([Last Invoice Date (Former Clients)], [Last Invoice Date (Current Clients)]);

$LastInvoicedate - [Contract Start Date]