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

Question

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?

Stefan Helzle · Accepted Answer

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

Mike Schmitt · Answer

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

Peter Lewis · Answer

Stefan is correct that you should use index() or property() to prevent errors within your a!forEach() function. That being said, it also looks like you're using a different name in both places. In your query the alias is called "rating_average", but your error message above says you're using "average_rating" to index the results.

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

Top Replies