Solution to Prevent Duplicate Entry

Is there a way to prevent users from add duplicate data in to a form?

For example, if they are adding a name - “Bob” - but “Bob” already exists in that column then they get a message asking them to enter a different name.

I’ve see a couple of threads that touch on this, but did not see a clear solution.

Thanks in advance!

2 Likes

To do this, we must make a user-specific column outside a form, make a relation from that column to the list of usernames already existed. If relation is not empty then tell them to pick another name, else show them the form button to collect that entered name via columns component.

1 Like

@ThinhDinh - would you mind expanding on this a little please?

My situation: I am using an allowed email list to restrict access to my app. I would like to allow existing users to “invite” new users by adding to the allowed email list, but I want to prevent them from creating duplicate entries. What you’ve described here I think is what I need to do, but I’m struggling to get my head around it

Expect a post from me on how to prevent duplicate entries and update entries using actions before the end of this week.

6 Likes

Was there ever a follow up to this? Facing similar circumstance.

Summary

I was hesitating because it involves adding a lot of user-specific columns, and may not be useful for cases with big forms, but I will make a video anyway, when I have time :cry:

1 Like

I have a working implementation of this.
I’ll post a summary later today.

2 Likes

Actually, @Eric_Penn have a look at this thread. What I have is essentially what was discussed there.

2 Likes

Here is a working solution for preventing duplicate entries as well as giving you the ability to extract the first part of an email to use as user name. I did this on the user profiles sheet as that is where it’s most needed.

3 Likes

Anyone is welcome to make a copy

1 Like

Thank you Wiz! I am currently using a form to capture. Would have to switch to details screen correct?

Summary
1 Like

No…a form you can place on any screen, including inside an inline list. Just create a relation between the form sheet and mNames sheet…and do the LKUPs I carried out on the uP. The form is not our concern…its the sheet to which the form will be writing the info. Once the relation is created you can then pull up the same ITE on the form to indicate whether name already taken.

Oh lol…damn! might not work lol…I did mine on uP…let me get back to you…Form needs to be submitted lol ! Let get back to you…sorry!

Nope, I can’t even get the formula to work on the form…Let me try with another formula…but regardless…for form to show a match it has to have two names on it in the first place…so on a form this will work with the 3rd entry…which is useless to us. I am sorry…I don’t think I can even get the switch formula to work on the form. I will give it a try and get back to you.

Only reason works on uP because we are using a button and not a form

UPDATE
Okay I have good news! I used a different logic for the form…works fine…!

Legendary.

Summary

I need to start using REGEX more. Very powerful thank you!

 ={"username";ArrayFormula(REGEXREPLACE(A2:A,"@(.*)",""))}
1 Like

Yes you should…It is very powerful and useful like the array formula.

I recommend you empty out your rows… :grimacing: The first time I see this error:
image

1 Like

@ThinhDinh Also, how in the world do you have 516 out of 500 rows?!!
image

It will still count row, but what’s visible in the app will be limited to the first 500 rows.

2 Likes

:rofl:

1 Like