Forum Discussion

Qrew Trainee

4 years ago

Get field from different records in the same table, related to parent?

Hi All, I'm trying to create an audit log with Pipelines with the intention of tracking how long a record is in each particular status and thought using {{a.$prev.updated_at}} would work to record ...

Pipelines

Relationships

MarkShnier__You

Qrew Legend

4 years ago

My suggestion is to use a child table like you suggested, but on each record you would have three fierlds

Status,
Start date/time
End date/time.

The pipeline would detect and trigger two steps. One would be to search for any existing children (there should only be one) and update their End date/time. Then it would create a new record for the start of the New Status.

Then, of course each child will have a duration by calculation. Then you can embed a summary report on the parent of the total duration by status or else make separate summary fields for the total duration for each separate status choice.

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

DanielJohnson2
Qrew Trainee
4 years ago
Thanks For the reply Mark. How do I tell the Pipeline which record to update with the End Date/Time in the child table?

------------------------------
Daniel Johnson
------------------------------
- MarkShnier__You
  Qrew Legend
  4 years ago
  Well, if the status changes, then any existing children with an empty End date need to be updated. So you would have a search step, then a For Each loop which would update the end date time. There is Jinja syntax for the current date/ time, or else you can create a field in native quickbase for the current date and time and use that field to populate the date time field.
  
  ------------------------------
  Mark Shnier (YQC)
  Quick Base Solution Provider
  Your Quick Base Coach
  http://QuickBaseCoach.com
  mark.shnier@gmail.com
  ------------------------------
  - DanielJohnson2
    Qrew Trainee
    4 years ago
    Success! I'm still new to Pipelines, so I wasn't aware of the Search and For Each loops functionality, it wasn't figuring into my possible solutions. Thanks Mark!
    
    ------------------------------
    Daniel Johnson
    ------------------------------