Hi,
Please could someone help me understand/change/tweak the select expression below…?
When I’m in the survey form and I select one item (i.e. CAT146) in the emum list [assets_to_abort] I get the correct result of:
136 , 137 , 138 , 139 , 140 , 141 , 142 , 143 , 144 , 153 , 155 , 291 , 292 , 293 , 294 , 298 , 299 , 300 , 301 , 302
However, when I’m in the survey form and I select more than one item (i.e. CAT146, CAT098) in the emum list [assets_to_abort] I get a list of rows that have both items in common in [survey_asset_list] but not the rows where either of the selected items appear on their own in [survey_asset_list]:
136 , 137 , 138 , 139 , 140 , 141 , 142 , 143 , 144 , 155 , 291 , 292 , 293 , 294 , 299 , 300 , 301 , 302
The list is missing: 151, 153, 296, 298
It’s as if the IN() expression is using the AND() parameter and the result I’m after would require OR().
[assets_to_abort] and [survey_asset_list] are both survey table EnumList type Ref columns pointing to the asset table.
Hope that makes sense.
Thanks in advance…
.
.
SELECT(
survey[_RowNumber],
AND(
[lcs] = [_THISROW].[lcs],
[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
IN([_THISROW].[assets_to_abort], [survey_asset_list])
)
)
.
.
Solved! Go to Solution.
Instead of this:
IN([_THISROW].[assets_to_abort], [survey_asset_list])
Try this:
ISNOTBLANK(
INTERSECT(
[_THISROW].[assets_to_abort],
[survey_asset_list]
)
)
Instead of this:
IN([_THISROW].[assets_to_abort], [survey_asset_list])
Try this:
ISNOTBLANK(
INTERSECT(
[_THISROW].[assets_to_abort],
[survey_asset_list]
)
)
Hi @Steve,
Thanks for your response…
It makes sense but for some reason it’s not producing a result in the form…?
SELECT(
survey[_RowNumber],
AND(
[lcs] = [_THISROW].[lcs],
[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
ISNOTBLANK(
INTERSECT(
[_THISROW].[assets_to_abort], [survey_asset_list]
))
)
)
Oops… Sorry @Steve… I was having a ‘senior moment’ earlier…
It works perfectly - exactly as suggested… Many thanks…
User | Count |
---|---|
59 | |
25 | |
13 | |
12 | |
6 |