Valid if not already in row

[Roger_Dalomba]

I am having an issue that I can’t resolve.
I have a VALID IF that works as expected.

NOT(IN([ITEM #],INVENTORY[ITEM # ))

if someone enter a value that is already entered…the correct message comes up PRIOR to submitting form.

the problem I have is that i cannot edit row because I still get the same error message.

i do have one that work but the error message only comes up on form submit.
NOT( IN( [_THIS] , SELECT( INVENTORY[ITEM #] , [ITEM # VIN] <> [_THISROW].[ITEM #] ) ) )

what am I missing…

[Steve · Accepted solution]

This this:

ISBLANK(
  FILTER(
    "INVENTORY",
    AND(
      ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER]),
      ([ITEM #] = [_THISROW].[ITEM #])
    )
  )
)

Or this:

ISBLANK(
  FILTER(
    "INVENTORY",
    ([ITEM #] = [_THISROW].[ITEM #])
  )
  - LIST([_THISROW])
)

[Roger_Dalomba]

1st one…thanks

ISBLANK(
FILTER(
“INVENTORY”,
AND(
([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER]),
([ITEM #] = [_THISROW].[ITEM #])
)
)
)