How do I prevent duplicate rows? I have a ta

@Steven_Coile

Good point Steven! I am in the process of updating the docs to use your expression in place of mine. I am thinking of writing the following. Can you review it for me?

Preventing Duplicate Values:

You can ensure that every record in a table has a unique field value by specifying a Valid_If expression for that field.

=NOT(IN([_THIS], SELECT(Customers[State], NOT(IN([CustomerId], LIST([_THISROW].[CustomerId]))))))-- when used as the ValidIf condition for field State, it ensures that every customer has a unique value for State. In this example, we assume that CustomerId is the key for the Customers table.

This expression uses [_THISROW].[CustomerId] to obtain the key value of the current row. This allows you exclude the current record from the list of records having duplicate field values. This makes it possible for you to update the current record without reporting it as a duplicate field value.

@Terry_Holt

The key can never be edited. The key value is assigned when the record is created. The key value must always be unique. Once the new record is saved, its key value can never be changed. The purpose of the key is to uniquely and permanently identify the record.

If you want the Name to be editable, then you need to designate some other field to serve as the key. You might want to consider using a Text field and giving it an InitialValue of UNIQUEID().

Once you have a key field that uses UNIQUEID(), you may still want to prevent duplicate Name values. You can do that by using a Valid_If expression on the Name file to ensure that no other record with the same Name exists.

@Philip_Garrett_Appsh that’s the issue is that if you validate on the column, then it locks it up exactly as Terry described.

@Terry_Holt

Terry have you been able to get this working?

@Philip_Garrett_Appsh the doc looks good to me!

@Steven_Coile

Thanks Steven. I really appreciate your help.

I am thinking of adding a new article about “Editable Keys”. @Terry_Holt is only the latest person to try to achieve this.

In the article: 1. I will describe why the table key cannot be edited.

  1. I will suggest using a UNIQUEID() as the key field.

  2. I will suggest using the VALID_If expression to enforce uniqueness of the field they wanted to use as the secondary key.

@Philip_Garrett_Appsh instead of [_THISROW].[CustomerId] can’t you just use [_THISROW] since it’s a reference to the key anyway? Just more curious… I’ve been doing just [_THISROW] instead of the “deref” version lately when I’m just looking for the key, wondering if I’ll run into trouble?

@Grant_Stead I think the bare [_thisrow] might be confusing to those who don’t already understand a row reference. Making the key column reference explicit doesn’t hurt.

@Steven_Coile true, for the docs… I just meant it’s cool for me to use it that way right?

@Grant_Stead I do, myself.

@Philip_Garrett_Appsh.

So, I should be able to set up an actual column with an initial value of UNIQUEID(), hide the column, and make it a key?

Then the key would be auto generated, couldn’t be edited, and not show up in the views.

Then I would use @Steven_Coile expression to prevent duplicate names and it should allow the name and other columns to be edited and saved.

Sounds like it would work!

I am not able to test it at the moment, but will report back as soon as I can.

Thanks, guys!!!

@Terry_Holt your plan looks correct to me.

Note that you will need to add keys to rows that already exist; AppSheet will not backfill those values.

The problem with NOT(IN([__THIS],Item[Name]) is that Item[Name] (which includes [Name] values for all rows) contains the already-existent value of [__THIS] (the [Name] value for this row), so the expression matches. You have to remove the value this row (and only this row) has:

=not(in([_this], select(Item[Name], not(in([Key], list([_thisrow].[Key]))))))

Replace [Key] with the name of your key column.

@Terry_Holt

Exactly.

That is what I am describing in the new article. Hopefully it will make it easier for the next person who tries this.

@Terry_Holt appsheethelp.zendesk.com - Manually Generating UniqueId Key Values Manually Generating UniqueId Key Values appsheethelp.zendesk.com

@Terry_Holt

@Steven_Coile is right about setting the value of the key field in your existing table rows.

I hope to publish the new article in the next hour or so. I would appreciate it if both of you would review it. It am trying to provide step by step instructions for doing this.

@Terry_Holt

You might also want to set the Label property for the Name field to “true”. That will tell AppSheet to display the value in the Name field in lieu of displaying the actual key value.

@Terry_Holt@Steven_Coile@Grant_Stead

I have taken a shot at the new article. Comments are welcome.

See help.appsheet.com - Editable Keys

Editable Keys help.appsheet.com

@Philip_Garrett_Appsh In step (8) of the “here’s how you do it” part, I suggest clarifying “unique field”, since the newly-added ID/key field is also unique. Otherwise, it looks great!

@Philip_Garrett_Appsh I think that the example is actually best suited to a USER table where you have a UNIQUEID() that represents the user, and then you have a [useremail] column in which you NEED the useremail() to be unique as well… But clearly people may need to change accounts, etc so you choose not to make it the key…