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?
Discussion posts and replies are publicly visible
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.
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]]
Replaced null on line 14 above with {} and it works as expected now.