How to queryRecordType with gouping by date range and offset (hours) and also include quantity counters

Do you already have something? I mean, asking us to analyze your data structure and implement running code for you might a bit too much!?!

Hello again. Am having an update. Unfortunately I couldn't do this with just one query. I had to loop threw pas days a make a query for each day according to the time range requirements. Here is my code. Am sure there is a way to improve my code even more. If you have any suggestions to improve it let me know. :)

/* LunchOrders Interface */

{
  a!localVariables(
    local!orderDate,
    local!mySelection,
    local!selectedRow,
    a!columnsLayout(
      columns: {
        a!columnLayout(
          contents: {
            a!sectionLayout(
              label: "Lunch Orders",
              contents: {
                a!localVariables(
                  local!startDate: today() + 1, /*+ intervalds(09, 30, 0),*/
                  local!endDate: local!startDate - 10,
                  /* Get all dates from today to 9 days back*/
                  local!workingDatesWithNulls: a!forEach(
                    items: enum(
                      1 + tointeger(local!startDate - local!endDate)
                    ),
                    expression: {
                      if(
                        calisworkday(local!startDate - fv!item),
                        local!startDate - fv!item,
                        null
                      )
                    }
                  ),
                  /* if fv!item is null return position (index)*/
                  local!nullIndices: a!forEach(
                    items: local!workingDatesWithNulls,
                    expression: if(isnull(fv!item), fv!index, null())
                  ),
                  local!getNullIndices: remove(
                    local!nullIndices,
                    where(
                      a!forEach(
                        items: local!nullIndices,
                        expression: if(a!isNullOrEmpty(fv!item), true, false)
                      )
                    )
                  ),
                  /* Remove positions that have null value from array with dates */
                  local!workingDates: remove(
                    local!workingDatesWithNulls,
                    local!getNullIndices
                  ),
                  a!forEach(
                    items: local!workingDates,
                    expression: {
                      a!localVariables(
                        local!lunchOrdersPerDay: a!queryRecordType(
                          recordType: 'recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation',
                          fields: {
                            a!aggregationFields(
                              groupings: {
                                a!grouping(
                                  field: 'recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation.relationships.{839da51e-c9d0-44c7-a17c-9cf842ac54d8}product.fields.{65910d5d-681b-4729-b01c-27bf7096c77c}id',
                                  alias: "id"
                                ),
                                a!grouping(
                                  field: 'recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation.relationships.{839da51e-c9d0-44c7-a17c-9cf842ac54d8}product.fields.{6dd1c941-3e1f-45c7-9a13-bfdc7e900d30}name',
                                  alias: "product"
                                )
                              },
                              measures: {
                                a!measure(
                                  field: 'recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation.fields.{62806eb9-d749-4771-838a-ff1bc24c5197}quantity',
                                  function: "SUM",
                                  alias: "count"
                                )
                              }
                            )
                          },
                          filters: {
                            a!queryFilter(
                              field: 'recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation.relationships.{839da51e-c9d0-44c7-a17c-9cf842ac54d8}product.fields.{fcd0f810-cba5-406a-9cf0-930e1718bcf2}categoryId',
                              operator: "=",
                              value: 5
                            ),
                            a!queryFilter(
                              field: 'recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation.fields.{399b36b5-c6ae-4b9b-a144-3a4639dcab41}createdOn',
                              operator: "between",
                              value: { gmt(fv!item - intervalds(14, 30, 0)), gmt(fv!item + intervalds(09, 30, 0)) }
                            )
                          },
                          pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 5000)
                        ).data,
                        {
                          if(
                            a!isNotNullOrEmpty(local!lunchOrdersPerDay),
                            rule!ICOS_LunchOrdersPerDay(
                              lunchDate: fv!item,
                              lunchOrders: local!lunchOrdersPerDay,
                              orderDate: local!orderDate,
                              mySelection: local!mySelection,
                              selectedRow: local!selectedRow
                            ),
                            {}
                          )
                        }
                      )
                    }
                  )
                )
              }
            )
          }
        ),
        a!columnLayout(
          contents: if(a!isNotNullOrEmpty(local!selectedRow["id"]),
          {
            a!localVariables(
              local!perProductEmloyeeOrders: a!queryRecordType(
                recordType: 'recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation',
                fields: {
                  'recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation.relationships.{679bda44-9473-47de-9a65-ef3a5a314c9d}order.relationships.{ffcd3b5e-f97c-4f0c-acdc-0844a9dca960}assigneeUser'
                },
                filters: {
                  a!queryFilter(
                    field: 'recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation.fields.{f264cc1d-2ee5-49b0-8645-76b6d4195e8c}productId',
                    operator: "=",
                    value: local!selectedRow["id"]
                  ),
                  a!queryFilter(
                    field: 'recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation.fields.{399b36b5-c6ae-4b9b-a144-3a4639dcab41}createdOn',
                    operator: "between",
                    value: { gmt(local!orderDate - intervalds(14, 30, 0)), gmt(local!orderDate + intervalds(09, 30, 0)) }
                  )
                },
                pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 5000)
              ).data,
              {
                a!cardLayout(
                  contents: {
                    a!richTextDisplayField(
                      labelPosition: "COLLAPSED",
                      value: {
                        a!richTextHeader(
                          text: text(local!orderDate,"dd/mm/YYYY") & " - " & local!selectedRow["product"],
                          size: "SMALL"
                        )
                      },
                      marginAbove: "LESS"
                    ),
                    a!cardLayout(
                      contents: {
                        a!gridField(
                          label: text(local!orderDate,"dd/mm/YYYY") & " - " & local!selectedRow["product"],
                          labelPosition: "COLLAPSED",
                          data: local!perProductEmloyeeOrders,
                          columns: {
                            a!gridColumn(
                              label: "Order Id",
                              value: a!richTextDisplayField(
                                value: a!richTextItem(
                                  text: "#" & fv!row['recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation.fields.{7effecc6-7ad7-4664-a95e-b4572964fab7}orderId'],
                                  link: a!recordLink(
                                    recordType: 'recordType!{a6586e03-53d4-47ea-8f21-52d658700d3b}ICOS Order',
                                    identifier: fv!row['recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation.fields.{7effecc6-7ad7-4664-a95e-b4572964fab7}orderId']
                                  ),
                                  linkStyle: "STANDALONE"
                                )
                              )
                            ),
                            a!gridColumn(
                              label: "Employee",
                              value: if(a!isNotNullOrEmpty(fv!row['recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation.relationships.{679bda44-9473-47de-9a65-ef3a5a314c9d}order.relationships.{ffcd3b5e-f97c-4f0c-acdc-0844a9dca960}assigneeUser']),
                              fv!row['recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation.relationships.{679bda44-9473-47de-9a65-ef3a5a314c9d}order.relationships.{ffcd3b5e-f97c-4f0c-acdc-0844a9dca960}assigneeUser.fields.{SYSTEM_RECORD_TYPE_USER_FIELD_firstAndLastName}firstAndLastName'],
                              ""
                              )
                            ),
                            a!gridColumn(
                              label: "Message",
                              value: fv!row['recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation.fields.{1c8fb762-47aa-47a7-b677-dff286da94d1}message'],
                              backgroundColor: "INFO"
                            ),
                            a!gridColumn(
                              label: "Quantity",
                              value: fv!row['recordType!{43c0dc45-98e0-40cf-aae3-b85bfa2dfeee}ICOS Order Product Variation.fields.{62806eb9-d749-4771-838a-ff1bc24c5197}quantity'],
                              align: "END"
                            )
                          },
                          validations: {},
                          borderStyle: "LIGHT",
                          shadeAlternateRows: true,
                          refreshInterval: 0.5
                        )
                      },
                      height: "AUTO",
                      style: "NONE",
                      padding: "NONE",
                      marginBelow: "STANDARD",
                      showBorder: false
                    )
                  },
                  height: "AUTO",
                  style: "#ffede7",
                  shape: "ROUNDED",
                  padding: "STANDARD",
                  marginBelow: "STANDARD",
                  showBorder: false
                )
              }
            )
          },
          {}
        )
        )
      }
    )
  )
}

