Initial Value - computed and unique

[Fraxinus]

I am having a heck of a time getting my head wrapped around this. This is the outcome I am looking to drive:

I have a computed initial value, which is just using MAX to calculate the next number in a sequence (that expression works just fine), however I want to give my users the ability to override the sequence and assign a different value, PROVIDED it has not already been used before in that same column.

I have a VALIDIF constraint of: AND(NOT(IN([_THIS],Samples[Sample])),[_THIS]<999999)

and and INITIAL VALUE of: RIGHT(CONCATENATE("000000",MAX(Samples[Sample])+1),6)

The problem is the user cannot make edits to other data in the row, because the VALIDIF prevents re-saving the record with this existing column value.

Hopefully that makes sense.

Any takers? Could really use some bigger brains behind this than myself.

[peterson]

The best formula for valid if is the following:

Preventing duplicates in this way is very straight-forward in AppSheet; all you need is a validation formula in the Valid If space for the column you want to prevent duplicates on.

Not(In([_THIS], SELECT(Table[Column_Name_for_THIS_Column], [TableID] <> [_ThisRow].[TableID])))

• [Column_Name_for_THIS_Column] needs to be the name of the column for the one you’re trying to validate.
  • i.e. If I was trying to prevent a duplicate User_Name, I could put [User_Name] there - because that is the column that I’m putting this validation formula on.
  • If I didn’t want the same license plate number entered twice, and I was putting this formula inside the Valid If formula space for the field that records the license plate numbers, I would use [Vehicle_License_Place] or whatever the name of that field is.

Here's the original post where I found it

https://www.googlecloudcommunity.com/gc/Tips-Tricks/How-to-Prevent-Duplicate-Records/m-p/649308#M890...

[Fraxinus]

I appreciate the effort, but that didn't solve my problem. When I attempt to edit an existing record in the form view, it doesn't allow the user to save the form and triggers the VALIDIF error message.

The expression is used in VALID if is:

NOT(IN([_THIS],SELECT(Samples[Sample],Samples<>[_THISROW].Samples)))

Where "Samples" is the name of the table, and [Sample] is the column where I am trying to prevent the duplicate "Sample Number", but still want users to be able to edit/save a record once that sample number is already assigned.

[peterson · Accepted solution]

NOT(IN([_THIS],SELECT(Samples[Sample],[ID]<>[_THISROW].[ID])))

Try this one.

PS: I am guessing that your key columns is called ID, if not just change the names and it will work. Please vote for solved if it works.

[Fraxinus]

That did the trick - you are my new hero!! Thank you for that solution!! 🙂