Prevent duplicate entry on a new form

[Eli_b]

I’d like to have an error message for duplicate entries in a column on a new form and set the length to 15 characters, to avoid mistypes, but i’m going in circles. I was using conditional formatting as a bandaid to a google sheet but cant figure out an equivalent here.

[Grant_Stead]

Hey @Eli_b hope you’re doing awesome! Check out this article… If you can be a little more specific, we can help a little better.

help.appsheet.com

Editable Keys

How to make key values work well in your app.

[Eli_b]

It’s a microchip field for foster animals. Right now we’re using a spreadsheet that anyone can edit so we often have issues with incomplete, leaving a number off the end or a putting in a number that’s already assigned to another animal. The chips we most often use have 15 numbers. I’ve got the column in the app set as text though. That seemed like a better fit.

[LongingToProgra]

Hey @Eli_b this should be fairly simple as long as you can make this a required column for your table. Go to Columns and select this particular column and you want to do 2 separate things:

1. Set the Data Validity:
   

   
   
   For the Valid If you could use: IF(Len([Microchip])>=15,True,False)
   Then set a message that goes back to the user if the microchip is less than 15.

2. Set the Key to On. This will auto check for duplicates for you and not allow them. You can have more than one column that is a key for the table if you also have another Key.
   

   

If you can have a record with the microchip field blank then this gets a little trickier. Let me know and I’ll see if I can come up with something else. Also, for all of your current bad data, the next time someone opens a record to edit it, it should force them to fix the microchip column before saving. Hope this helps!

[Eli_b]

It would be convenient to not have the microchip field required but its not a huge deal. I can always fill it will all zero’s until getting the actual number. Id rather do that then have dups:). I’m having a problem with having multiple keys. Once I add the microchip as a 2nd key both the L# and the microchip allow duplicates. I’m learning as I go so it seems like I fix one thing but create 3 problems lol. I had L# set to sequentially generate for new forms but cant do that with a key.

[Steve · Accepted solution]

In the microchip column’s Valid if expression:

ISBLANK(
  IFS(
    ISNOTBLANK(
      FILTER(
        "the-table",
        ([_THIS] = [microchip-column])
      )
      - LIST([_THISROW])
    ),
      "Duplicate microchip ID!"
  )
)

In the same column’s Invalid value error expression:

IFS(
  ISNOTBLANK(
    FILTER(
      "the-table",
      ([_THIS] = [microchip-column])
    )
    - LIST([_THISROW])
  ),
    "Duplicate microchip ID!"
)

Note that the first is merely the second wrapped in ISBLANK().

In both, make appropriate substitutions for the-table and microchip-column.

No need to make the microchip column required. Don’t use the microchip column as or part of the key. I recommend using UNIQUEID() to set the key column value.

If you want to notify the user if a microchip value is not the expected 15-character length but not treat it as an error, consider adding a (virtual?) column of type Show to display a message in that case.

[Grant_Stead]

@Steve I was looking for that part of the docs that we had made that literally had all of the expressions for this… But when I clicked on the link to “preventing duplicates” it takes you to the list docs… Do you remember what I’m talking about?!

[Steve]

The part you’re talking about probably got wiped out in the list expressions doc rewrite. What do you want to see? I’ll try to reproduce it.

[Grant_Stead]

I think it was just that ^

[Steve]

Added:

help.appsheet.com

List Expressions and Aggregates

List expressions use and/or produce lists of values.

[Lucinda_Mason]

Thanks Steve. I was just needing this today for a personal project I’m working on.

[Eli_b]

" If you want to notify the user if a microchip value is not the expected 15-character length but not treat it as an error, consider adding a (virtual?) column of type Show to display a message in that case."

Could you expand on the steps for this? I tried a few things but could still have more or less than 15 and no notification.

[Steve]

Create a virtual column with an App formula expression of "", a Show? expression of (LEN([microchip-column]) <> 15), Type of Show, Category of Text, and Content expression of "Is microchip ID value correct?".

[Eli_b]

What am I doing wrong?

[Steve]

Looks good to me. What makes you think anything is wrong?

[Eli_b]

Nothing happens

[Steve]

Is the column present in your form? Specifically, if you’re using a slice, is it in the slice?

[Eli_b]

It is now, thanks:)

[Eli_b]

Hopefully the last question, Can I change the color of that text?

[Steve]

I’m not sure, actually. You could try using a format rule.

help.appsheet.com

Format Rules: The Essentials

Learn how to change the way information is displayed throughout your app.

[Grant_Stead]

That’s awesome… I think the key knowledge nugget is that the new record you’re creating doesn’t yet exist so far as last expressions are concerned

[Eli_b]

Thanks for the help guys. I made a row with a uniqueID. Im trying to make this app as simple as possible for the volunteers that are not a fan of technology. The spreadsheet theyre using is a mess and time consuming to keep updated. Would it be a bad idea to use this for our Identifier for each dog? Right now i just have a bunch of empty profiles with unused L#s. I know if they have to go back and forth to see what the next l# is especially when adding a large litter, theyre not going to give up the spreadsheet. =ARRAYFORMULA( IFS( ROW(C:C) = 1, “L#”, LEN(C:C) = 0, IFERROR(1/0), ROW(C:C) > 1, “L20-” & ROW(C:C) + -1 ) )

[AL_Shaikh_Real_]

Hello Everyone,

I badly need help.

I want to make sure that no duplicate data entry occurs

The fields in each row are as follows: (State, City, House Number)

Example

State	City	House Number	Duplicate (y/n)
Dhofar	Salalah	234	N
Dhofar	Hafa	234	N
Dakhiliyah	Salalah	234	N
Dhofar	Salalah	234	Y ( Duplicate)

[Steve]

ISBLANK(
  FILTER(
    "table",
    AND(
      ISNOTBLANK([State]),
      ([State] = [_THISROW].[State]),
      ISNOTBLANK([City]),
      ([City] = [_THISROW].[City]),
      ISNOTBLANK([House Number]),
      ([House Number] = [_THISROW].[House Number])
    )
  )
)

[AL_Shaikh_Real_]

Thank you so much, you saved me, man

[AL_Shaikh_Real_]

The formula works fine when add a new record. When I modify it give me an error

[Steve]

Oops! Yep! I missed a line! Try the following:

ISBLANK(
  FILTER(
    "table",
    AND(
      ISNOTBLANK([State]),
      ([State] = [_THISROW].[State]),
      ISNOTBLANK([City]),
      ([City] = [_THISROW].[City]),
      ISNOTBLANK([House Number]),
      ([House Number] = [_THISROW].[House Number]),
      NOT([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
    )
  )
)

[AL_Shaikh_Real_]

How about this it worked with me. is it correct?

ISBLANK(
FILTER(
“table”,
AND(
ISNOTBLANK([State]),
([State] = [_THISROW].[State]),
ISNOTBLANK([City]),
([City] = [_THISROW].[City]),
ISNOTBLANK([House Number]),
([House Number] = [_THISROW].[House Number])
)
)
- LIST([_THISROW])
)

[Steve]

Looks good to me!

[AL_Shaikh_Real_]

Thank you, man, you are a savior

[Jairon]

this save a ton of work! thanks!