Make a slice with a row filter based on data in other table

AppSheet AppSheet Q&A
1

Hello,

I need to make a slice with a row filter based on data in other table.

Slice is for table Customer Products. I have two more tables - Orders and Order_Details.

When I add a new order, I get one row in Orders table and as many rows as many products I add in a basket in Order_Details table. These two tables are referenced and referenced rows have the same ID number (OrderID and OrderLink in Order_Details table).

Naff_0-1744702467194.png
Naff_0-1744702467194.png415×192 6.28 KB

Naff_2-1744702555813.png
Naff_2-1744702555813.png460×181 8.6 KB

There is one product that should stay alone in an order - let us say ProductX (OrderDetail_Product “4400d35c”). A user can add ProductX in a basket, but after that It must be impossible to add any other product from Customer Products table. Otherwise, if a use adds first any other product, he/she can’t add ProductX as a second or third product in a basket. For example in the second screenshot there are three rows with number 90d30a5c in Order_Link Column. There are three products added, however if the first product is 4400d35c, the other products should have not been
added.

So I want to omit some products from the slice for table Customer Products depending on the rows in Order Details table. Could you please give me an idea, I am stuck with this question for two days.

2

I am not sure that Slices are the solution here. But maybe I don’t have all the details about why you are choosing Slices?

As I understand it, you want to remove products from being chosen in the OrderDetails Form when either:

  1. They have already been added
  2. They are incompatible with an already listed Product

Do I have that right?

First, you need to have recorded somewhere, and in some way, which products are incompatible with each other.

To make sure you are aware, typically you would use the Valid_If property to control a List of available Products. To omit Products meeting the conditions above you would use an expression in the Valid_If Property similar to this:


SELECT(Products[Product ID], 
       OR(
          [Product] = [_THISROW].[Product],          
          AND(
              NOT(IN([Product], <<expression for Products in this Order>>)),
              NOT(IN([Product], [Incompatible Products)),
          )

       )
)

This is just an example and will need adjusted to your use case. The part, [Product] = [_THISROW].[Product], is to support future editing of a row - otherwise the Product would be removed by the NOT(IN()) expression.

I hope this helps!