More than 24 entry of one value

[Sayad]

I want that there should not be more than 24 entry of one value. 

can you explain how can i do that. 

shelf code

[Joseph_Seddik]

Valid if

NOT( COUNT( FILTER(table, [Shelf Code] = [_This]) - [_ThisRow] ) > 24 ) 
  

[Sayad]

NOT( COUNT( FILTER(CAMPUS SCAN SHEET, [Shelf Code] = [_This]) - [_ThisRow] ) > 24 ) 

Arithmetic expression '(SELECT(CAMPUS SCAN SHEET[GATE ENTRY NO],([Shelf Code] = [_This]))-[_ThisRow])' has inputs of an invalid type 'Unknown'

[Joseph_Seddik]

Why aren't you using the same expression I gave you? 

[Sayad]

The function you have given. He's not working. Showing error. 

[Joseph_Seddik]

Oh, really? 

---

@Sayad wrote:

NOT( COUNT( FILTER(CAMPUS SCAN SHEET, [Shelf Code] = [_This]) - [_ThisRow] ) > 24 )

(SELECT(CAMPUS SCAN SHEET[GATE ENTRY NO],([Shelf Code] = [_This]))-[_ThisRow])

Do they look the same to you? 

[Sayad]

Hi @Joseph_Seddik 

[Joseph_Seddik · Accepted solution]

OK, thank you! Sorry I didn't know about this behavior of the expression assistant. Please, try this:

NOT(
  COUNT(
    FILTER(CAMPUS SCAN SHEET, [Shelf Code] = [_This]) - LIST([_ThisRow])
  ) > 24
) 

[Sayad]

Hi @Joseph_Seddik

Can you explain where i mistake in function.

IFS(
  NOT(NOT(
  COUNT(
    FILTER(CAMPUS SCAN SHEET, [Shelf Code] = [_This]) - LIST([_ThisRow])
  ) >= 24,"Duplicate Shelf Code",

  NOT(OR(
  AND(
    startswith([SHELF CODE],"MCOP-"),
    LEN([SHELF CODE]) = 8
  ),
  AND(
    startswith([SHELF CODE],"MCUP-"),
    LEN([SHELF CODE]) = 8
  ),
  AND(
    startswith([SHELF CODE],"FKSOP-"),
    LEN([SHELF CODE]) = 9
  ),
AND(
    startswith([SHELF CODE],"FKSUP-"),
    LEN([SHELF CODE]) = 9
),AND(
    startswith([SHELF CODE],"HOLD"),
    LEN([SHELF CODE]) = 7),
AND(
    startswith([SHELF CODE],"ADJUST"),
    LEN([SHELF CODE]) = 11),
AND(
    startswith([SHELF CODE],"MD-"),
    LEN([SHELF CODE]) = 5)))
),"Invalid Shelf code"
))))

[Joseph_Seddik]

Try this: 

IFS(
  NOT(
    COUNT(
      FILTER(CAMPUS SCAN SHEET, [Shelf Code] = [_This]) - LIST([_ThisRow])
    ) >= 24
  ), "Duplicate Shelf Code",
  NOT(
    OR(
      AND(
        STARTSWITH([SHELF CODE],"MCOP-"),
        LEN([SHELF CODE]) = 8
      ),
      AND(
        STARTSWITH([SHELF CODE],"MCUP-"),
        LEN([SHELF CODE]) = 8
      ),
      AND(
        STARTSWITH([SHELF CODE],"FKSOP-"),
        LEN([SHELF CODE]) = 9
      ),
      AND(
        STARTSWITH([SHELF CODE],"FKSUP-"),
        LEN([SHELF CODE]) = 9
      ),
      AND(
        STARTSWITH([SHELF CODE],"HOLD"),
        LEN([SHELF CODE]) = 7
      ),
      AND(
        STARTSWITH([SHELF CODE],"ADJUST"),
        LEN([SHELF CODE]) = 11
      ),
      AND(
        STARTSWITH([SHELF CODE],"MD-"),
        LEN([SHELF CODE]) = 5
      )
    )
  ), "Invalid Shelf code",
  TRUE, "Unknown error."
)