Maintaining Unique Entries

Ed_Cottrell · 10-10-2021 09:10 PM

To prevent a user entering a duplicate value (e.g. to maintain a unique list)

I’ve be using this Valid If expression:

OR(NOT(IN([_THIS], Table[ColumnName])), [_THIS] = [_THISROW_BEFORE].[_THIS])

…with the second part being required to allow updates to other values in the record using a form, without invalidating the value we are keeping unique (and not also changing) which would block the user from saving the form.

HOWEVER…

this does NOT seem to allow any value to be entered in the constrained column by the Data: set the values of some columns in this row action.

Does anyone know why?

So, recently i’ve started using this instead:

NOT(IN([_THIS], Select(Table[ColumnName], [Key] <> [_THISROW])))

…which I think is nicer anyway.

Lastly…

I also noticed that neither of these two expressions seem to allow the value to be set using the new INPUT() function.

Would love to get other peoples ideas on these two methods, and if there’s other methods people use?

@Steve @MultiTech_Visions @tsuji_koichi @LeventK @Aleksi @Bellave_Jayaram @Rifadm817 @GreenFlux - I’ve learnt a lot from you all in this community

(I fully expect @Steve to come up with something twice as elegant, maybe even half as long!)

Thomas_Varghese

why just not

NOT(IN([_THIS], Table[ColumnName]))
will also wont allow duplicate entry. say coulmn name is phone, would prevent duplicate entry of phone number.

Ed_Cottrell

Hi @Thomas_Varghese, the reason the second part of the first expression is needed is that if you just use NOT(IN([_THIS, Table[ColumnName])) and then you try to update the record at some point (in your example, perhaps you later want to change the person’s address), you can’t then save the form with the same phone number in it…

Hence, you need the second part like OR({First Part}, [_THIS]=[_THISROW_BEFORE].[_THIS]). BUT, as metioned this doesn’t allow a Data: set the values of some columns in this row action to work on the field, so you need the select version of the formula above - which removes the current row from the rows to be checked.

Koichi_Tsuji

Try this.

Create VC with expression like

IN([_thisrow],TableName[ID])

This returns false when the form entry is to add new record. if the transaction is to update the existing row, then it returns true.

Let me name this [Init] as column name.

Then on your virtual expression, place the expression like

IF(
NOT([init]),
NOT(IN([_THIS], Table[ColumnName])),
NOT(IN([_THIS], Table[ColumnName]-LIST([_thisrow].[ColumnName])))
)

This is not tested, but I reckon this would help your case.

Steve

Yours:

NOT(
  IN(
    [_THIS],
    SELECT(
      table[column],
      ([key] <> [_THISROW])
    )
  )
)

Mine:

NOT(
  IN(
    [_THIS],
    SELECT(
      table[column],
      ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
    )
  )
)

I prefer using _ROWNUMBER because it has the same effect here, and I can cut-and-paste the expression to reuse it elsewhere with fewer edits. Purely a matter of personal preference; both approaches are entirely valid.

MultiTech

Yours:

NOT(
  IN(
    [_THIS],
    SELECT(
      table[column],
      ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
    )
  )
)

Mine:

NOT(
  IN(
    [_THIS],
    SELECT(
      table[column],
      ([KEY] <> [_THISROW].[KEY])
    )
  )
)

I prefer using they [Key] value because the KEY is what the system uses to identify the record. Purely a matter of personal preference; both approaches are entirely valid. (^_^)

Ed_Cottrell

Thanks @Steve and @MultiTech_Visions and @tsuji_koichi

Any ideas on this aspect? Sorry Tips & Tricks post as it may have helped some users, but this part is… (…a BUG?):

MultiTech

@Ed_Cottrell is the column in question an EnumList? And are you trying to execute this action through automation?

There’s a known bug when trying to set an EnumList column to a list value through automation.

I.E. Set the EnumList column to “Option1, Option2”

Even though this may satisfy the valid if formula (in the sense that those two options are inside the list provided)
- because of how the system evaluates things, it’s currently causing a logic error.

Ed_Cottrell

Hey @MultiTech_Visions ,

Actually it’s just an email column (in my users table).

I have an action that ‘archives’ a user by removing their email from the [UserEmail] column. There is also another column that holds their [ContactEmail].

Then I have another action that can be used to ‘restore’ the user from archive, which is meant to set the value of [UserEmail] = [ContactEmail].

This restore action fails the data validation rule and the action fails…

In any case I decided it’s not a good approach in this case anyway, as if it really is a duplicate User Email then the operator needs to be able to pick another. So I’ve run with a form for this, because as also mentioned in the first post the Input() function also doesn’t work in this case…

(Good to know about the EnumList bug – one can toil over these bugs for hours to no avail if not!!)