Solved: Prevent duplicate row of similar ENUM selections - Page 2

Mic_L_Angelo · 02-03-2020 09:58 PM

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

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

View solution in original post