Preventing Duplicate Record

[MauriceWhelan]

Hi. I have a Product_Master_File table with a column [Raw Code]. I need to enter an expression in the Valid_If constraint to prevent the user submitting a Raw Code that already exists. I need to use another column in the table as the key field so I can’t set the [Raw Code] column as the key field.

Any help on this would be great.

Thank you.

[Steve · Accepted solution]

Try:

ISBLANK(
  FILTER(
    "Product_Master_File",
    AND(
      ([_THISROW].[Raw Code] = [Raw Code]),
      ([_THISROW].[RowKey] <> [RowKey])
    )
  )
)

Replacing RowKey with the name of the key column of the Product_Master_File table.

[MauriceWhelan]

Worked perfectly @Steve, thanks a million.

[MJvanEwijk]

Hello Steve,
I tried this expression and it works, I added the “Invalid value error” with an error/duplicate message.
The problem is that when would like to edit the row it gives the message that the record already exits. So I am no able to edit the form with your mentioned expression… How can I solve this?

[Steve]

Please provide a screenshot of the complete Valid If expression.

[MJvanEwijk]

Hi Steve, thank you for replying.
The table name is WorkOrder Man Hours.
I would like to prevent employees adding same name (“Vollledige_Naam”) in the same project_ID (ENUM from select active projects) but would like to edit if wrong data is added in “work Hours…”
If I try to edit it give the invalid value error.
Hope you can help
Thanks Muk

[Steve]

Add this condition inside your AND() expression:

([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])

You existing expression answers the question, “does this particular combination of values occur anywhere in the table?” Adding the condition above changes the question to, “does this particular combination of values occur anywhere else in the table?”