Maximum Value based on Condition

[RohitGaur]

Hi,

I have one table name Mst_User and it has two columns.

Column1 =  Category ( This is a List column and have two list values "Contractor", "Company Employee"

Column2 = EmpCode (Number)

Condition:

1. When i select Contractor, it should pick the maximum value of EMP Code + 1001. This value should be appearing in EMP code.

2. When i select Company Employee, it should allow me to enter value.

I have applied IF condition, Max() and IFS but getting following error message: 

Valid_IF:

IFS(
[_THISROW].[Category]="Contractor",
(MAX(List(Select(Mst_User[EmpCode],[Category]="Contractor"))))+1001
)

"The expression is valid but its result type 'Number' is not one of the expected type: Yes/No"

Appreciate your help on my query.

Thanks,

Rohit Gaur  

[TeeSee1]

Where is your expression set?

It should be the Initial Value of the EmpCode. (I am suspecting you have it in the valid-if..)

[RohitGaur]

Hello,

I have updated my expression set in the question for reference. Please help. 

[TeeSee1]

I had initially missed that you had your epx in valid-if..

Try using below in the initial value. 

IF(
 [category] = "Contractor", MAX([EmpCode]) + 1001,
 0
)

EDITED: [EmpCode] should be Mst_Users[EmpCode]

Note, the user can change the value regardless of the category.

If you want to prevent the user from editing the value when the Contractor category is chosen, then you should put the following in the editable? field also

[Category] <> "Contractor"

[Ryan_Mortimer]

MAX() - is used to select the highest value(s) not to set the highest value. 

Have you tried to do start small and simple then work your way up?

What happens if you start with something like the following as a “Valid_If”

[YOUR COLUMN] < [EMP CODE] + 1001

[Ryan_Mortimer]

Unless I have misunderstood the task and requests.

If I misunderstood your usage of MAX(), just wrap it with ANY().

ie.

ANY(MAX([EMP CODE]))

EDIT: Thanks to @Steve for correcting below

[Steve]

MAX() returns a singular value, not a list. Wrapping it with ANY() will have no effect. It won't hurt, but it won't help, either.

[RohitGaur]

Hello,

I have updated my expression set in the question for reference. Please help me.

[Ryan_Mortimer]

Hi Rohit,

What I would do is work towards getting this to work with simply ONE condition.

Once that works, try again with the other condition.

THEN you can work on making both the conditions work together.

Have you tried making this work

[RohitGaur]

Hi Ryan,

Yes, in simple condition it is working perfectly but when i try to achieve my final objective with Max number.. doesn't get success. 

[Ryan_Mortimer]

I am still a little confused about whether you want this to be an initial value or a Valid_IF value

I'm going to assume you need to do both, but this is what I would do.

1. Use this expression in the Initial Value

IFS(
  [Category] = "Contractor",
    MAX(Mst_Users[EmpCode])+1001,
  [CATEGORY] <> [Contractor],
    "")

2. To make it so it is a predetermined (or "locked) value when the user selects contractor - use this expression in Editable?

AND(
[CATEGORY] = "Contractor", 
ISNOTBLANK([EmpCode])

Show More

PS: You might not need to utilise the ISNOTBLANK part of this code, but it wouldn't hurt to add it at this stage. You can play with the optimisation of the codes once it is behaving as you require

Also, please note, this is just a compilation between all the feedback (mainly from @TeeSee1 and a few new suggestions from myself. 

[RohitGaur]

My Friend,

Whole day, i kept doing the R&D to make this work and finally i am able to crack this.

below is the working solution of this problem:

IFS([Category]="Contractor",
Max(List(0)+(SELECT(Mst_User[EmpCode],[Category]="Contractor")))+1

)

[RohitGaur]

Hello,

I have updated my expression set in the question for reference. Please help.

[RohitGaur · Accepted solution]

Dear Friends,

Thanks for your time to attempt to solve this issue. Below please find the working solution for this issue:

IFS([Category]="Contractor",
Max(List(0)+(SELECT(Mst_User[EmpCode],[Category]="Contractor")))+1

)

Thanks,

Rohit Gaur