error when trying to display 1 iteration of a querry thats its empty

a!queryEntity(
  entity: cons!AS_DataStore_Ratings_Pointer,
  query: a!query(
    aggregation: a!queryAggregation(
      aggregationColumns: {
        a!queryAggregationColumn(
          field: "productId",
          isGrouping: true
        ),
        a!queryAggregationColumn(
          field: "rating",
          alias: "rating_average",
          aggregationFunction: "AVG"
        )
      }
    ),
    logicalExpression: a!queryLogicalExpression(
      operator: "AND",
      filters: {
        a!queryFilter(
          field: "productId",
          operator: "=",
          value: ri!productId
        )
      },
      ignoreFiltersWithEmptyValues: true
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: 50
    )
  ),
  fetchTotalCount: false
).data

Hi!

so i have this query which return the average of a value from an ID, but not all items  have rating and when i. use it in a foreach i get this error:

Interface Definition: Expression evaluation error at function a!forEach [line 63]: Error in a!forEach() expression during iteration 3: Expression evaluation error: Invalid index: Cannot index property 'average_rating' of type String into type List of Variant

it says during iteration 3 because its the first time there is no rating on a specific item. Any help?

  Discussion posts and replies are publicly visible

Parents
  • 0
    Certified Lead Developer

    Use the index() or property() function to access individual fields instead of the dot-notation.

  • in the query expression or in the interface? because i've tried both funtion on the interface and it didnt work

  • a!localVariables(
      local!filtered: rule!AS_Get_By_Category(ri!filter),
      local!visivel,
      local!id,
      local!querycomments: rule!AS_Get_Comments(local!id),
      local!info: rule!AS_Get_By_Id(local!id),
      local!rating: 0,
      local!totalstars: 10,
      local!score: 5,
      local!comment,
      local!stored:{productId: local!info.id, rating: local!rating},
      local!comments:{productId: local!info.id,
      comments: local!comment,
      user: loggedInUser(),
      date: today()},
      a!headerContentLayout(
        header: {
          a!billboardLayout(
            backgroundMedia: a!documentImage(
              document: cons!BILLBOARD
            ),
            backgroundColor: "#f0f0f0",
            marginBelow: "NONE",
            overlay: a!barOverlay(
              contents: {
                a!richTextDisplayField(
                  label: "Appian Store",
                  labelPosition: "COLLAPSED",
                  value: {
                    a!richTextHeader(
                      text: {
                        "Appian Store"
                      },
                      size: "LARGE"
                    )
                  },
                  align: "CENTER"
                )
              }
            )
          )
        },
        contents: {
          a!sectionLayout(
            label: "",
            labelSize: "MEDIUM",
            contents: {
              a!columnsLayout(
                columns: {
                  a!columnLayout(
                    contents: {
                      a!dropdownField(
                        label: "Category",
                        labelPosition: "ABOVE",
                        placeholder: "--- What are you looking for? ---",
                        choiceLabels: cons!AS_categories,
                        choiceValues: cons!AS_categories,
                        value: ri!filter,
                        saveInto: ri!filter,
                        searchDisplay: "AUTO",
                        validations: {}
                      ),
                      a!forEach(
                        items: local!filtered,
                        expression: 
                        a!localVariables(
                          local!queryrating: rule!AS_Get_Rating(fv!item.id),
                          local!ratestar: local!queryrating.rating_average,
                        a!boxLayout(
                            label: "",
                            contents: {
                              a!columnsLayout(
                                columns: {
                                  a!columnLayout(
                                    contents: {
                                      a!imageField(
                                        label: "Image",
                                        labelPosition: "COLLAPSED",
                                        images: {
                                          a!documentImage(
                                            document: fv!item.imageId
                                            )
                                          
                                        },
                                        size: "MEDIUM",
                                        isThumbnail: false,
                                        style: "STANDARD"
                                      )
                                    }
                                  ),
                                  a!columnLayout(
                                    contents: {
                                      a!sideBySideLayout(
                                        items: {
                                          a!sideBySideItem(
                                            item: a!richTextDisplayField(
                                              labelPosition: "COLLAPSED",
                                              value: {
                                                a!richTextItem(
                                                  text: {
                                                    fv!item.name
                                                    },
                                                  size: "MEDIUM_PLUS"
                                                )
                                              }
                                            )
                                          ),
                                          a!sideBySideItem(
                                            item: a!buttonArrayLayout(
                                              buttons: {
                                                a!buttonWidget(
                                                  label: "details",
                                                  icon: "info",
                                                  saveInto: {a!save(local!visivel,true()),
                                                  a!save(local!id,fv!item.id),
                                                  a!forEach(
                                                    items: fv!item.imageId,
                                                    expression: 
                                                  
                                                  a!save(ri!imagesdetail,append(ri!imagesdetail,fv!item))
                                                  )},
                                                  size: "LARGE",
                                                  style: "NORMAL"
                                                )
                                              },
                                              align: "END"
                                            )
                                          )
                                        }
                                      ),
                                      a!sideBySideLayout(
                                        items: {
                                          a!sideBySideItem(
                                            item: a!richTextDisplayField(
                                              label: "Price",
                                              labelPosition: "ABOVE",
                                              value: {
                                                a!richTextItem(
                                                  text: {"$" &fv!item.price}
                                                )
                                              }
                                            )
                                          ),
                                          a!sideBySideItem(
                                            item: a!richTextDisplayField(
                                              value: {
                                                a!forEach(
                                                  items: enumerate(local!totalstars) + 1,
                                                  expression: a!richTextIcon(
                                                    icon: if(
                                                      fv!index <= todecimal(local!ratestar),
                                                      "star",
                                                      if(
                                                        fv!index - 1 < todecimal(local!ratestar),
                                                        "star-half-o",
                                                        "star-o"
                                                      )
                                                    ),
                                                    color: "ACCENT"
                                                  )
                                                )
                                              }
                                            )
                                          ),
                                          a!sideBySideItem(
                                            item: a!buttonArrayLayout(
                                              buttons: {
                                                a!buttonWidget(
                                                  label: "add to cart",
                                                  icon: "shopping-cart",
                                                  size: "LARGE",
                                                  width: "MINIMIZE",
                                                  style: "PRIMARY"
                                                )
                                              },
                                              align: "END"
                                            )
                                          )
                                        }
                                      )
                                    },
                                    width: "WIDE"
                                  )
                                }
                              )
                            },
                            style: "#f3f3f3",
                            marginBelow: "STANDARD"
                            
                          ))
                      )
                    }
                  )
                }
              )
            },showWhen: isnull(local!visivel)
          ),
          a!richTextDisplayField(
            labelPosition: "COLLAPSED",
            value: {
              a!richTextItem(
                text: {
                  a!richTextIcon(
                    icon: "long-arrow-left"
                  ),
                  " Back to the store"
                },
                link: a!dynamicLink(
                  value: null,
                  saveInto: {a!save(local!visivel,null()),
                  a!save(local!id,null()),
                  a!save(ri!imagesdetail,null())}
                ),
                linkstyle: "STANDALONE"
              )
            },
            showWhen: not(isnull(local!visivel))
          ),
          a!sideBySideLayout(
            items: {
              a!sideBySideItem(
                item: a!richTextDisplayField(
                  labelPosition: "COLLAPSED",
                  value: {
                    a!forEach(
                      items: enumerate(local!totalstars) + 1,
                      expression: a!richTextIcon(
                        icon: if(
                          fv!index <= local!score,
                          "star",
                          if(
                            fv!index - 1 < local!score,
                            "star-half-o",
                            "star-o"
                          )
                        ),
                        color: "ACCENT"
                      )
                    )
                  }, showWhen: not(isnull(local!visivel))
                )
              ),
              a!sideBySideItem(
                item: a!buttonArrayLayout(
                  buttons: {
                    a!buttonWidget(
                      label: "ADD to cart",
                      icon: "shopping-cart",
                      style: "PRIMARY"
                    )
                  },
                  align: "END"
                )
              )
            }, showWhen: not(isnull(local!visivel))
          ),
          a!sectionLayout(
            label: local!info.name,
            labelSize: "LARGE",
            labelColor: "#434343",
            contents: {
              a!columnsLayout(
                columns: {
                  a!columnLayout(
                    contents: {
                      a!richTextDisplayField(
                        label: "About the Product:",
                        labelPosition: "ABOVE",
                        value: local!info.description
                      )
                    }
                  ),
                  a!columnLayout(
                    contents: {
                      a!sideBySideLayout(
                        items: {
                          a!sideBySideItem(
                            item: a!richTextDisplayField(
                              label: "Price",
                              labelPosition: "ABOVE",
                              value: "$" & local!info.price
                            )
                          ),
                          a!sideBySideItem(
                            item: a!richTextDisplayField(
                              label: "Stock",
                              labelPosition: "ABOVE",
                              value: local!info.quantity & " Units"
                            )
                          )
                        }
                      )
                    }
                  ),
                  a!columnLayout(
                    contents: {
                      a!imageField(
                        label: "",
                        labelPosition: "ABOVE",
                        images: {
                          a!forEach(
                            items: ri!imagesdetail,
                            expression: a!documentImage(
                              document: fv!item
                            )
                          )
                        },
                        size: "GALLERY",
                        isThumbnail: true,
                        style: "STANDARD"
                      )
                    }
                  )
                }
              )
            },
            showWhen: not(isnull(local!visivel))
          ),
          a!sideBySideLayout(
            items: {
              a!sideBySideItem(
                item: a!richTextDisplayField(
                  label: "Rate the Product",
                  labelPosition: "ABOVE",
                  value: {
                    a!forEach(
                      items: enumerate(local!totalStars) + 1,
                      expression: {
                        a!richTextIcon(
                          icon: if(
                            fv!index <= local!rating,
                            "star",
                            "star-o"
                          ),
                          link: a!dynamicLink(
                            value: if(local!rating=fv!index, 0, fv!index),
                            saveInto: local!rating
                          ),
                          linkstyle: "STANDALONE",
                          color: "ACCENT"
                        )
                      }
                    )
                  }
                )
              ),
              a!sideBySideItem(
                item: a!paragraphField(
                  label: "Comment",
                  labelPosition: "ABOVE",
                  value: local!comments.comment,
                  saveInto: {local!comment},
                  refreshAfter: "UNFOCUS",
                  validations: {}
                )
              ),
              a!sideBySideItem(
                item: a!richTextDisplayField(
                  labelPosition: "COLLAPSED",
                  value: {
                    char(10),
                    char(10),
                    char(10),
                    char(10),
                    char(10),
                    a!richTextItem(
                      text: {
                        "Comments:"
                      },
                      size: "MEDIUM_PLUS",
                      style: {
                        "STRONG"
                      }
                    )
                  }
                )
              )
            },showWhen: not(isnull(local!visivel))
          ),
          a!sideBySideLayout(
            items: {
              a!sideBySideItem(
                item: a!buttonArrayLayout(
                  buttons: {
                    a!buttonWidget(
                      label: "Rate",
                      icon: "star",
                      saveInto: a!writeToDataStoreEntity(cons!AS_DataStore_Ratings_Pointer,local!stored),
                      style: "NORMAL"
                    )
                  },
                  align: "START"
                )
              ),
              a!sideBySideItem(
                item: a!buttonArrayLayout(
                  buttons: {
                    a!buttonWidget(
                      label: "Add comment",
                      icon: "feather-alt",
                      saveInto: a!writeToDataStoreEntity(cons!AS_DataStore_Comments_Pointer,local!comments),
                      style: "NORMAL"
                    )
                  },
                  align: "START"
                )
              ),
              a!sideBySideItem(
                item: a!richTextDisplayField(
                  labelPosition: "COLLAPSED",
                  value: {
                    a!forEach(
                      items: local!querycomments,
                      expression: 
                        a!richTextItem(
                          text: {fv!item.user &
                          "     " &
                          fv!item.date &
                          char(10) & "____________________________________________________________"& 
                            char(10) &
                          fv!item.comments & char(10) & char(10) & char(10)}
                    ))
                  }
                )
              )
            },showWhen: not(isnull(local!visivel))
          )
        }
      )
    )

    foreach starts in line 63

  • then i use the variable in line 150ish to implement a star rating system

  • 0
    Certified Lead Developer
    in reply to pedrob0002

    Line 68 will fail if there is no rating for that given product. Why not modify the get_rating expression in a way it always return a valid number? And just a number.

  • yes the problem i'm having is that line probably i just dont know how to fixe it tried a lot of stuff already.

    how would you implement it? if thats no trouble.

  • +1
    Certified Lead Developer
    in reply to pedrob0002

    Quick draft. Did not test. Should return a valid integer in all cases.

    tointeger(
    index(
    index(
    a!queryEntity(
      entity: cons!AS_DataStore_Ratings_Pointer,
      query: a!query(
        aggregation: a!queryAggregation(
          aggregationColumns: {
            a!queryAggregationColumn(
              field: "productId",
              isGrouping: true
            ),
            a!queryAggregationColumn(
              field: "rating",
              alias: "rating_average",
              aggregationFunction: "AVG"
            )
          }
        ),
        logicalExpression: a!queryLogicalExpression(
          operator: "AND",
          filters: {
            a!queryFilter(
              field: "productId",
              operator: "=",
              value: ri!productId
            )
          },
          ignoreFiltersWithEmptyValues: true
        ),
        pagingInfo: a!pagingInfo(
          startIndex: 1,
          batchSize: 50
        )
      ),
      fetchTotalCount: false
    ).data,
    1,
    null
    ),
    rating_average,
    0
    )
    )

  • will test it out, is it with the two index functions? looks weird 

  • it works thank you so much,

    but could you explain the meaning of 2 indexes? just so I understand

  • 0
    Certified Lead Developer
    in reply to pedrob0002

    The inner index picks the first item in the list of returned rows. The outer index picks the value of the field and in case the is nothing a zero.

    All my expressions returning data, always return the same data type. Even if it is a casted null value. This makes it so much easier to work with them.

  • +1
    Certified Lead Developer
    in reply to Stefan Helzle

    Shouldn't line 41 be in quotes, i.e. "rating_average", to provide the property string?  I don't know what it would do if not encapsulated like that, I would assume it would give an error. 

    Also IMHO this should be a property() call instead of nested index(); this scenario is a clear example of why these functions should be used for their "namely" purposes, even if they're identical on the back-end.

    tointeger(
      property(
        index(
          a!queryEntity(
            entity: cons!AS_DataStore_Ratings_Pointer,
            query: a!query(
              aggregation: a!queryAggregation(
                aggregationColumns: {
                  a!queryAggregationColumn(
                    field: "productId",
                    isGrouping: true
                  ),
                  a!queryAggregationColumn(
                    field: "rating",
                    alias: "rating_average",
                    aggregationFunction: "AVG"
                  )
                }
              ),
              logicalExpression: a!queryLogicalExpression(
                operator: "AND",
                filters: {
                  a!queryFilter(
                    field: "productId",
                    operator: "=",
                    value: ri!productId
                  )
                },
                ignoreFiltersWithEmptyValues: true
              ),
              pagingInfo: a!pagingInfo(
                startIndex: 1,
                batchSize: 50
              )
            ),
            fetchTotalCount: false
          ).data,
          1,
          null
        ),
        "rating_average",
        0
      )
    )

