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

[Harriswe]

Hi I have four tables:

1. Products
2. Features
3. ProductFeature * intermediate table to handle the many to many relationship*
4. 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.

[Bellave_Jayaram]

Need a little more context. And what would your app do with the Result? Is the result simply saved somewhere? Does it go to a different view?

[Harriswe]

Austin_Lambeth:

ISBLANK(List(Selected Features)-List(Product Features))

Hi [Bellave_Jayaram] thank you for your question. The result is to display the list of matching products that have the combination of features selected. Consider the following use-case:

A user is looking for a new Credit Card

Credit Card BLUE has the following features:

• Cash back
• Insurance on purchases
• Air miles
• No-fee for foreign exchange transactions

Credit card BLACK has the following features

• Cash back
• Insurance on purchases
• Air miles
• Free cash-withdraws aboard
• 8 weeks interest free period

A user wants to find a credit card that offers:

• Air miles
• Cash back
• 8 weeks interest free period

The result would display Credit Card BLACK.

The result is a list of ProductUUIDs representing products that match the criteria, captured in a virtual column in the Search table. It can also be used to filter the main Product view by using the list of ProductUUIDs identified. See screen shot below

[Austin]

Something along the lines of List(Selected Features)-List(Product Features)=0.
Having a hard time following the post but that will tell you if the Product has all of the selected features. You also don’t need to have selected every feature of a product for this to return true.

More accurate formula would be ISBLANK(List(Selected Features)-List(Product Features))*

[Harriswe]

Hi [Austin_Lambeth]

ISBLANK(List(Selected Features)-List(Product Features))*

An interesting idea to use List subtraction and not one I was aware was possible; thank you. I can see the simple logic, which is more straight forward than using a count of matching features.

Can this logic be applied as the condition in a Select statement or Filter?

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

Features is a virtual column in the ProductFeature table representing the combined list of features the Product has.

Many thanks once again. Looks promising…

[Austin]

Yes, that logic can be applied to select statements.
That formula looks correct to me, can’t speak to your names and such but the general logic looks correct to me.

[Marc_Dillon]

Disclaimer, I didn’t read any of this except for the thread title.

INTERSECT() = COUNT()

[Steve]

Harriswe:

An interesting idea to use List subtraction and not one I was aware was possible

List subtraction is described here:

help.appsheet.com

List Expressions and Aggregates

List expressions use and/or produce lists of values.

[Steve · Accepted solution]

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
)

[Harriswe]

Hi Steve,

I suspected the additional List() function may have been redundant. I am still finding my way around AppSheet.

Needless to say the select statement:

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

worked well in filtering products that have ALL the selected features not just one or two. Next task is combine the results of this query with other conditions using an intersection statement.

Many thanks to all who posted suggestions.