Average time between records

Topic Labels: Formulas
829 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hello everyone, I have two linked tables (“Projects” and “Issued items”).
Issued items have a date field. They are not entered into the table chronologically - they are entered in batches, so the linked items are often out of order. (also note that the # of issued items per project is not consistent, could be as few as 2 or as many as 17. )

I am trying to calculate the average time between issued items for each project. I know I can do this with a series of rollups and formulas, but the solutions I can think of would require the linked records to show up in the correct order, and I can’t guarantee that.

I can create a concatenated string of all the issued dates, then use find (left or right) to pick out the issued item’s own date from the string, and then select the immediately adjacent characters / date. However this will only work if the links are in the right sequence. From what I can tell, I can sort the issued items table by issued date, but that doesn’t affect the order of the links in the project table - they are linked in the order they were created (or the reverse order).

How can I force the links to re-order, or work around it so that their sequence is irrelevant?

Any help is greatly appreciated!

1 Reply 1

The Batch update app lets you MANUALLY force the links in a linked record field to re-order (re-sort) by their primary field value.

Apps can’t be automated, though, so if you wanted to automate this process so that it automatically happened for you, you would either need to write a custom JavaScript or use an external automation tool like Integromat.