Reply
  • +1
    Certified Lead Developer
    in reply to Stefan Helzle

    Shouldn't line 41 be in quotes, i.e. "rating_average", to provide the property string?  I don't know what it would do if not encapsulated like that, I would assume it would give an error. 

    Also IMHO this should be a property() call instead of nested index(); this scenario is a clear example of why these functions should be used for their "namely" purposes, even if they're identical on the back-end.

    tointeger(
      property(
        index(
          a!queryEntity(
            entity: cons!AS_DataStore_Ratings_Pointer,
            query: a!query(
              aggregation: a!queryAggregation(
                aggregationColumns: {
                  a!queryAggregationColumn(
                    field: "productId",
                    isGrouping: true
                  ),
                  a!queryAggregationColumn(
                    field: "rating",
                    alias: "rating_average",
                    aggregationFunction: "AVG"
                  )
                }
              ),
              logicalExpression: a!queryLogicalExpression(
                operator: "AND",
                filters: {
                  a!queryFilter(
                    field: "productId",
                    operator: "=",
                    value: ri!productId
                  )
                },
                ignoreFiltersWithEmptyValues: true
              ),
              pagingInfo: a!pagingInfo(
                startIndex: 1,
                batchSize: 50
              )
            ),
            fetchTotalCount: false
          ).data,
          1,
          null
        ),
        "rating_average",
        0
      )
    )

Children
No Data