Valid If - What formula to use to only allow unique values

We want to restrict the ability to add a value in a field, if that value is already in the field.
i.e. we want to replicate the way Appsheet will restrict duplicate values in a “key” field

But, we use a different “key field”.
I tried making this other field also a"key", but after saving it was deselected. So appears can only have 1 key.

I know IF and COUNT should be able to be used, but not sure how to…

Thanks!

1 Like

Hi @Tom_Graham,

Please explore if the approach described in the section " Preventing Duplicate Field Values" in the following article helps you

https://help.appsheet.com/en/articles/961274-list-expressions-and-aggregates

1 Like

thanks @Suvrutt_Gurjar

  • I can use that type of formula to create the correct Valid condition for the SKU field
  • but, doing so causes the Key to change to the “Computed Key”
  • the Key field previously was ‘Custom Label’
  • therefore now “Custom Label” is not restricted from having duplicates
  • also, I want to keep “Custom Label” as the Key value, as there is likely other parts of the app that relay on this being the Key.

Is it possible to achieve without creating and using a Computed Key?

i.e. “Custom Label” and “SKU” both must contain ‘unique values’. And we want “Custom Label” remain as the Key. How can we restrict SKU from accepting duplicates?

Thanks!

example of current formula inside the Valid IF field for “SKU” field:

NOT(IN([_THIS], SELECT(Inventory Data[SKU], NOT(IN([SKU], LIST([_THISROW].[Custom Label]))))))

Hi @Tom_Graham,

If I have correctly understood your requirement ,could you try following expression in your SKU field’s valid_if expression

NOT(IN([_THIS], SELECT(Inventory Data[SKU], NOT(IN([Custom Label], LIST([_THISROW].[Custom Label]))))))

The above expression assumes that [Custom Label] is key field and ]SKU]is the non key field that you do not wish to be duplicated.

5 Likes

thanks - works perfectly now - thanks!

1 Like

I am attempting to use this code to accomplish the same thing. Unfortunately, I’ve found an unwanted side effect is that it won’t allow you to return to a record and edit it because it looks back at “itself” and says it’s a duplicate. Is there a solution to that problem? Here is my exact code:

NOT(IN([_THIS], SELECT(Level 3[Employee Number], NOT(IN([Employee Number], LIST([_THISROW].[Audit ID]))))))

Try:

ISBLANK(
  FILTER(
    "Level 3",
    AND(
      ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER]),
      ([Employee Number] <> [_THISROW].[Audit ID]),
      ([Employee Number] = [_THIS])
    )
  )
)

Essentially: FILTER() finds all rows in the Level 3 table with a matching Employee Number, excluding rows where the row’s Employee Number matches the Audit ID column value of this row, and excluding this row itself. If FILTER() returns anything after those exclusions, you’ve got a duplicate value.

1 Like

Just curious (and may not matter), but if the Audit ID and Employee Number are never equivalent (the Audit ID is a randomly generated alphanumeric string), is the second boolean test: ([Employee Number] <> [_THISROW].[Audit ID]) necessary?

That’s your call. You had it in your expression so I preserved it.