Forum Discussion

MattMc's avatar
MattMc
Qrew Trainee
2 months ago

A way to look up a previous Record ID's [Date Created] Field

Hi, thank you in advance for any help, I am currently trying to figure out a way to look up a previous Record ID's date created and calculate the time between the old record ID's [Date Created] and ...
formulas
  • MarkShnier__You's avatar
    MarkShnier__You
    2 months ago

    Ok, it's relatively easy to calculate the date created between the most recent child and the previous child.  If you want to know the time between each successive child record, that's a whole different question and more difficult.

    1. Summary field for Max Date Created
    2. Lookup down to child calling it [Date Created of Most Recent Change History].
    3. Summary field for Maximum Date Created subject to the filter that the [Date Created] is less than the  value in the field [Date Created of Most Recent Change History].  Call this [Date Created of 2nd Most Recent Change History

    'er, yur done!

PeterMonahan1's avatar
PeterMonahan1
Qrew Member
2 months ago

One way to get the field you're looking for on each individual Change History record would be to use a combination of relationship fields and formula queries.

  1. Create a summary on the Order table to get the total # of child Change History records. Pass that field down to the Change History records as a lookup field.
  2. On the child table create a formula numeric we'll call Change # that queries for other Change History records where the Related Order is the same as the current record and where Record Id# is less than or equal to the current records Id and get the number of returned records. (This will tell us where a Change History falls in order)
  3. On the child table create the final formula duration field.
    1. It will first query for the Change History record where the Related Order is the same as the current record and Change # is one less than the current records Change #
    2. Get the Date Created Field from the queried record and calculate the difference between that and the current records Date Created.

One thing to consider is that this new formula field has a query that looks at a query that looks at a summary. This is in general not a great practice as when you get into very large reports the fields can sometimes skip the queries to load quicker and give you incorrect information.

If you decide to go this route and get stuck writing out the formulas I can point you in the right direction.