Restrict dropdown list values if value already selected

[dhdevans]

I have an app which is built with an inventory database and an orders database.

The background

Each row in the orders database can have up to four items attached to it. The four items exist in four separate columns. The origin of the items list for each column is a single column list in the inventory database.

At the moment an item can be selected more than once for different order rows. An example of the issue below where someone has assigned 'Apple1' to both orders:

Initial solutions

I initially set up an automation that would remove the value from the inventory database list when an order was saved with the item assigned. So, when someone assigns 'Apple1' to John the value in the inventory database is removed (not the entire record, just the value 'Apple1' in the inventory database which is being used as part of the VALID_IF statement in the column Item1/Item2/Item3/Item4).

Unforseen consequences

So, the automation removes the value 'Apple1' from the inventory database. Now we can't select 'Apple1' for anyone else because the value simply isn't in the list for the VALID_IF statements.

Unfortunately, orders aren't always fulfilled on first edit of the order record; sometimes they are partially fulfilled and held until all the items are available for assignment:

Roger requires 2 items, but we've only managed to assign 1 so far. The automations above remove 'Orange2' from the inventory database list. It is now no longer available for anyone to assign to an order. 

Let's say that Roger's second item is now available to assign so we reopen the order to assign 'Apple3'. However, on save the following happens:

The order cannot be saved because, rightly so, 'Orange2' no longer exists in the inventory database because the automation removed it when it was assigned.

I am really struggling to find a solution to this. I imagine it's quite simple, but at the minute i'm floundering.

Can anyone help with this?

[WillowMobileSys · Accepted solution]

Assuming you have all of your products listed in an Inventory table AND that you are using Ref type columns in the ITEM1, ITEM2, ITEM3 and ITEM4, you can insert this general expression into each ITEM columns Valid IF:

Inventory[Item ID] 
- LIST([ITEM1], [ITEM2], [ITEM3], [ITEM4] 
+ LIST([_THIS])

• Inventory[Item ID] - get a list of ALL the available times 
• - LIST([ITEM1], [ITEM2], [ITEM3], [ITEM4] - removes from the list above any items already chosen on this row
• + LIST([_THIS]) - reserved word for current column - adds back the item for THIS column, as the chosen value it IS a valid item in THIS column

You could simplify this expression by modifying it to fit each individual column.

************************

CAUTION:  AppSheet is a row-based, relational platform.  Meaning all functions are designed to operate over rows and the datasource is expected be in a relational table form.  Having columns like ITEM1, ITEM2, ITEM3, etc is a non-normal form for relational tables.  You will find it more difficult than necessary to create calculations (such as Total pricing for Orders) and to maintain the app - e.g. going from 4 items to 10 items - as you will need to add columns for each additional item AND update the calculations all over the app.

Instead, you want to consider an Orders design that has an Orders Parent table and an OrderDetails Child table that has an Order column as a Ref type to point back to the proper Order row - linking the two tables.

With the above suggested design, you can have any number of items in an Order and the expressions/calculations will operate correctly over that list of OrderDetail rows.

[dhdevans]

Thank you!

Inheriting systems is always a pain; i've never heard another SysAdmin compliment another SysAdmin's systems or work. In this case the system wasn't scoped properly before deployment and as such it is probably terminally flawed. However, solutions like the one you've presented allow me to keep it ticking over something like this:

That's you. That's your piece of code that is keeping the whole thing from falling over and preventing my warehouse guys from double-assigning items!