How do I prevent duplicate rows? I have a ta...

expressions
(Terry Holt) #1

How do I prevent duplicate rows?

I have a table with items and the item names are in the [Name] column. I want to prevent adding another row for the same item. I had the [Name] column as the key, which prevents duplicate names, but it appears that the key column is not editable in the form view.

I found this expression in the community, VALID_IF = NOT(IN([_THIS],Item[Name]) which does prevent duplicate name from being added. But again, run into problems when editing a item. I can edit the name and save. But if I am editing any other field and keeping the name the same, I am unable to save the changes.

My third attempt was to make a virtual column that duplicated the name

column and make it the key, but it had the same affect as making the original name column the key. I was unable to edit the name.

Am I trying to do the impossible?

(Philip Garrett) #2

@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.

(Philip Garrett) #3

@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.

(Grant Stead) #4

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

(Philip Garrett) #5

@Terry_Holt

Terry have you been able to get this working?

(Steven Coile) #6

@Philip_Garrett_Appsh the doc looks good to me!

(Philip Garrett) #7

@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.

(Grant Stead) #8

@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?

(Steven Coile) #9

@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.

(Grant Stead) #10

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

(Steven Coile) #11

@Grant_Stead I do, myself.

(Terry Holt) #12

@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!!!

(Steven Coile) #13

@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.

(Steven Coile) #14

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.

(Philip Garrett) #15

@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.

(Steven Coile) #16

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

(Philip Garrett) #17

@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.

(Philip Garrett) #18

@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.

(Philip Garrett) #19

@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

(Steven Coile) #20

@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!