Solved: Re: How do I prevent duplicate rows? I have a ta - Page 2

Terry_Holt · 03-23-2018 05:00 PM

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?

Steve

Try:

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

FILTER("MyTable", ...) gathers a list of rows from the MyTable table that match the given criteria (...; see (2)).
AND(..., ...) requires that both of the given criteria (...; see (3) & (4)) match.
ISNOTBLANK([MyColumn]) limits matches to only rows with a non-blank value in the MyColumn column.
([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.
... - LIST([_THISROW]) removes the current row from the list of gathered rows (...; see (1)).
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.

View solution in original post

Wellington

Had the same issue and that worked great for me

Thanks!

Juan_Camilo_Sie

HI @Steve , Came here latelly, but I’m trying to use the formula and I’m reciving this Error due to the Step (5) "- LIST([_THISROW])

Error: cannot convert ‘List of Number’ to ‘List of Ref to table ‘My Table’ of Text’

Steve

Please post a screenshot of your entire expression with the error displayed.

Juan_Camilo_Sie

Steve

What are you trying to do? You are NOT attempting to prevent a duplicate row as the expression is intended to do.

Juan_Camilo_Sie

Yes, I’m Creating row based on an action, and I don’t want to add rows that have already been created

Steve

it appears you are adding a row to a table that is not Detalle Rendimiento. The expression is meant to prevent duplicates within the same table.

Juan_Camilo_Sie

That make sense, how can I do it in that way

Juan_Camilo_Sie

Hi @Steve , I Added the same formula in the Colum [Grupo de Trabajo] Valid if,

And now is not showing the Error, but it is still allowing me to add duplicates rows in that Colum, any suggestion ?.

To give you more information…
I have a table with All the operators, those operators has a group where they belong to
(Only when the [Área] Colum is “Fumigación” they have group, in the other options to [Área] It doesn’t work).
What I have is an action that brings me Daily All the operators to the “Detalle Rendimiento” Table.
So at this point it is working, but I want that when the [Área] Column is = “Fumigación”, It only brings me 1 [Grupo de trabajo] of each

This is what I have, but when I trigger the action (ForEachRow Of table “Operadores”) It’s still bringing me duplicated rows of [Grupo de Trabajo]

Simon1

I’m thinking of using this, but before I do I’m wondering if it will slow the app down considerably as it will need to check the whole table.
Thanks

Steve

A Valid If expression is only evaluated when a row is created or modified. If you make bulk changes to your table from the app, a complex Valid If expression could noticeably affect performance.

Simon1

thanks I might give it a go

ErickSE

Hi everyone, I came to this old conversation because I’m with a problem related to this expression:

ISBLANK(  FILTER(    "Obra",    ([_THIS] = [NúmeroObra])  )  - LIST([_THISROW]))

The expression change the data type from text to Yes/No and the values disapere… When I turn to text the data is there but I can not add, edit… So how can I use this? I want to prevant duplicate values

Steve

ErickSE

Hi @Steve, thank you for this responce. The problem I was facing is that I was writing the expression in the field Formula! After read the article and take a look at the example I realized the right place…
Leaning how to use appsheet every day!

ancy_shaji

Hello,

I need help preventing duplicate for 3 fields. Per the suggestion given above, I used the below validation statement
=COUNT(SELECT(Items[Name], AND([Name] = [_THIS], [Name]<> [_THISROW].[Name]))) = 0

and it prevented duplicate for 1 field, but the two other columns got saved with duplicates.

I am trying to do the validation for Employee Id, Identifier and Username columns. Can some one help me? The validation statements for each field are given below,

=COUNT(SELECT(EFR[EID], AND([EID] = [_THIS], [EID]<> [_THISROW].[EID]))) = 0

=COUNT(SELECT(EFR[Identifier], AND([Identifier] = [_THIS], [Identifier]<> [_THISROW].[Identifier]))) = 0

=COUNT(SELECT(EFR[Username], AND([Username] = [_THIS], [Username]<> [_THISROW].[Username]))) = 0

Thank you

dbaum

@ancy_shaji wrote:

[Name]<> [_THISROW].[Name]

In each expression, use the table's key column where you use [Name] in this example.

ancy_shaji

I understood now, as the EID(Employee Id) was the key it worked for the EID column. I updated the same for Identifier and Username column and it worked perfectly. Thank you so much for the help!!