Forum Discussion

SyaefulBahri3's avatar
SyaefulBahri3
Qrew Trainee
6 years ago
Solved

Automatic Current Date Tracking Based on Status Field Choice

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
------------------------------
Forms
  • MarkShnier__You's avatar
    MarkShnier__You
    6 years ago
    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
    ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    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
    ------------------------------
    • SyaefulBahri3's avatar
      SyaefulBahri3
      Qrew Trainee
      6 years ago
      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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        6 years ago
        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
        ------------------------------
  • BabiPanjikar's avatar
    BabiPanjikar
    Qrew Assistant Captain
    6 years ago
    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
    ------------------------------