/* LunchOrdersPerDay sub interface */

a!localVariables(
  local!toggleCard: if(ri!lunchDate = ri!orderDate, true, false),
  {
    a!cardLayout(
      contents: {
        a!sideBySideLayout(
          items: {
            a!sideBySideItem(
              item: a!richTextDisplayField(
                value: a!richTextItem(
                  text: text(ri!lunchDate, "dd/mm/YYYY"),
                  link: a!dynamicLink(
                    saveInto: {
                      a!save(
                        target: local!toggleCard,
                        value: not(local!toggleCard)
                      )
                    }
                  ),
                  linkStyle: "STANDALONE",
                  style: "STRONG"
                )
              )
            ),
            a!sideBySideItem(
              item: a!richTextDisplayField(
                value: a!richTextIcon(
                  icon: "angle-down",
                  link: a!dynamicLink(
                    saveInto: {
                      a!save(
                        target: local!toggleCard,
                        value: not(local!toggleCard)
                      )
                    }
                  ),
                  linkStyle: "STANDALONE"
                ),
                align: "RIGHT"
              )
            )
          }
        ),
        a!cardLayout(
          contents: {
            a!gridField(
              labelPosition: "COLLAPSED",
              data: ri!lunchOrders,
              columns: {
                a!gridColumn(
                  label: "ID",
                  sortField: "id",
                  value: fv!row.id
                ),
                a!gridColumn(
                  label: "Product",
                  sortField: "product",
                  value: fv!row.product
                ),
                a!gridColumn(
                  label: "Quantity",
                  sortField: "count",
                  value: fv!row.count,
                  align: "END"
                )
              },
              pageSize: 10,
              selectable: true,
              selectionStyle: "ROW_HIGHLIGHT",
              selectionValue: if(ri!lunchDate = ri!orderDate, ri!mySelection, null),
              selectionSaveInto: {
                a!save(
                  ri!selectedRow,
                  index(
                    fv!selectedRows,
                    length(fv!selectedRows),
                    null
                  )
                ),
                a!save(
                  ri!mySelection,
                  index(save!value, length(save!value), null)
                ),
                a!save(
                  target: ri!orderDate,
                  value: ri!lunchDate
                )
              },
              validations: {},
              borderStyle: "LIGHT",
              refreshInterval: 1.0
            )
          },
          showWhen: local!toggleCard,
          style: "NONE",
          padding: "NONE",
          showBorder: false
        )
      },
      style: "INFO",
      shape: "ROUNDED",
      padding: "STANDARD",
      marginBelow: "STANDARD",
      showBorder: false
    )
  }
)