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

Question

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 the new Record ID's [Date Created] to calculate the time between for an SLA report function.For a description:I have 1 record that has an imbedded record called "Change History", Inside this change history, It logs any status changes that happen throughout the order.For Example:123 John Street (Order Number #27) had a job status of "On Hold", When this changed from on hold to "In Progress" a new change history is logged which would look something like the following:Embedded report showing all records relating to order number #27Record ID: #71, Date Created: 28/10/2024 14:22PM Order Created - 123 John StreetRecord ID: #84, Date Created: 29/10/2024 08:32AM On Hold - 123 John StreetRecord ID: #106, Date Created: 29/10/2024 08:52AM In Progress - 123 John StreetWhat I want to do is work out the duration between the new Record ID(106)'s Date Created and the old Record ID(84)'s date created (I want to use this formula to do the same for Record ID 71 to Record ID 84 but its off the same basis)So the end result would return a value of 30 Minutes.The reason being is I am trying create a countdown timer which is straight forward but once this record is no longer the newest record I need to find the time taken between the 2 fields.

MarkShnierYou · Accepted Answer

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.

Summary field for Max Date Created
Lookup down to child calling it [Date Created of Most Recent Change History].
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 · Answer

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.

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

Sign up

Login to the community