Hello everyone,
I'll preface this by saying I'm new to Appian, so don't assume I've tried the obvious...
I am picking up the administration of another person's app and using a text field to pull back information for a read-only grid. The expression is set to look at three different search fields in the interface and retrieve information based on one or more of these fields (three AND query filters), ignoring empty filters. The text queries are fine, but the numeric one is not and is configured as follows:
a!queryFilter( field: "number", operator: "=", value: ri!rinumber )
This filter either matches exactly and returns just one record, or returns ALL records as though no filter were applied. I've removed my other filters from the query just in case and got the same result. Any ideas what might be causing this? Is it possible (or even logical) to wrap just this one filter in an if() to return a distinct value or nothing at all?
Discussion posts and replies are publicly visible
If your query entity is set up to "ignore empty" as you've indicated, then if you pass in nothing for ri!rinumber, it will query as if you're not filtering on that field at all. If your use case requires you to return no results when ri!rinumber is blank, you might consider setting the query to not ignore empty filters, or alternatively (my preferred workaround) is wrapping the input in a null check and passing a value you know will be invalid (i.e. -1) - this is usually done from the parent, i.e. if my query is self-contained within an expression rule and i want to query a row by ID, but i want it to return blank (instead of trying to return every row) when the ID is blank - i'll replace blank values with -1 from there.
Thanks for the reply, Mike. To clarify, I'm really not upset about it returning everything when the field is blank - actually I prefer it. My issue is when I pass in a value that does not match - for instance, these are all 5-digit integers as defined in the DB, CDT, etc., but I enter three random letters - I need it to return an empty grid instead of all values. Hopefully that makes sense.
Ok, the auto-typecasting explanation makes sense. I'm not intending to pass character values, merely using it to demonstrate my issue. The same thing happens if I pass more or less than 5 integers or if I pass five integers which have no match in the database - it gives me all the results. If I pass five integers which do match a value in the database then my read-only grid filters just like it's supposed to and returns that one record.
Your a!queryFilter() should be working in the desired manner per its configuration above, if the query is not, say, calling multiple a!queryLogicalExpression() in a fashion that negates the one including ri!rinumber, or if the ri!rinumber value is not correctly populated in the query. It would help this thread if it is possible to share the entire a!queryEntity() and call to it.
Sure. So, the grid is built on a rule in the interface which is populated with the local variables we are searching for. That rule is as follows (redacted slightly for obvious reasons):
a!localVariables( /*project info*/ local!idQry: a!queryEntity( entity: cons![constant], query: a!query( selection: a!querySelection( columns: { a!queryColumn(field: "id"), a!queryColumn(field: "owner"), a!queryColumn(field: "project"), a!queryColumn(field: "projectno") } ), logicalExpression: a!queryLogicalExpression( ignoreFiltersWithEmptyValues: true(), operator: "AND", filters: { a!queryFilter( field: "project", operator: "includes", value: ri!project ), a!queryFilter( field: "projectno", operator: "=", value: ri!Number ), a!queryFilter( field: "owner", operator: "=", value: ri!owner ), } ), pagingInfo: a!pagingInfo(1, - 1) ) ), local!IDs: if( local!idQry.totalCount = 0, "", tointeger(local!idQry.data.id) ), local!launchQry: if( ri!noFiltersApplied, a!queryEntity( entity: cons![Constant2], query: a!query(pagingInfo: a!pagingInfo(1, - 1)) ), a!queryEntity( entity: cons![Constant2], query: a!query( logicalExpression: a!queryLogicalExpression( ignoreFiltersWithEmptyValues: true(), operator: "AND", filters: { a!queryFilter( field: "GoLive", operator: "=", value: tostring(ri!GoLive) ), a!queryFilter( field: "createdon", operator: ">", value: ri!date1 ), a!queryFilter( field: "createdon", operator: "<", value: ri!date2 ), if( isnull(ri!launchName), {}, a!queryFilter( field: "launchname", operator: "includes", value: ri!launchName ) ), if( or(isnull(ri!pm), length(ri!pm) = 0), {}, a!queryFilter( field: "PM", operator: "in", value: ri!pm ) ), if( isnull(ri!launchStatus), {}, a!queryFilter( field: "launchstatus", operator: "in", value: if( contains( ri!launchStatus, cons![Constant2][1] ), append(ri!launchStatus, ""), ri!launchStatus ) ) ), if( isnull(ri!createdOn), {}, a!queryFilter( field: "createdon", operator: "includes", value: tostring(ri!createdOn) ) ), /*project info*/ if( local!IDs = "", {}, a!queryFilter( field: "id", operator: "in", value: local!IDs ) ), } ), pagingInfo: a!pagingInfo(1, - 1) ), fetchTotalCount: true ) ), local!launchQry )
The problem filter is the second of the three near the top and if I strip the other two out entirely I get the same result. All rule inputs and local variables are populating correctly from what I can tell.
Thanks, guys.
Thanks, that helps! So in line 3 here, local!idQry is retrieving a list of values to be used in the final queryEntity. If your number does not match any project numbers, local!idQry becomes empty.
Line 39 local!IDs is then empty, as totalCount is 0 from the top query.
Line 44 begins the launchQry, which will be the returning data set. The if() on line 116 is bypassing the query for "id" whenever local!IDs is empty (which is occurring when the number search does not match), essentially including all rows at that point.
It appears here you will want to move this line 119 queryFilter outside of that if(), essentially always applying it. Comment out lines 116,117,118 and124 to test.
Note this method does have scalability concerns, if your data set for retrieved IDs grows into the thousands, retrieving a list of all the IDs to re-apply with IN will begin error once the ID list size becomes over 1 MB of data. The optimal solution would be to have a database view that joins the datasource from constant2 over to constant1 based on id, including those constant1 fields together to filter on within the same a!queryEntity() as local!launchQry.
Unfortunately that didn't fix it, but I think it's more due to my redactions and explanation than your assessment of the issue, so my apologies.
The ID field is a value we're not searching/filtering by, just retrieving to include in the final grid. Lines 11 and 23-26 are where I'm struggling.
In almost all cases I recommend that query entities be encapsulated in their own Expression Rule - generally speaking you usually only need one Expression Rule for any unique data store entity you want to query, configured in such a way that a subsequent user of the rule can pass one or more parameters (mapping to filter values, etc), and that additional filtering parameters can be added in the future without negatively impacting the existing uses. In this way you could simply test the query against different inputs in a rapid-fire way and see how it reacts. I suggest you try that with your query here, since it might let you more easily discover how something is behaving differently from your expectations.
100% agree with Mike on separating out these queryEntity() calls for easy of debugging, development, re-use, etc.
So, based on this code the local!IDs field is pulled from the initial query involved in lines 23-26 (and surrounding) and applied in the final query only when populated - I'm still targeting the same area, as the top query only appears to be used to drive the "id" filter in the final query. However in my previous post I had missed that the final query is utilizing ignoreFieldsWithEmptyValues: true(), which negates the queryField for "id" when it is empty. Along with the commenting out of the if() from above, try changing the ignoreFiltersWithEmptyValues setting on line 54 to false. Most of he other filters are manually bypassed with their own IFs, which you would also need to do for the ignore update with the GoLive and createdOn filters if they are not always applied.
Try this:
a!localVariables( /*project info*/ local!idQry: a!queryEntity( entity: cons![constant], query: a!query( selection: a!querySelection( columns: { a!queryColumn(field: "id"), a!queryColumn(field: "owner"), a!queryColumn(field: "project"), a!queryColumn(field: "projectno") } ), logicalExpression: a!queryLogicalExpression( ignoreFiltersWithEmptyValues: true(), operator: "AND", filters: { a!queryFilter( field: "project", operator: "includes", value: ri!project ), a!queryFilter( field: "projectno", operator: "=", value: ri!Number ), a!queryFilter( field: "owner", operator: "=", value: ri!owner ), } ), pagingInfo: a!pagingInfo(1, - 1) ) ), local!IDs: if( local!idQry.totalCount = 0, {}, {tointeger(local!idQry.data.id)} ), local!launchQry: if( ri!noFiltersApplied, a!queryEntity( entity: cons![Constant2], query: a!query(pagingInfo: a!pagingInfo(1, - 1)) ), a!queryEntity( entity: cons![Constant2], query: a!query( logicalExpression: a!queryLogicalExpression( ignoreFiltersWithEmptyValues: false(), operator: "AND", filters: { if( isnull(ri!GoLive), {}, a!queryFilter( field: "GoLive", operator: "=", value: tostring(ri!GoLive) ) ), if( isnull(ri!date1), {}, a!queryFilter( field: "createdon", operator: ">", value: ri!date1 ) ), if( isnull(ri!date2), {}, a!queryFilter( field: "createdon", operator: "<", value: ri!date2 ) ), if( isnull(ri!launchName), {}, a!queryFilter( field: "launchname", operator: "includes", value: ri!launchName ) ), if( or(isnull(ri!pm), length(ri!pm) = 0), {}, a!queryFilter( field: "PM", operator: "in", value: ri!pm ) ), if( isnull(ri!launchStatus), {}, a!queryFilter( field: "launchstatus", operator: "in", value: if( contains( ri!launchStatus, cons![Constant2][1] ), append(ri!launchStatus, ""), ri!launchStatus ) ) ), if( isnull(ri!createdOn), {}, a!queryFilter( field: "createdon", operator: "includes", value: tostring(ri!createdOn) ) ), /*project info*/ a!queryFilter( field: "id", operator: "in", value: local!IDs ) } ), pagingInfo: a!pagingInfo(1, - 1) ), fetchTotalCount: true ) ), local!launchQry )
No dice, unfortunately. It sounds like I may need to look at redesigning things as you both mentioned. Do you happen to have a link to something that would let me read up on your proposed design so I don't take up anymore time from your days?
Strange.. As Mike mentions, you would create 2 new expression rules, 1 for each a!queryEntity() that is called to set local!idQry and local!launchQry. Configure them with the necessary inputs and in your code above, call the new expression rules into the local variables instead of having the queryEntity calls within the parent rule itself. This will allow you to test each separately, creating different test cases to verify outputs without having to test them together in one expression as above.
Additionally the query for local!launchQry can likely be simplified by removing the if check for empty filters and allowing the ignoreFiltersWithEmptyValues to handle that for you. Such as:
Rule 1:
a!queryEntity( entity: cons![constant], query: a!query( selection: a!querySelection( columns: { a!queryColumn(field: "id"), a!queryColumn(field: "owner"), a!queryColumn(field: "project"), a!queryColumn(field: "projectno") } ), logicalExpression: a!queryLogicalExpression( ignoreFiltersWithEmptyValues: true(), operator: "AND", filters: { a!queryFilter( field: "project", operator: "includes", value: ri!project ), a!queryFilter( field: "projectno", operator: "=", value: ri!Number ), a!queryFilter( field: "owner", operator: "=", value: ri!owner ), } ), pagingInfo: a!pagingInfo(1, - 1) ) )
Rule 2, simplified by removing the if(): (note local!IDs is changed to a rule input)
a!queryEntity( entity: cons![Constant2], query: a!query( logicalExpression: a!queryLogicalExpression( ignoreFiltersWithEmptyValues: true(), operator: "AND", filters: { a!queryFilter( field: "GoLive", operator: "=", value: tostring(ri!GoLive) ), a!queryFilter( field: "createdon", operator: ">", value: ri!date1 ), a!queryFilter( field: "createdon", operator: "<", value: ri!date2 ), a!queryFilter( field: "launchname", operator: "includes", value: ri!launchName ), a!queryFilter( field: "PM", operator: "in", value: ri!pm ), a!queryFilter( field: "launchstatus", operator: "in", value: if( contains( ri!launchStatus, cons![Constant2][1] ), append(ri!launchStatus, ""), ri!launchStatus ) ), a!queryFilter( field: "createdon", operator: "includes", value: tostring(ri!createdOn) ), /*project info*/ a!queryFilter( field: "id", operator: "in", value: ri!IDs /* changed to rule input from local */ ) } ), pagingInfo: a!pagingInfo(1, -1) ), fetchTotalCount: true )
Feel free to give that a try and post your configuration back here if you continue to have issues!
Following up on this one, instead of trying to make an old form work I decided to re-create it instead using some of the best practices you both recommended above (namely letting Appian handle what it could without unnecessary code, including ignoreFieldsWithEmptyValues). There is still a bit of a disconnect with using the IN or INCLUDES operator with integer values, but I've gotten around this by using "=" and a length validation on the field. If it does not match then it returns an empty grid with a message indicating the value was not found. This is good enough for me.
Thank you both again for your time.