Skip to main content
Question

Status Field based on date

  • February 24, 2020
  • 0 replies
  • 28 views
  • Del
  • Quickbase Alumni
In need of a formula status field that will be used to highlight a record based on the date field "Action Date" being within the current month. Example, If Action Date is not within current month then change status to "NOT Completed", and if it does fall in the same month, then "Completed".

------------------------------
Ermias Bean
------------------------------
This topic has been closed for replies.

  • BrandonDrake
  • Quickbase Alumni
  • February 24, 2020
Hello Ermias, 

Would something like this work? 

if(not isNull([Action Date]) and ([Action Date] >= FirstDayofMonth(Today()) and [Action Date] <= LastDayOfMonth(Today())), "Completed", "Not Completed")

------------------------------
Brandon Drake
JHI Group
Monroeville OH
------------------------------
Forum|alt.badge.img+15
  • DonLarson
  • Quickbase Alumni
  • February 24, 2020
Ermias,

How did you build Status?

I can think of three very different options.

  • Formula Text Field
  • Multichoice
  • Related Status 
  • Status Change

The first one is very straight forward and is solved by Brandon's example.

A multichoice text field is the next one and would require you to use the API or an automation change the value in that field.  There is an advantage to this that the User can just click it at any time and pick a value as well.   The down side is you do not who did it, when they did and if the values change you will have orphaned the earlier entries.    A sample URL that does this would look like this

var text URLAPIEdit =
  URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&apptoken="&
  $Token &
  "&rid=" &[Record ID#] &
  "&_fid_XX=" & "Complete";

This is telling field XX to change value to "Complete"   You need more info still but hopefully you get the idea.

Your third option is to drive it with a relationship like this

Now the Status is the Parent record of the project.   You can change values in the Status table and all the Projects will update.   Changing "complete" to "Complete"  will not break things.

You still need the API to make this happen but you are setting the value of the field Related Status to make the Status Value flow to the Project.   

My favorite method is a bit more complicated and looks like this:



Now you are no longer changing the values of any fields in the Project.  Every time the Project Status needs to change, you are actually adding a new record to the Project Status Change table.   The entire history is preserved along with other important information like when did the status change, who changed the status, what were the notes when it changed and anything else that it is important in the long term to evaluate Projects, workflows, bottle necks and so on.    You probably do not need this immediately.  However at some point a manager is going to ask, why did this take so long or who is the worst project manager?

If you are on your first QB application, start with the Formula Text.  If you are already getting probing questions about "why" instead of just what is the status, then consider a more complex architecture.






------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------
Terms & ConditionsAccessibility statement