Discussions

Expand all | Collapse all

Automatic Current Date Tracking Based on Status Field Choice

Jump to Best Answer
  • 1.  Automatic Current Date Tracking Based on Status Field Choice

    Posted 12-14-2019 22:14
    Edited by Syaeful Bahri 12-14-2019 22:16
    Hello everyone,

    Currently, I have multiple choice status field with this content: 

    Invoice received
    Documents review
    PRF preparation
    PRF approval
    Bank transfer process
    Payment completed

    I have created date field for each status to track the duration from one status to another status.

    This is the complete date field for each status
    Invoice received date
    Documents review date
    PRF preparation date
    PRF approval date
    Bank transfer process date
    Payment completed date

    The user chooses the actual status and change the related date field for the status. Below is the screenshot of my actual form

    Is there any solution to automatically the process?.
    User choose the actual status -> Quickbase grab the current date -> Quickbase record the date for the status

    for example:
    user choose status Invoice received, the current date is 13 December 2019, QB record the status for the invoice received is happening at 13 December
    user choose status Documents review date, the current date is 16 December 2019, QB record the status fo Documents review date is happening at 16 December

    So I can count the duration for one status to another status later.

    Many thank






    ------------------------------
    Syaeful Bahri
    ------------------------------


  • 2.  RE: Automatic Current Date Tracking Based on Status Field Choice
    Best Answer

    Posted 12-15-2019 12:15
    I think that this can be done with 6 form rules.  Here is the first rule

    CONDITIONS
    When the record is saved
    and Status has changed
    and Status is equal to Invoice received

    ACTIONS
    Change the Invoice received date to the current date



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Automatic Current Date Tracking Based on Status Field Choice

    Posted 12-15-2019 22:13
    Hi Mark,

    Thank you for your response. Do you think it's possible to remove all the status date field part?

    Invoice received date

    Documents review date
    PRF preparation date
    PRF approval date
    Bank transfer process date
    Payment completed date

    Rather than creating dynamic form rules?, but it still grabs the current date when the status field is chosen?

    Many thank Mark

    ------------------------------
    Syaeful Bahri
    ------------------------------



  • 4.  RE: Automatic Current Date Tracking Based on Status Field Choice

    Posted 12-15-2019 22:16
    Sorry, I am not understanding.

    You want to "delete" these fields?

    Invoice received date
    Documents review date
    PRF preparation date
    PRF approval date
    Bank transfer process date
    Payment completed date

    Where would you store those dates then?  Perhaps I am not understanding your question.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Automatic Current Date Tracking Based on Status Field Choice

    Posted 12-15-2019 22:26
    Hi Mark,

    This is my not understanding. I apologize. Where I would store those dates then. You are definitely correct.

    I'm gonna give a go for dynamics form rule solution. I'm thinking about grabbing the current date from the computer system. But that's "another story". 

    Many thanks, Mark

    ------------------------------
    Syaeful Bahri
    ------------------------------



  • 6.  RE: Automatic Current Date Tracking Based on Status Field Choice

    Posted 12-16-2019 06:13
    My solution seems more simple than Babi's.  But depending on how much of an Audit trail you want as t9 who did the update and at exactly what date and time, there are other enhancements possible.

    As for grabbing the computer date, I assume that you are aware that you can set your application to your correct local time zone as long as all your users are in similar time zones.  The form rules know what the current date is.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Automatic Current Date Tracking Based on Status Field Choice

    Posted 12-16-2019 21:29
    Thank Mark,

    I'm curious what is the difference between your dynamics form rule solutions with this



    ------------------------------
    Syaeful Bahri
    ------------------------------



  • 8.  RE: Automatic Current Date Tracking Based on Status Field Choice

    Posted 12-16-2019 23:16
    The reason I suggested a form rule that did not fire until the record was saved, was just in case the user wasn't sure about what status they were choosing I didn't want the date to fill-in until they made their final decision and did the save.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 9.  RE: Automatic Current Date Tracking Based on Status Field Choice

    Posted 12-17-2019 01:13
    Thanks Mark, really appreciated

    ------------------------------
    Syaeful Bahri
    ------------------------------



  • 10.  RE: Automatic Current Date Tracking Based on Status Field Choice

    Posted 12-16-2019 01:57
    You should make use of  mix of solutions for this (QB action, timeofDay fields and a log table) to record the timestamp when the status was changed.

    We do track duration for every activity on our production apps. Feel free to connect and understand more on how we achieve this.

    Create a log table:
    Related Record ID
    Status
    Start Date
    Start TimeOfDay
    End Date
    End TimeOfDay


    Create below fields in the main table where you want to change the status
    CF-Start Date - Formula Date - ToDate(Now)
    CF-Start TimeOFDay  - Formula TimeofDay  - ToTimeOFDay (Now())
    Start Date
    Start TimeOfDay
    End Date     - Use Formula Date = ToDate([Date Modified)
    End TimeOfDay - Use Formula TimeOfDay =  ( ToTimeOFDay([Date Modified]))

    Write a form rule on Save record.
    When the record is saved,
    change Start Date -> value from CF-Start Date
    Change Start TimeOfDay - > Value from CF-Start TimeOFDay

    Make sure that both CF- fields are used on the form. Otherwise the value will not be updated.

    Write a QB Automation  on Add/Modify to copy the values from above fields to log table. Use condition when StartTimeOFDay field changes.

    Now Create relationship between your main table and log table using related record id. Use Summary fields or reverse relationship using max id for each status to derive the duration.

    Hope that helps.

    ------------------------------
    Babi Panjikar
    ------------------------------