Help with expression

[Quality_Assuran]

Hi community,

Please help

I have an orders table with a Ref column for store name, columns for the orders and I want to have a column that shows which truck the order will go on. This should be predetermined by a Trucks table

I have a table with the names of the different truck routes and an enum list of the stores that go out on each truck route.

How do I check whether the store name on the orders table is in the list on the trucks table and then pull the name of the truck route into the orders table each time an order is placed?

I tried some SELECT and IN expressions but they didn't return any value in the Truck Route column on the Orders Table

[Joseph_Seddik]

It would be easier to show your tables and expressions so that we can correct them. 

[Quality_Assuran]

@Joseph_Seddik thank you for the response.

My app was quite convoluted so I was trying to avoid adding it.

I've built this basic app to demonstrate my problem.

I have tables for  "Stores","Orders","Routes","Items".

Each store is allocated to a particular truck route by means of an enumlist.

I would like to look at this list and if the store the user has selected in the column, [Order_store_link], (Refs to the "Stores" table) is in the list in the [Stores_go_on_route] list, then insert the value from the [Route] column on the "Routes" table into the column [Truck], on the "Orders" table.

How can I get this right?

[Joseph_Seddik · Accepted solution]

1. In the Orders table, your truckRoute column should be of type Ref pointing to the Routes table
2. In the Routes table, your Stores_go_on_route EnumList column should have base type Ref pointing towards the Stores table.
3. Providing that each store exists in only one route, then the expression for the truckRoute column should be:
     
   ANY(
     FILTER(Routes, IN([_ThisRow].[Order_Store_Link], [Stores_go_on_route]))
   )

If one store can exist within more than one route, then a better setup is recommended.  

[Quality_Assuran]

@Joseph_Seddik thanks for your help.

So based on your last statement, I am wondering what to do.

The schedule works on a two week basis.

Week 1 some of the stores will go on one route.

Week 2 they will go on another route.

And then week 1 starts again.

How would I account for this?

[Joseph_Seddik]

You are good to go. A store will only belong to one route at any given time. The route can change for the store, but it only be one route not more. If this is your case, then you don't have to do further modifications. 

---

Just FYI, when a route can have multiple stores and a store can have multiple routes, then you'd have a Many-to-Many relationship that you'll have to establish using another separate table. 

Read about this setup in this guide: Data: The Essentials - AppSheet Help