Adding Unique Data in App

Goal : We are building a sales app. When user update the phone number in the app, system should check if that number is there in database or not if its already there then system should notify user that details exists already if not then user can add number.

How to do this?

@Adviuz Hello again. I do not have an answer for you, just ideas to play with:

(1) Use a match() function to search for the phone number among all the phone numbers previously entered. Match from the beginning of the column to the cell just above the last phone number.

(2) Wrap the match() function inside an iferror() function: if the match() function returns a number then it means you have a duplicate, if there is no match then have the iferror() function return 0 (for instance). 0s will indicate you have a new number, any other positive integer will indicate a duplicate.

(3) This being said, the two points above imply that the latest phone number was submitted and then only tested. This is not your use case, since you want your phone number to be tested first, then submitted or refused.

Hopefully someone else will be able to help you along further.

1 Like

I will try

Adding to this suggestion — if the Phone number is gathered through a form submission, then you could have it submitted to the Phone # column, have the sheet perform the match() and/or iserror function and then use the Glide editor’s if/then column to return either the user’s phone number (if no error), else some error message to indicate a new phone number is needed. In the user’s profile, display this if/then column instead of the main phone # column.

Another thought would require the phone number to be entered on a detail screen (not a form or add screen) and the value written to a user specific column. Then you could use that column to create a relation to the sheet that contains existing phone numbers. On your detail screen, you could then add a rich text message indicating there is a match. You would set the visibility on the rich text component to only show if the relation is not empty. Not sure if this is ideal, but it would tell you instantly instead of waiting for the sheet to do the check and return a result. You could also create a form button with visibility set to only show when the number is not empty and the relation is empty. That way they would have to enter a phone number and it has to not match an existing one before the form button would show. Then you can pass the phone number into the form and the user can continue to fill out any additional information before submitting the form.

3 Likes

Good call! Same logic as @Robert_Petitto’s Meet with Me app for checking for duplicate Date/Times. I actually use this logic quite a bit now and it works great!

@Adviuz if you need a hand, let me know and I don’t mind helping you through it.

2 Likes

@Jeff_Hager @Adviuz
I like this approach. I went to bed thinking that functions in GS are going to create a lag, which in this case we cannot afford to have.

Thanks, Jeff :slight_smile:

1 Like