IN operator not working

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

This is very straight issue. I am passing one view output in another view query filter. In view a we have fields data type is string which we are passing to another view and trying to filter that. But getting above error which is very weird .

But in your response you are converting to toInteger() but we have field string type not integer type like "1027A"

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.

Thanks for your analysis. But please reconsider this without join array. As i have used as an work around to fix this. But it didn't work. I have mentioned in my chat also to consider this without joinarray. Why its giving problem. I am just trying to pass one view field to another query filter in filterQuery().

Ideally it should work but its throwing an error. Youc an consider this as string only.

New possibility to explore: What if the IN operator is failing because the filter on local!dataF is only returning one value?  Most things that are capable of returning multiple values, you'll find, return one value as just that value, rather than as a one-lengh list containing that value.  A one-length list, IN could find, but not a single value.  Maybe that's why it fails.

You could simply have the rule output local!dataF to see if it is one value or no values. IN would also have problems trying to search through 0 values, which Appian consistently casts as type "List of Variant".  The test output should also show the type of local!dataF which could be illuminating on the problem if it's an implicit casting error. 

If it is only one value or none, the solution is to explicitly cast local!dataF as a list of the type you want, if I understand correctly a list of strings.

Yes it could be. Anyways thanks for your response. I will try your approach also.

Yes it could be. Anyways thanks for your response. I will try your approach also.