Prevent duplicate row of similar ENUM selections

[Mic_L_Angelo]

Hey folks, I need some help with an expression.

I have a table called “The Pinpoints”.

This table has 4 Enum columns

[KEY ID - States]
[KEY ID - Areas Counties]
[KEY ID - Hoods Cities]
[KEY ID - Locations Spots]

What I’m looking for is an expression that prevents users from creating a new row if a combination of these columns already exists in the table.

I tried something like

SELECT(AND(
[KEY ID - States] <> [_THISROW].[KEY ID - States],
[KEY ID - Areas Counties] <> [_THISROW].[KEY ID - Areas Counties], 
[KEY ID - Hoods Cities] <> [_THISROW].[KEY ID - Hoods Cities],
[KEY ID - Locations Spots] <> [_THISROW].[KEY ID - Locations Spots],
 [_RowNumber] <> [_thisrow].[_RowNumber]).

But this didn’t work. Does anyone have an idea of how i can customize this code?

[Suvrutt_Gurjar · Accepted solution]

Hi @Mic_L_Angelo,

You may wish to try something like below.

Please create a column with an expression something like CONCATENATE( [[KEY ID - States], [KEY ID - Areas Counties], [KEY ID - Hoods Cities], [KEY ID - Locations Spots]). Suppose this column is called [Combined Details]

Then you may wish to create a valid_if expression such as below in any of the Enum columns mentioned above.

NOT(IN([Combined Details], SELECT(Table Name[Combined Details], NOT(IN([Table’s Key Column], LIST([_THISROW].[Table’s Key Column]))))))

One thing to note is that such duplicate prevention logic will likely fail in multi user enviromnet , if more than one user is trying to simultaneously add a record in the table.

Edit: This combined column can be a VC

[Mic_L_Angelo]

Suvrutt_Gurjar:

NOT(IN([Combined Details],

@Suvrutt_Gurjar so so the initial code you suggested works, but i wanted to add a condition to filter the ENUM drop-down options.

For example, the [KEY ID - States] drop-down already has a ValidIF formula

SELECT(The Locale_Numbers[LOCALE KEY ID], [LOCALE CATEGORY KEY ID] = “67A32355”)

Would you know how i would include this condition into the formula you originally suggested?

I tried to do so myself, but i had trouble. I appreciate any help.

[Suvrutt_Gurjar]

Hi @Mic_L_Angelo ,

Thank you forthe update. If you have used valid_if for those enum columns drop down choices, you could use the valid_if condition for restricting duplicates that I mentioned in any other column of fthe table. You may even include the [Combined Details] column and include the valid_if condition with that column itself.

In general, you may include it in a column that is closer in position to the last or either of those enums. This will mean the user does not have to scroll up and down much to see the error message as to why her/his form is not getting saved.

Hope this helps.

[Mic_L_Angelo]

@Suvrutt_Gurjar

Suvrutt_Gurjar:

NOT(IN([Combined Details], SELECT(Table Name[Combined Details], NOT(IN([Table’s Key Column], LIST([_THISROW].[Table’s Key Column]))))))

Thanks for the info. And good call on that. I just used your expression for another field and it worked like a charm. Many thanks for the insight and swift response! I’m really grateful.

[Suvrutt_Gurjar]

Thank you @Mic_L_Angelo for your update. Good to know that the expression works as per your requirement.