adding another constraint

AppSheet AppSheet Q&A
,
1

@Joseph_Seddik

Prior Post

IF( IN(1, SELECT(ProductModel[Created_Order], [Season] = [_ThisRow].[Season])),
MAX(SELECT(ProductModel[Created_Order], [Season] = [_ThisRow].[Season])) + 1,
1
)

Hi, his is expression is helping me out a lot thank you.

I would like to further constraint it by the example below:

I have a Category Column Type Enum, Values (A,B,C)

I would like Created_Order to start from 1, for every Category for every Season.

How can I express this?

2

Try adding the constraint by ANDing the two

AND([season]=[_thisrow].[season], [cat]=[_thisrow].[cat])

1 Like
3

The second argument of a SELECT() statement comprises the constraint for the selection of row. To group various constraints together so that all are TRUE you should use the logical function AND(). Like this:

IF(
IN(1,
SELECT(ProductModel[Created_Order],
AND(
[Season] = [_ThisRow].[Season],
[Category = [_ThisRow].[Category]
)
)
),
MAX(
SELECT(ProductModel[Created_Order],
AND(
[Season] = [_ThisRow].[Season],
[Category = [_ThisRow].[Category]
)
)
) + 1,
1
)

1 Like
4

It tells me that

“The inputs for funtion ‘MAX’ should be a list of numeric values.”

Probably because my category is not a number?

What can I do?

5

woops, sorry forgot to change my Product_Order to type Number :sweat_smile:

1 Like
6

@TeeSee1 @Joseph_Seddik

Thank you, Very much!!

2 Likes
7

The IF() is unnecessary: if SELECT() returns no values, MAX() will return 0.

The following is sufficient and more efficient:

MAX(
  SELECT(ProductModel[Created_Order],
    AND( 
      [Season] = [_ThisRow].[Season], 
      [Category = [_ThisRow].[Category]
    )
  )
) + 1
2 Likes
8

Thank you @Steve !

I didn’t know about this but had thought about it, so before writing this expression I tested with:

MAX(LIST())

which gave me an error, so I thought it wouldn’t work since it is not also in the doc. After your reply I tested again with:

MAX( LIST(0, 1, 2) - LIST(0, 1, 2 ) )

And this worked; MAX() returned a 0 as you’ve said. Thanks much!

@NCD Please implement Steve’s expression instead. It will cut execution time by half.

2 Likes
9

The reason MAX(LIST()) fails is because LIST() has an undefined type (which may default to Text). There’s no way to compute the maximum of undefined or Text values. LIST(0, 1, 2) has a Number type; removing all of its values with list subtraction doesn’t remove the list’s type. Likewise, SELECT() returns a list with a specific type. The type is defined even if the list contains no elements.

1 Like
10

Yes I thought so, but was too quick to test with an undefined empty list :slightly_smiling_face: Thank you!

1 Like