How can I use MAXROW to achieve this? I am st...

[Russey_Novell]

How can I use MAXROW to achieve this? I am struggling with the expression.

Scenario: I have 3 columns, Category, Code, and ServiceNumber. All these columns are in one table name called “Event”

I want the ServiceNumber finds the last entry increment by 1 based on Category + Code.

Example: User select [Category] + [Code] = last entry of [ServiceNumber]

Let’s say the user selected: Cat1 + Cod01 and I want the service number find me the last entry of the [ServiceNumber] based on Cat1 + Cod01.

In this case, let’s say Cat1 + Cod01 = 99. Since the 99 is the last entry in the record, I want the 99 + 1 as a new input so when the user click Save it will save the data as Cat1 + Cod01 = 100.

This basically let the user know that the new entry of Cat1 + Cod01 is 100th.

Can someone please come up with an expression for me to try? Thanks.

[Aleksi_Alkio]

Have you thought what happens if two users are doing the same at the same time? I mean if and when that happens, the value will be the same.

[Russey_Novell]

@Aleksi_Alkio I have concatenated these 3 columns data as one key call EventKey so no duplication is allowed as far as I tested it before. One two users are allowed to use this part of the app. The odd will be low for two of them entering at the same time but there is a possibility still. If that is the case, I will ask them to assign solely to one person for this part of the job.

Right now the user would love the system able to find the last service number and increment by 1 so that they don’t have to find the last service number by themselves.

If they only use 1 type of category, I would have the expression works already and because there are multiple categories and multiple codes, I have difficulties with the expressions to make this work.

[Aleksi_Alkio]

What would be the EventKey value? Is it like Cat1Cod0199?

[Aleksi_Alkio]

And is the [Service Number] field a number field like 1…100?

[Russey_Novell]

@Aleksi_Alkio Similar. Cat1-Cod01-99 Translate

[Russey_Novell]

@Aleksi_Alkio So far from the data I am looking at the user has 4 Category (Cat1, Cat2, Cat3, and Cat4). 3 Codes (Cod01, Cod02, Cod03). 15 rows of running numbers from 1-9.

Examples: Cat1-Cod01-1, Cat1-Cod01-2, Cat1-Cod01-3…

Cat2-Cod01-1, Cat2-Cod01-2, Cat2-Cod03-1…

Cat3-Cod03-1, Cat3-Cod01-1, Cat3-Cod01-2…

Cat4-Cod01-1, Cat4-Cod02-1, Cat4-Cod03-1…

[Aleksi_Alkio]

You can read the last number like this… MAX(SELECT(TableName[Service Number],AND([Category]=[_THISROW].[Category],[Code]=[_THISROW].[Code])))+1