Multiple Tables in Inventory App (Working With Three Tables)

AppSheet AppSheet Q&A
, , , , ,
1

I am working on a app for inventory , There are three Main Points , One shop Category ,One Item Category and one is items , How would i create a dependent dropdown with three Columns.

I Click Shop Category ,It should filter item Category, and on clicking item category it should show items as per selected item category.

I am able to cretate dependent dropdown for shop Category and item category , But Not able to make connection between Item Category and item list

Please help

2

This is referred to as Dependent Dropdowns. Please refer to the attached article for more details

Dependent Dropdowns

2 Likes
3

i want dependent dropdown to work in more than 3 tables for multiple columns

there are three tables :

itemlist with columnname →

ID Item Category Item

unitPrice with columnname →

ID item category Item unit price

finalList->

ID Item Category Item unit price

i want unit price to take item and itemcategory from itemlist

and i want finallist to filter ,

1.Click Item Category , Open Item list with desired item category

2.on clicking item opens unit related to it

3.on clicking unit opens the price of item of selected unit

4

Its not exactly clear how the 3 tables are linked and how you are expecting to use them in your Form

Dependent dropdown is still what you need to use. To hide columns until others are filled in you would use Show_If (see article below for more on that)

Conditionally show or hide column using Show_If

The idea is this…

NOTE: This is all an example of how this is typically done trying to adjust for your table structure. But since I do not know all of your data details, you may need to tweak these expression to fit.

I’ll assume you have a Form with the fields Category, Item, Unit, Price and Inventory Row. Item, Unit, Price and Inventory Row are hidden by placing a Show_If expression in each column, e.g.:


*Item Show_If =* **ISNOTBLANK([Category])**
*Unit Show_If =* **ISNOTBLANK([Item])**
*Price Show_If =* **ISNOTBLANK([Unit])**
*Inventory Row Show_if =* **ISNOTBLANK([Price])**

Then insert the dropdown expressions into the Valid_If as described in the Dependent Dropdown article above.

Category column might just be an enum column but you could also list all your Categories in a Category table. If you did use a table the Valid_If expression would be:

**Categories[Category Name]** -- *this is shorthand for "SELECT(Categories[Category Name], true)"*

Once a Category is selected, the Item field will show because of the above Show_If. The dropdown expression for its Valid _If would be something like:

**SELECT(ItemsList[ID], [Category] = [_THISROW].[Category])**

For the rest of the fields the Valid_If expression would be very similar:

*Unit Valid_If =* **SELECT(UnitPrice[ID], AND([Category] = [_THISROW].[Category],**
 **[Item] = [_THISROW].[Item]))**

*Price Valid_If =* **SELECT(UnitPrice[ID], AND([Category] = [_THISROW].[Category],**
 **[Item] = [_THISROW].[Item],**
 **[Unit] = [_THISROW].[Unit]))**

Then with all of the above you can select your filtered row:

*Inventory Row App Formula (or Initial Value) =*
**SELECT(FinalList[ID], AND([Category] = [_THISROW].[Category],**
 **[Item] = [_THISROW].[Item],**
 **[Unit] = [_THISROW].[Unit],**
 **[Price] = [_THISROW].[Price]))**
1 Like