Assigning or pulling inventory numbers from table

[steve1123]

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]

Have you tried using Suggested Values and the expression List([Description])

- Column type: Enum 
- Allow other values
- Base type: text

- Input mode: Dropdown

- Suggested values: LIST([Description])

It sounds like you may have overcomplicated it but I could be missing something in your explanation that's crucial to your process. Save your formula somewhere before you try this and let me know if that's what you're looking for!

https://help.appsheet.com/en/articles/2423938-suggested-values-for-a-column

[steve1123]

Hi Aaron,

Thank-you kindly for your quick reply!

Apologies, I should have clarified. The Description drop-down field is working just fine. It's the Inventory number that I am having trouble auto-calculating. If a user is adding a sub-part to an existing Description, the inventory number should auto-populate with the existing Inv number. If it's an entirely new Description, then the Inv field needs to auto-populate with the highest existing Inv number +1.

Sorry if I made it more confusing, lol.

[AaronTerry]

Ahh, okay so you just want to auto-fill the Inv column based on the previous and highest entry.

I notice that you have multiple rows with an Inv column of 2, wouldn't this be impossible if you set it to always auto-populate the highest number?

[steve1123]

Almost!

Some parts share the same Inv number because they're part of the same group (ie. Description) of parts. Most of these parts are small enough to put in an envelope or box, with the Inv # written on it.

There are 2 possible scenarios:

1) The user wishes to add a sub-part to an already existing Description (ie. same Description, different Details). The Inv # should auto-populate with the existing Inv number for the Description.
Example: Someone buys a battery for the MiniMax Tug.
The user opens the Parts Form, taps on Description to get the drop-down, selects MiniMax Tug, enters "Battery" in the details, and saves.
In this case, the Inv should auto-populate with the number 1.
When I use the code below (by itself) it works perfectly.
ANY(SELECT(Parts[Inv], ([Description] = [_THISROW].[Description])))

2) The user wishes to enter an entirely new Description (ie. starting a new envelope or box, a new group of parts).
The Inv should find the highest Inv number, and +1 to it.
Example: Someone buys new spare switches for the instrument panel.
The user opens the Parts Form, taps on Description, and this time types in "Instrument Panel" in the Description field, types "Switches" in the Details, and saves.
In this case, the Inv should auto-populate with the number 6, the next number in sequence.
When I use the code below (by itself) it works perfectly.
Max(Parts[Inv]) + 1

I'm bad at making a short story long (sorry!) lol. Hope this helps to clarify it a bit?

[AaronTerry]

Alternatively, you can just use a simple counter like this one and then use a button to create a pdf or inventory table row of the current inventory.

I've loaded your current info into this template, if it works then copy it to enter in your info.
https://www.appsheet.com/start/471abf7f-9ae1-490b-899c-ac9359ecbc4b

[JPAlpano]

Trying to create an auto increment field in Appsheet is very tricky and is not recommended, especially if your app will use that as an index and if your app will be accessed by multiple users simultaneously.

Remember that appsheet loads the table in a cache.  Therefore, the Max() fpr a specific user may no longer be updated if another user already added an entry before the current user was able to sync.  

You may also end up having same id for different submissions.

[steve1123]

Hmmm, fair point. I'm using the Timestamp as the key. The Inv number is more of just a reference for the parts, so I don't believe Appsheet would use it as an index. But I think I see what you're saying. There's only 5 of us, so hopefully we wouldn't run into too many issues?

[AaronTerry · Accepted solution]

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)

[steve1123]

Yes!! This is it! Thank-you very much Aaron! Yes indeed, that's exactly what it's doing. I have made those modifications and it's working great. Thanks again!