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

[Tom_Graham]

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!

[Suvrutt_Gurjar]

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

[Tom_Graham]

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]))))))

[Suvrutt_Gurjar · Accepted solution]

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.

[Tom_Graham]

thanks - works perfectly now - thanks!

[Patrick_Paul]

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]))))))

[Steve]

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.

[Patrick_Paul]

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?

[Steve]

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

[SuhasVenkatesh]

Dear All,

I have a table named "Import Waybill" with the below mentioned coloumns

Way Bill ID = (Unique ID) as the Key

Way Bill Date = Date

Way Bill No = Text

Truck Number Plate = Text

Waybill PK = Text with the app formula as      =   text([Waybill Date],"DD-MMM-YY")&"-"&[Waybill No]&"-"&[Truck Number Plate]

I wish to avoid entry of duplicate records which are of the same date, having the same way bill no and same truck plate number. 

I tried using the the below mentioned formula in the coloumn Waybill PK

Valid If = NOT(IN([_ThisRow].[Waybill PK],SELECT(IMPORT WAYBILL[Waybill PK],[Waybill PK] <> [_ThisRow].[Waybill PK]))) 

 and 

NOT(IN([_This],SELECT(IMPORT WAYBILL[Waybill PK],[Waybill PK] <> [_ThisRow].[Waybill PK])))

Both did not work. Pls suggest if you have any better solution on the same.

[SuhasVenkatesh]

Thanks all, I have figured out the solution for my query in the previous post.

AND(([Waybill Date]<>[_THISROW].[Waybill Date]),([Waybill No]<>[_THISROW].[Waybill No]),([Truck Number Plate]<>[_THISROW].[Truck Number Plate]))