Solved: MAX +1 - Page 2

msahines · 03-08-2023 11:32 AM

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?

Markus_Malessa

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.

View solution in original post