Solved: Assigning or pulling inventory numbers from table - Page 2

steve1123 · 01-25-2022 12:15 PM

Hello Appsheet experts,

I have a spreadsheet that keeps track of spare parts.

Parts are sometimes grouped together and share a common inventory number and Description. So the Description field can be thought of as a category.

When a user wishes to add a new part via the Parts Form, the first thing they fill out is the Description field. This field is a drop-down-style field and has all of the existing Description of parts, with the ability to add an entirely new Description. So, if the user wishes to add another subpart to an existing inventory, (which will have the same inventory #), the expression below pulls the existing Inv number from the spreadsheet. All is well.

When a user wishes to add an entirely new Description (and thereby require a new inventory number), I use MAX to find the highest inventory number already in use, and then add 1 to it.

These expressions seem to work fine individually, but when I try to combine them via an IF statement, I run into a problem. Whenever a user enters a new Description, it appears as though the expression which I use to pull an existing inventory number gets (understandably) confused and "errors out", and the inventory field isn't even displayed.

It's as though I need an IFERROR-style expression that says to continue to the MAX expression (and thereby assign a new inventory number) if the existing Description doesn't exist in the table.

Here's what I have so far.

IF(ANY(SELECT(Parts[Inv], ([Description] = [_THISROW].[Description]))) = "",
Max(Parts[Inv]) + 1,
ANY(SELECT(Parts[Inv], ([Description] = [_THISROW].[Description])))
)

Sample of the spreadsheet below:

Timestamp	Inv	Description	Details	Quantity

12/6/2021 14:35:42	1	MiniMax Tug	Chain	1
12/6/2021 14:36:42	1	MiniMax Tug	Master Link	1
12/6/2021 14:37:42	1	MiniMax Tug	Gear Clamps	2
12/7/2021 16:15:08	1	MiniMax Tug	Cotter Pins	8
12/8/2021 10:02:31	2	Throttle Quadrant Controls	Spare (Red) TOGA Button	1
12/16/2021 10:17:17	2	Throttle Quadrant Controls	Cowl Flaps Knob	1
12/31/2021 11:00:17	2	Throttle Quadrant Controls	Mixture Knob	1
12/31/2021 10:15:17	2	Throttle Quadrant Controls	Throttle Knob	1
12/31/2021 13:31:20	2	Throttle Quadrant Controls	Prop Knob	1
12/31/2021 13:33:03	2	Throttle Quadrant Controls	Loc-Tite	1
12/31/2021 13:34:43	3	Universal Fuel Gauge	Fuel Stick	1
12/31/2021 13:49:19	3	Universal Fuel Gauge	Fuel Stop	1
1/4/2022 12:35:19	3	Universal Fuel Gauge	Conversion Chart	1
1/4/2022 12:37:20	4	Switch Cover - Spare 3D Printed	(For Reference)	1
1/10/2022 11:10:00	5	Gear Actuator Service Kits	Nose Gear Kit	1
1/4/2022 12:38:03	5	Gear Actuator Service Kits	Main Gear Kits	2

Thanks in advance for any and all assistance!

AaronTerry

Here's the solution if anyone needs it in the future, I feel like it's worth noting that @steve1123 is using this setup to sort their inventory description by concatenating these inv numbers onto the beginning of the description and sorting them from lowest to highest.

IFS(
IN([_THISROW].[Description] , LIST(PARTS[Description])),
NUMBER(SELECT(Parts[Inv], IN([_THISROW].[Description],LIST([Description])),TRUE)
),
NOT(CONTAINS([_THISROW].[Description] , LIST(PARTS[Description]))),
Max(Parts[Inv])+1
)

For a clean user experience, I recommend changing the Details column to an Enum, deselecting Auto-complete other values and setting the Suggested values to:

SELECT(Parts[Details], ([_THISROW].[Description] = [Description]),TRUE)

View solution in original post