Preventing Duplicate Record

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.

Solved Solved
0 6 439
1 ACCEPTED SOLUTION

Steve
Participant V

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.

View solution in original post

6 REPLIES 6

Steve
Participant V

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.

Worked perfectly @Steve, thanks a million.

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?

Please provide a screenshot of the complete Valid If expression.

3X_d_9_d935b51addabb0ca6d3b715c49c61f9a4228a969.png
3X_0_e_0effe584f81166b9506c401ffd9b720ab3ef262f.png


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

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?โ€

Top Labels in this Space