Using IN() to find list of rows based on selection in an EnumList (Ref)

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… :slight_smile:
.
.

SELECT(
	survey[_RowNumber],
		AND(
		[lcs] = [_THISROW].[lcs],
		[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
		IN([_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]
  )
)
3 Likes

Hi @Steve,

Thanks for your response… :slight_smile:

It makes sense but for some reason it’s not producing a result in the form…? :thinking:

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… :flushed:

It works perfectly - exactly as suggested… Many thanks… :wink:

:ok_hand:

1 Like