Auto Fill Date Field Based Off of Another Field

  • 1
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Hello, 

I would have three fields that I would like to work together, [Status], [Scheduled Date] and [Completion Date]. Essentially I want the [Completion Date] field to auto fill with the same date as the [Scheduled Date] field once the [Status] field is equal to "Complete". 

Any thoughts on how to make this work? I'm a little new at QuickBase. 

Thanks!
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb

Posted 2 years ago

  • 1
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
You will need to set a "dynamic form rule" to populate that date, when your status field is changed.  

If you are an Admin on the account and you open up one of the records
>
In the top right you will see "Customize This Form"
>
After opening up those settings, click on the "Dynamic Form Rules" tab
>
Create a new rule with the needed logic
>
Something to the affect of:

Condition
If [Status] is equal to "Complete"

Action:
Change [Completion Date] to the value in the field [Scheduled Date]
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb
"&_fid_17=" & URLEncode([Scheduled Date]);
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
the portion that has "&_fid_17="&Now();
is already writing the date.  

Any reason why you don't want to keep using the button to mark things complete.

I much prefer buttons over form rules.
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
Matthew, 

I definitely want to continue using the button. I will try Chris' update to the button code to get it to populate the correct date. 
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

It has occurred to me after reading over this thread that you may be trying to write a date field value to a date/time field value.

Now() is a function that gives you the current date 'and' time of day. Are both [Completion Date] and [Scheduled Date] Date/Time fields? Or, is one of them just a Date field?


Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
SUCCESS!!! Thank you guys so much!!
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

Andreonna,

Are you certain the two dates will always match? Is it not possible that [Completion Date] may occur sooner than or later than [Scheduled Date] ?

Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
The field needs to be able to be edited in the case that they are not the same. 95% of the time they will be the same date. 
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

You would auto populate the [Completion Date] field much like Matthew said, except when [Status] = "Complete", change [Completion Date] to the current date in the form rule. You will still be able to edit the [Completion Date] field manually, the form rule allows you to populate the date field automatically upon a certain condition.

In the form rule, you may want to check the box that says, "change occurs when condition changes from false to true". This will keep the [Complete Date] from changing to the current date every time the record is edited subsequent to its completion.

Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
Chris, Can you please look at my comment on Matthews post as well regarding the URL button. This seems to be causing an issue. 

Thanks!