Auto populate date field based on another - issues with initial value and app formula

[jgiangra]

Hi, I have a date field that should show and default to today's date once another field is marked "Yes". Show if on the date field working fine, its the initial value / app formula to get todays date that doesnt achieve exactly what I'm looking for. 

Fields: "Document Sent" (Y/N) and "Date Document Sent":  I tried the following on the Date Document Sent field:

(1) Initial Value TODAY ( ), that appears fine in the app since the show if formula hides the date field until Document Sent = Yes; however, it populates the date in the backend sheet when the record is created, which I cant have becasue there is a looker dashboard running on that source. 

(2) If I use the Initial Value of IF([Document Sent]="Yes", TODAY( ), " ") with reset on edit unchecked (since the date should not change once its set), it stops the date from populating in the backend sheet but the date field is not populating once Document Sent is marked Yes.

(3) If I use an app formula with the ^  above mentioned formula with reset on edit unchecked or checked, the date always changes on edit, which makes sense with the formula in place...

Is there a way to have the date field only populate todays date when the document sent field is marked Y in both the app and the backend sheet, while also remaining that value when that record is edited in the future? 

[Rafael_ANEIC-PY]

Hello there @jgiangra 

The problem with point 2) is that since it's an initial value once the row is saved it won't recalculate again, so it doesn't matter if your condition verifies after that, the calculation has been made.

I suggest you either:

1. Mark your document as sent using an action that also sets the date to TODAY() at that moment
2. Create an automation that looks for changes to the document sent column and once it changes to "Yes" it adds the date to the relevant column

Both are fairly straightforward to do, but if you don't want to change how your users change the document sent status I suggest you go for option number 2.

[jgiangra]

Hi! thank you for the reply. I thought of these options.. I already have so many actions so I didnt want to introduce more. I like the idea of scheduling a bot but the user wants to visibly see the date field update when they mark document sent. 

[dbaum · Accepted solution]

Try:

Initial value: IFS([Document Sent], TODAY())
Reset on edit: AND(NOT([_THISROW_BEFORE].[Document Sent]), [Document Sent])

[jgiangra]

@dbaum this worked!!! Thank you SO much! I really appreciate it 🙂