IN operator not working

Question

-------Updated below code-------------- 
 Hi , 
 local!data: a!queryEntity( entity: cons!TEST , query: a!query( ) ), local!dataF:index(local!data.data,wherecontains((9555),year(local!data.data.date))), local!dataH: a!queryEntity( entity: cons!HELLO, query: a!query( logicalExpression: a!queryLogicalExpression( operator: "AND", filters: { if(rule!APN_isBlank(local!dataF.name), {}, a!queryFilter( field: "name", operator: "in", value: local!dataF.name ) )}))) 
 
 By running above code i am getting below error. 
 
 Expression evaluation error at function a!queryEntity [line 80]: Cannot apply operator [IN] to field [componentId] when comparing to value [TypedValue[it=3,v=1027A]]. 
 
 Why IN operator i not working inside query filter. Outside its printing correct value. What exactly its expecting ? 
 Can any help me on this ?

Dave Lewis · Answer

You are trying to find whether or not a specific string, such as your example " 1026A", is located within a list of strings, local!test. There's one fundamental problem with this. You used the joinarray function on local!test. It is no longer many strings from which you can find "1026A"; it's one long, massive string containing all the search results concatenated together with commas. It's not a list, but a single entity, which "IN" fails to recognize because it's expecting a list. 
 Don't use the joinarray function here. Instead, try a!flatten, which should remove all nesting and leave you with a nice, clean list of strings. 
 Try commenting out the query and having the whole rule just return local!test instead until it's squared away, then uncomment and try polishing the query again. It's possible that the first query isn't returning the same type as the second one, or that neither one are returning the type you expect.

Ravi Roshan · Answer

with( local!dataOne: a!queryEntity( entity: cons!TEST, query: a!query( a!paginInfo( 1, - 1 ) ) ).data, local!dataTwo: index( local!dataOne, wherecontains( tointeger( 9555 ), tointeger( year( index( local!dataOne, date ) ) ) ), {} ), local!test: joinarray( index( local!dataTwo, "componentId", "" ), "," ), local!dataFinal: if( rule!APN_isEmpty( local!dataTwo ), null, a!queryEntity( entity: cons!HELLO, query: a!query( logicalExpression: a!queryLogicalExpression( operator: "AND", filters: { if( rule!APN_isBlank( local!dataTwo.id ), {}, a!queryFilter( field: "id", operator: "in", value: { tointeger( local!test ) } ) ) } ) ) ) ) )

Dave Lewis · Answer

You've got strings, but you're using joinArray to convert them into a long string. You can't index a singular long string, that's why the IN operator threw an error. Use a!flatten, which removes nesting but keeps the highest level array, then you'll have a list of strings, and the IN operator should work fine. 
 Another possibility to look into is whether or not you're using an automatic sequencer. If you are, then the datastore might be misinterpreting your data and adding an integer id column on your behalf. You would to well to rewrite the rule to output the results of the queries one at a time so you can see what it's returning. Return local!test by itself as the result of the rule as a test to see what you're passing into the query. I think the problem may become clear once you have eyes on what each part is really doing and really giving to the other parts.

IN operator not working

Top Replies