Dynamic filter with Related Table Condition

AppSheet AppSheet Q&A
, ,
1

Hello everyone!! Hope u are all good.

I developed an app for a friend, and i created a Dynamic Filter Dashboard based on some links here. But now he asked me to add another condition that would filter a related Table and i need help on two things:

This app has a Order Table, a Product Table and a OrderProduct Table (to deal with the many-to-many relationship). My filter slice setting is at Order’s Table, that has a Related OrderProduct (App Formula: REF_ROWS(“OrderProduct” , “Order ID”)).

  • Changing the row condition formula to include this Related Product filter:

    The way i tried to manage it was to use the following row filter condition inside the AND([ALL FILTER FORMULAS]

    IF(
    ISBLANK(DashboardFilter[Product ID]),
    true,
    IN([Related OrderProduct][Product ID],DashboardFilter[Product ID])
    )

The problem is that this IN() formula is not working for multiple values. Since the [Product ID] at DashboardFilter is a Enumlist ref I need it to work based on multiple values and not single one.

  • Show only the related Products matching the filter condition:

Since the slice is based on Order Table the same is for the card view. And at the “image” card setting i set to show [Related OrderProduct] as the image bellow. So it will show the order that matches the filter condition but will not restrict the products view at the [Related OrderProduct] card view. Is it possible to in addition to show the orders based on the filter, showing the products too?

Thales_Taranto_0-1657302434192.png
Thales_Taranto_0-1657302434192.png326×566 18 KB

1 Like
2

Hi, good question.

I’m not sure that my solution is right, but I use CONTAINS() to find something in Enumlist, because Enumlist it’s a text separated by a comma.

https://support.google.com/appsheet/answer/10107955?hl=en

1 Like
3

Thank for your suggestion but it didn’t worked also. It is returning only the columns that matches all the selected itens. For exemple:

Order 001 - ITEM A, ITEM B, ITEM C

Order 002 - ITEM A, ITEM B

Order 003 - ITEM C

If my enumlist has the ITEM A and the ITEM B selected it should return the orders 001 and 002.

With your formula it will return only the order 002. I think the way CONTAINS work is different from what you said, the list of itens will be a single text like “ITEM A, ITEM B” and will result true whenever the column is filled exactly with this text.

4

CONTAINS(“abc”, “bc”) returns TRUE

1 Like
5

Yeah it does, but CONTAINS(“b”, “bc”) return FALSE, and in my case it should return TRUE.

What this case means, supose the user is searching for item b or item c on the filter, and this order only has the item b. As item b is on the criteria, this row should appear on the filter and it isn’t. I don’t know if i made it clear enough.

6 
OR(
  ISBLANK(DashboardFilter[Product ID]),
  ISNOTBLANK(
    INTERSECT(
      DashboardFilter[Product ID]),
      [Related OrderProduct][Product ID]
    )
  )
)
3 Likes
7

Thanks Steve. You’re always saving us!!! By the way, i managed to get this product list inside of the Product table, since that is a intermediate Table that relates Products and Orders.

I just did the [Related IntermediateTable][Column I wanted] in Product table, and i got the result almost the way my friend expected. I’ll give a try to your suggestion.