MAX +1

[msahines]

honestly i'm having troubles understanding the max expression and applying it to my project.

i have a table named: intake form number

column named: Item Number

fields in the column will either be labeled: F_000001 or I_000001 (F if it's a fixture and I if it's an item, as of now it auto puts in the F_ or I_ depending on a yes/no button of a field called Fixture?)

what i am looking for it to do is to be able to filter out the F_'s in the columns if fixture is selected and select the highest number for the F_'s and +1 to populate the field with the next sequential number.

can someone help please?

[Marc_Dillon]

https://community.appsheet.com/t/serial-numbers-if-you-must/19325

[msahines]

i appreciate that reply, but this isn't creating numbers for keys and the basic expression (MAX(MyTable[Serial]) + 1) isn't working at all (inputting my fields in that way)

[Markus_Malessa]

Conceptionally speaking the link that @Marc_Dillon posted would work for this, but given your specific setup it needs to be heavily altered to meet your exact needs. I presume that your table 'intake form number' probably does not have separate columns for the F/I and then the number? If it does, this becomes a bit easier, however if you only have the single column 'Item Number' that is a alphanumeric like you indicated this could become very difficult in trying to figure out by using combinations of text/number functions most likely with SELECT() or FILTER(). If you have separate columns for the F/I and then the number I can share an example.

[msahines]

i can definitely do them in separate columns if it makes things easier. i can use the already mentioned "Item Number" as the column for I_ and create a "Fixture Number" column for F_

[Markus_Malessa · Accepted solution]

I would probably recommend as follows:

Column "Item Type" with the following initial Value (I think you mentioned you had a yes/no column [Fixture] if it is a fixture):

IF([FIXTURE], "F", "I")

 Then column "Fixture Number" with initial Value:

IF(
ISNOTBLANK(FILTER(intake form number, [_THISROW].[Item Type] = [Item Type])),
MAX(SELECT(intake form number[Fixture Number], [_THISROW].[Item Type] = [Item Type])) + 1,
1
)

Then if you must you can still have a combined column "Item Number" with a Calculation:

[Item Type] & "_" & LEFT("000000", 6-LEN(TEXT([Fixture Number]))) & TEXT([Fixture Number])

You may also need to implement update behaviors to reset on edit. This is tested in a specific scenario and is working for my specific application, I would try at your own risk and you may find that this requires further tweeks to make it work for you.

[msahines]

i seem to be getting the same "max" error i was getting with the basic max expression. states:

"the inputs for function "max" should be a list of numeric values. maybe i'm getting this because of the f_ or i_ in the fields?

i created a column named Fixture Number and a column named Item Type

[msahines]

scratch the last comment, i must have misdone something with your solution as it's now working to display something. now to fine tune to get it to display what it want.

it's working in the column for the "Fixture Number" but not taking anything from the "Item Number". thinking though if i make it an IFS expression and copy most of the middle expression, but point it to the "Item Number" column it may do something good (or knowing my luck, will be broken lol). either way i appreciate your time!