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 ...
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
    ------------------------------
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
------------------------------