Incert Date Fromula

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

I have a box that when checked, I would like the formula to populate the current date. The formula is listed below...the problem is...I used Today() which changes daily! What function should I replace that with?

If([TASK COMPLETE] and IsNull([COMPLETE DATE]),Today(),

[TASK COMPLETE] ,[COMPLETE DATE])

Photo of Joshua

Joshua

  • 0 Points

Posted 5 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
You will need to do this as a form rule.



When the record is saved

and

when all of the following are true

[Task complete] is checked

[complete date] is equal to (blank out the date)

change the value in the field [complete date] to "the current date"



Note that form rules are called form rules because they work on forms - i.e. they will not work in grid edit.
Photo of Joshua

Joshua

  • 0 Points
Unfortunately, all my field crews work this data from a tablet on grid view.  I am trying to create a formula that notes:  When the task has been checked off as compete, then the date is marked as today.  This prevents them from having to add the current date many times over for each task completed.   In addition, I need a date that can be edited if they forget to check off daily....then the date that was added manually (not as common) would supersede the formula date.  I am okay if I need to have 2 or three date fields to get this done...my reports will only pull from the one that supersedes.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
OK, so we go to plan B. The other way to capture a date is to use a field which logs the changes.

We will need a few fields.

[Update Completed] will be a multiple choice field with the choices of


.Yes


No

The purpose of the .Yes is to get the Yes to sort before the No. Check the checkbox to "log changes" so that we can capture the date of the change.


The next field will be a formula text field [Completed] with the formula

var text LastUpdate = Trim(Right([Update Completed],"]"));
If($LastUpdate = ".Yes","Yes","No")




The next field will be date field called [Date Completed Override]

The last field will be a formula date field called [Date Completed] which will parse out the date


If(
[Completed]<>"Yes",null,
not IsNull([Date Completed Override]),[Date Completed Override],
ToDate(Left(Right([Update Completed],"["),9)))





Photo of Joshua

Joshua

  • 0 Points
I have entered all the columns as noted above...but when I check the [task complete] checkbox nothing changes in the dates....what am I still missing?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
Remove the [Task Complete] checkbox from your Grid edit and add the fields I created.  My multiple choice field for .Yes and No will leave a Log trail of the updates.

Try it first on a regular form and you will see how it works.
Photo of Joshua

Joshua

  • 0 Points
I see how it works...but....my guys use tablets and the testing is not working on the tablet.  The drop down Yes or No is not working with the finger touch.  Any plan "C"?
Photo of Joshua

Joshua

  • 0 Points
I need to be able to take date that shows up in the field, then have that date in another field...only never let it change.
Photo of Joshua

Joshua

  • 0 Points
kind of like a snapshot field...though I don't know how those work exactly.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
I tried my solution on my iPad and i agree that is less than friendly.

But try this

Change the name of the update field to [Update Completed with Y /N] and change it from a multiple choice field to just a data entry field.

Then change the [Completed] field formula to

var text LastUpdate  = Trim(Right([Update Completed with Y /N],"]"));
If(Contains($LastUpdate,"Y"),"Yes","No")

Its the multiple choice on that append only update field which is causing iPad weirdness.  So, this change is to make it a plain data entry field.
Photo of Joshua

Joshua

  • 0 Points
I think that will work...Thanks!  Now...how do I clear all my name info from updated completed data that was generated from my testing?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
Wonderful!  

To clear that logged history just change that field property to uncheck "log changes".  Then do a report or just an advanced search of the records where that field is not blank and grid edit to blank.  Then turn Log Changes back on.
Photo of Joshua

Joshua

  • 0 Points
one more questions....how would I adjust the date completed formula to add....if there is a date completed override...then it should show that date even if the completed still says "no".  Would that just be another if formula added?  We already have a heap of data...and this would make it so that I don't have to change several hundred lines to yes.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
If(
not IsNull([Date Completed Override]),[Date Completed Override],
[Completed]="Yes",ToDate(Left(Right([Update Completed with Y /N],"["),9)))
Photo of Joshua

Joshua

  • 0 Points
Thanks!  That works Perfect!
Photo of Joshua

Joshua

  • 0 Points
One last slight adjustment....what if function would I add to my update Completed with Y/N to have it change to yes if there is a date completed?  We are going to upload several dates into the override section and that will change the date complete...but it still says "No".   
var text LastUpdate  = Trim(Right([Update Completed],"]"));
If(Contains($LastUpdate,"y"),"Yes","No")

Thanks again for you help!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
Good point

var text LastUpdate  = Trim(Right([Update Completed],"]"));
If(not isnull([date completed]),"Yes",
Contains($LastUpdate,"y"),"Yes","No")
Photo of Joshua

Joshua

  • 0 Points
when I input this formula...I get an error that says "A formula may not contain a reference to itself directly or indirectly through another formula."
Photo of Joshua

Joshua

  • 0 Points
I think this is coming from the formula in our date completed.  It is referring back to it looking for that "yes".
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
ah, a circular reference .....

Try this
var text LastUpdate  = Trim(Right([Update Completed],"]"));
If(not isnull([date completed override]),"Yes",
Contains($LastUpdate,"y"),"Yes","No")
Photo of Joshua

Joshua

  • 0 Points
That Worked!  Thanks
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
:)