• State Not Answered
  • Replies 5 replies
  • Subscribers 13 subscribers
  • Views 385 views
  • Users 0 members are here
Related Discussions
Home » Discussions » Data and Records

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

christodoulosp0001

Hello everyone,

I have 3 record types. "Orders", "Products" and "Order Product Variations" (relationship).

Orders (id, createdOn)
Products(id, categoryId)  *category must be equal to 5
Order Product Variations (id, productId, quantity, createdOn)

I want to create an interface that shows which products are ordered for each day.

  1. The orders products that are included for each day are from the previous date at 09:30:01 to current day at 09:30:00
  2. I want to have a counter for each product (including the quantity)

I want to ask what is the best way to proceed with this.
This interface will be used by the Canteen Manager to prepare lunch orders for employees.

At a later stage I will try to show which employees ordered each food.

Case scenarios:

##########Case No1###################


Today: 23 Feb 2024 08:00 AM

1st list el:
--------------------------------------------
Orders for 23/02/2024

  1. Food A                 qty: 7
  2. Food B                qty: 4
  3. Food C                qty: 2

--------------------------------------------

2nd list el:
--------------------------------------------
Orders for 22/02/2024

  1. Food A                 qty: 9
  2. Food D                qty: 4
  3. Food F                qty: 3

--------------------------------------------

##################################

###########Case No2##################


Today: 23 Feb 2024 09:31 AM

1st list el:
--------------------------------------------
Orders for 24/02/2024

  1. Food B                 qty: 1

--------------------------------------------

2nd list el:
--------------------------------------------
Orders for 23/02/2024

  1. Food A                 qty: 7
  2. Food B                qty: 4
  3. Food C                qty: 2

--------------------------------------------

3rd list el:
--------------------------------------------
Orders for 22/02/2024

  1. Food A                 qty: 9
  2. Food D                qty: 4
  3. Food F                qty: 3

--------------------------------------------

##################################

  Discussion posts and replies are publicly visible

  • 0
    Certified Lead Developer

    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!?!

  • 0 in reply to Stefan Helzle

    The code I have at the moment doesn't work for me as I want it. I tried this but data are not as I want them...

    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.fields.{399b36b5-c6ae-4b9b-a144-3a4639dcab41}createdOn',
interval: "DATE",
alias: "createdOn",
),
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.{f264cc1d-2ee5-49b0-8645-76b6d4195e8c}productId',
function: "COUNT",
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
)
},
pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 5000)
)
)

    Not sure If I have to make multiple queries and adjust my data using for loops and variables.

    I get this as a result

    [product:Chickpeas with spinach,
count:1,
createdOn:2/22/2024];

[product:Briam,
count:1,
createdOn:2/23/2024];

[product:Chickpeas with spinach,
count:2,
createdOn:2/23/2024]

  • 0
    Certified Lead Developer
     in reply to christodoulosp0001

    Can you update your post and use this?

  • 0
    Certified Senior Developer
     in reply to christodoulosp0001

    What looks wrong in the above output? Correct me if I am wrong,  As per your aggregation you have got the correct values. If you want to get the data based on the date selection have another filter in your queryRecordType which will have a variable for selected Date.

  • 0

    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
    )
  }
)

© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.