Converting Flattened Data to Nested

Question

I currently am doing an aggregate query that groups by two fields and sums a third field.  The result is the following:
[period:1,week:1,total_hours:2]; [period:5,week:1,total_hours:3]; [period:2,week:2,total_hours:5]; [period:6,week:2,total_hours:8]; [period:12,week:3,total_hours:4]; [period:3,week:4,total_hours:2]
I am trying to pass this data into a grid but with one row per "week" value with an array of period / hour values as below:
[week:1,periods:[[period:1,total_hours:2],[period:5,total_hours:3]]]; [week:2,periods:[[period:2,total_hours:5],[period:6,total_hours:8]]]; [week:3,periods:[[period:12,total_hours:4]]]; [week:4,periods:[[period:3,total_hours:2]]]
- Can this be done in the queryRecordType itself?
- Which array methods would be best suited to handle this?
- Should I be using foreach to do this?

jonathanf3696 · Accepted Answer

I've gotten close with this code:a!forEach(    items: enumerate(4),    expression: a!localVariables(      local!week: fv!index,      local!periods: a!forEach(        items: local!events,        expression: {          if(            fv!item.week = local!week,            a!map(              period: fv!item.period,              hours: fv!item.hours            ),            null          )        }      ),      {        a!map(week: fv!index, periods: local!periods)      }    )  )However its adding an entry for each null in the else condition:[week:1,periods:[period:1,hours:2]; [period:5,hours:3]; ; ; ; ]; [week:2,periods:; ; [period:2,hours:5]; [period:6,hours:8]; ; ]; [week:3,periods:; ; ; ; [period:12,hours:4]; ]; [week:4,periods:; ; ; ; ; [period:3,hours:2]]

jonathanf3696 · Answer

I am trying to use foreach in the following way with contains:

a!forEach(
    items: local!events,
    expression: {
      if(
        or(
          a!isNullOrEmpty(local!result),
          not(contains(local!result["week"], fv!item.week))
        ),
        append(local!result, a!map(week: fv!item.week, periods: { a!map(period: fv!item.period, hours: fv!item.total_hours)})),
        false
      )
    }
  )

However, it does not appear that the "contains" method is properly identifiying that a week record has already been added to my result.

Sign up

Login with SSO

Login to the community

Login with SSO