Skip to main content
Solved

Automatic Current Date Tracking Based on Status Field Choice

  • December 15, 2019
  • 0 replies
  • 149 views
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
------------------------------

Best answer by MarkShnierYou

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
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+22
  • MarkShnierYou
  • Quickbase Alumni
  • Answer
  • December 15, 2019
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
------------------------------
Mark Shnier. Your Quickbase Coach
  • BabiPanjikar
  • Quickbase Alumni
  • December 16, 2019
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
------------------------------
Terms & ConditionsAccessibility statement