Solved: How can you do the equivalent of: has ALL items (i... - Page 2

Harriswe · 04-24-2020 10:14 AM

Hi I have four tables:

Products
Features
ProductFeature * intermediate table to handle the many to many relationship*
Search captures the user selection of features

I am looking for a way where I can search for a product that has ALL the features selected not just one or some. The IN () function is in fact an OR function where it returns products that have one or more of the features in the list.

Normally: when looking for example: 3 selected features.
Sort(Select(ProductFeature[Product], (IN( [FeatureUUID], [_THISROW].[Features])) ,TRUE))

returns Result = ETH-USD, ETH-USD Swap
which is incorrect for my purpose as ETH-USD Swap is missing one of the features.

The following select statement :
Result = Sort(Select(ProductFeature[Product], (IN( [FeatureUUID], [_THISROW].[Features])) ,FALSE))

returns the following: when looking for 3 selected features.
ETH-USD , ETH-USD , ETH-USD , ETH-USD Swap , ETH-USD Swap

Only one product in the results returned has all three features, because it appears 3 times. Is there a way to write the select statement to return only products that when counted appears 3 times, matching the number of features selected, inferring that the product has all 3 features?

So the end result would be Result = ETH-USD.

Steve

Your expression reformatted for clarity:

SELECT(
  ProductFeature[ProductName],
  (
    ISBLANK(
      LIST([_THISROW].[SelectedFeatures])
      - LIST(ProductFeature[Features])
    )
    = TRUE
  ),
  TRUE
)

Using LIST() in LIST((ProductFeature[Features])) is redundant: ProductFeature[Features] itself produces a list, so wrapping it in LIST() creates a list-of-lists, which probably isn’t what you want. You can probably omit LIST().

For that matter, I’d guess [_THISROW].[SelectedFeatures] is an EnumList column, which is already a list, so wrapping it in LIST() has the same problem as above.

Getting back to ProductFeature[Features]: your SELECT() expression is explicitly searching the ProductFeature table (SELECT(ProductFeature[ProductName], ...). Referencing ProductFeature[Features] creates a list of all Features column values from all rows of the ProductFeature table, which probably isn’t what you want. Instead, it sounds like you want to use the Features column value from the current row SELECT() is examining. To get that, omit the table name and leave just the column value reference: [Features].

= TRUE is redundant/unnecessary.

Revised:

SELECT(
  ProductFeature[ProductName],
  ISBLANK(
    [_THISROW].[SelectedFeatures]
    - [Features]
  ),
  TRUE
)

View solution in original post

How can you do the equivalent of: has ALL items (in list)