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
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
1s, its a pretty long expression
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
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.
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
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.
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 ) )