How do I prevent duplicate rows? I have a ta

[Terry_Holt]

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?

[Phil]

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

[Phil]

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

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

[Phil]

@Terry_Holt

Terry have you been able to get this working?

[Steven_Coile]

@Philip_Garrett_Appsh the doc looks good to me!

[Phil]

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

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

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

[Grant_Stead]

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

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

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

[Steven_Coile]

@Grant_Stead I do, myself.

[Terry_Holt]

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

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

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.

[Phil]

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

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

[Phil]

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

[Phil]

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

[Phil]

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

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

[Grant_Stead]

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

[Grant_Stead]

@Philip_Garrett_Appsh Also, I would give the reader hope early in the article…

We are sometimes asked if there is a way to make the key value editable. The short answer is no. However, you can ensure that any value is a unique value, while remaining editable, and still get a constant unique key value that will remain constant for the life of the record.

[Phil]

@Steven_Coile@Grant_Stead@Terry_Holt

Excellent comments! I incorporate all of them. Here is the latest version help.appsheet.com - Editable Keys

Editable Keys help.appsheet.com

[Steven_Coile]

@Philip_Garrett_Appsh looks good!

[Phil]

@Terry_Holt @Grant_Stead

Thanks for clarifying Grant. Here is the expression:

=COUNT(SELECT(Customers[Name], AND([Name] = [_THIS], [Name]<> [_THISROW].[Name]))) = 0

— when used as the ValidIf condition for field Name, it ensures that every customer has a unique value for Name. In this example, we assume that Name is the key for the Customers table.

The second part of the AND expression is what allows the Update to succeed. It does not count the Name as a duplicate when examining the current row.

You can find this expression as the last example under topic “Using a list in a column constraint” in this article help.appsheet.com - List Expressions and Aggregates List Expressions and Aggregates help.appsheet.com

[Phil]

@Steven_Coile Thanks again! Really appreciate your help.

[Terry_Holt]

Works like a charm. Thank you @Steven_Coile and @Philip_Garrett_Appsh!!!

[Steven_Coile]

@Philip_Garrett_Appsh 's expression will not work as expected if [Name] is blank. Only ever use = and <> if you are sure both sides will never be blank.

[Terry_Holt]

@Philip_Garrett_Appsh

I made the 'Name" the key as recommended and tried your expression,

=COUNT(SELECT(Items[Name], AND([Name] = [_THIS], [Name]<> [_THISROW].[Name]))) = 0

It does allow updates to the row, but it does not allow the name to be edited because it is a key.

[Steven_Coile]

@Terry_Holt you’ll have to use some other column as key if you want to be able to edit the name. A key value must be fixed for the lifetime of the row.

[Steven_Coile]

@Terry_Holt these expressions we’re providing allow you to ensure non-key column values are unique without making them key columns.

[Terry_Holt]

@Steven_Coile

Your suggestion almost works.

I created a virtual column: Column Name is KEY Type is TEXT App formula is UNIQUEID() and made it the

key to the table

Then added the VALID_IF expression you suggested to the NAME column:

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

It prevented a new duplicate item and I was able to edit the name. But I couldn’t edit any of the other fields (column values) and save the changes.

[Steven_Coile]

@Terry_Holt your key column should not be a virtual column, it should be an actual column in your spreadsheet. The reason is that the App formula of a virtual column is recomputed every time the app syncs. Every time that App formula is recomputed, uniqueid() will produce a new value, resulting in a key value that changes! The key value should only be computed once: when the row is created.

[Aaron_Moolb]

I am using a similar formula to prevent duplicates and it works awesome but I am unable to edit the record because the Valid_If kicks in and displays my error message.
Any ideas to prevent duplicates that will still allow me to open and edit the record?

IF([_THIS]=LOOKUP([_THISROW].[the column you want unique],Table,KeyColumn,the column you want unique),TRUE,NOT(IN([_THIS],Table[the column you want unique])))

[Steve · Accepted solution]

Try:

ISBLANK(
  FILTER(
    "MyTable",
    AND(
      ISNOTBLANK([MyColumn]),
      ([MyColumn] = [_THISROW].[MyColumn])
    )
  )
  - LIST([_THISROW])
)

1. FILTER("MyTable", ...) gathers a list of rows from the MyTable table that match the given criteria (...; see (2)).

2. AND(..., ...) requires that both of the given criteria (...; see (3) & (4)) match.

3. ISNOTBLANK([MyColumn]) limits matches to only rows with a non-blank value in the MyColumn column.

4. ([MyColumn] = [_THISROW].[MyColumn]) further limits the matches to only rows where the MyColumn column value is equal to the current row’s MyColumn column value.

5. ... - LIST([_THISROW]) removes the current row from the list of gathered rows (...; see (1)).

6. ISBLANK(...) asks whether the resulting list of rows (...; see (5)) is empty.

(1) - (4) together answer the question, “which rows have the same column value as the one in the form?”

(5) answers the question, “which rows other than the one in the form have the same column value as the one in the form?”

(6) answers the question, “do no rows other than the one in the form have the same column value as the one in the form?” If no other rows have the same value, the value in the row in the form is unique.

[Aaron_Moolb]

5. answers the question, “which rows other than the one in the form have the same column value as the one in the form?”
   None…I have a column names Lead ID so if I add another record to the table and I input a Lead ID that already exists, it wont let me. But I need to be able to reopen that and edit it.

(6) answers the question, “do no rows other than the one in the form have the same column value as the one in the form?” If no other rows have the same value, the value in the row in the form is unique.
No other columns in the table have the same info so this Lead ID column is unique

Let me give this a whirl and I will let ya know. Thanks

[Aaron_Moolb]

It says it cant fine the Lead ID column…weird

ISBLANK(
FILTER(“Customers”,
AND(
ISNOTBLANK([Lead ID]),
([Lead ID]=[_THISROW].[Lead ID])
)
)
-LIST([_THISROW])
)

[Steve]

Is Lead ID a column in the Customers table?

[Aaron_Moolb]

Yes, it is the column I am wanting to lock down to be unique

[Aaron_Moolb]

Nevermind…I said Lead ID when its the [Lead Number] column.
It workes great!!! Thank you