Forum Discussion

Del's avatar
Del
Qrew Cadet
6 years ago

Status Field based on date

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
------------------------------
Formulas and functions
  • BrandonDrake's avatar
    BrandonDrake
    Qrew Cadet
    6 years ago
    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
    ------------------------------
    • Del's avatar
      Del
      Qrew Cadet
      6 years ago
      Thanks, works

      ------------------------------
      Ermias Bean
      ------------------------------
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    6 years ago
    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
    ------------------------------