Skip to main content
K
kellyq2641
September 10, 2024
Question

Calculation between records in a grid - Number of days between dates

  • September 10, 2024
  • 8 replies
  • 0 views

Hi All,

I have grid displaying certain records for a specific user, lets say I want to calculate how many days did it take the user to create the next record so for example in the image attached the user created a few on the same day, so if I wanted to add another column next to this one to display number of days, how can I achieve this? the calculation part? because I need to grab the first date of the previous record with the date of the next created record and so on . 

Is this even possible, or what are your suggestions on finding this kind of information?

Thanks in advance

8 replies

stewart.burchell
stewart.burchell
September 10, 2024

Let's start with the basics;

  • The data in your grid will be sourced from an array
  • Ensure that the array is sorted in date order so that you can process the data in sequential order
  • Process the data using a!forEach()
  • You can do a date difference between the current item and the adjacent item (the latter using the current item's index offset by 1)
  • note that there will be one item in your array that won't have an appropriate adjacent item so you'll need to make an exception for that item
K
kellyq2641Author
September 10, 2024

This is similar to the idea I had, but when you say the adjacent item, are we doing like fv!index + 1 ? 

would you mind sharing an example of code? 


stewart.burchell
stewart.burchell
September 10, 2024

Something like this:

a!localVariables(
  local!mySortedArrayOfDates: {
    todate("09/10/2024"),
    todate("09/05/2024"),
    todate("09/01/2024")
  },

  a!forEach(
    items: local!mySortedArrayOfDates,
    expression: if(
      fv!isFirst,
      tointervalds(null),
      local!mySortedArrayOfDates[fv!index - 1] - fv!item
    )
  )
)

The result is an array of "intervals" in "Day to Second" format that will align with the array of dates you want to display.

gayathris111098
gayathris111098
September 11, 2024

a!localVariables(
local!records:{} /* Query records sorted by createdDate for a specific user */,

local!recordWithDays: a!forEach(
items: local!records,
expression: a!map(
recordId: fv!item.recordId,
createdDate: fv!item.createdDate,
userId: fv!item.userId,
daysSinceLastRecord: if(
fv!index = 1,
null, /* No previous record for the first one */
days360(
date1: fv!item.createdDate,
date2: local!records[fv!index - 1].createdDate,

)&" days"
)
)
),

a!gridField(
label: "User Records",

columns: {
a!gridColumn(
label: "Record ID",
value: fv!row.recordId
),
a!gridColumn(
label: "Created Date",
value: fv!row.createdDate
),
a!gridColumn(
label: "Days Since Last Record",
value: fv!row.daysSinceLastRecord
)
},
data: local!recordWithDays
)
)

Terms & ConditionsAccessibility statement