Stop Duplicate Entry

Hi,
I want to prevent user to enter duplicate entry. I have two criteria. First if user id and date (given by user) match database(google sheet) and second if both user id & date are in same row. If data match and in same row that means data already submitted and user gets alert “Data already exists.” If not updated database.

2 Likes

You are already spelling out the logic of your project. Now put this logic in Thunkable blocks and show what you tried if you did not get the expected results.

2 Likes

How to get row id that contains both user id & date provided by the user. And use that row id to call alert. I am new to thunkable so I don’t know all the blocks yet.

@muneer I can start a loop and it can search for duplicate data but I don’t know how to stop this loop when the list is over and if a duplicate is not found what to do. I have shared my project link and my database screenshot. this app is meant to use for daily report submission. where a team of people will submit their report So the same user Id would have multiple reporting dates. I need help to solve this as I am unable to understand.

https://x.thunkable.com/copy/24c5d314936fe18c45016b3b15541ec9

Am I understanding this correctly?

  1. If the user enters data that doesn’t exactly match all of the row data, it should be allowed
  2. If the user enters data that exactly matches all of the row data, it should not be allowed

And if that’s true, is this also true?

  1. A user who entered 4211102193 Dec 15, 2022 true Dec 15, 2022 Dec 15, 2022 for the columns in the screenshot would NOT be allowed to enter that as a new r

Because if that’s also true, I think you can just compare row objects and loop through them which might simplify this process.

I using google sheet as my app database. I want to check if any single row in my database contains “User Id” and “Dates”.

Background

Which block should I use for checking any single row id at a time also looking for 3 variables in three different column? I am new to thunkable so I have yet to understand all the blocks. If anyone can visually show me that would greatly help my project.

You can use the Does List Contain block in the List drawer (see documentation here) and List of Values block (see documentation here) in the Data Sources drawer to check each column for certain values.

If you have trouble, post a screenshot of your blocks and someone may be able to help.

@tatiang This is how I’m trying to solve but it does not work. I don’t know which block allows me to check one row at a time for multiple columns (such as “User Id” and “Reporting date”). like after clicking submit button program gets to the database and looks in row number 1 for “user Id” in the User Id Column and “Reporting Date” in the Reporting Date column. If a match is found, show the alert “Data already exists” or check row number 2 and It will continue like this.

Okay, those details are helpful. You would do this:

Count with i from 1 to number of rows in [data source]
…if get list item #i from [list of values in APP in Database in UserId] = [stored variable SignIn_UserId]
…break out of loop

And then you can use i as the value of the row # where the user id was found. You can also access other columns’ data once you have that row #.

Edit: Sorry, there’s an easier way! Use the [find first occurence of “”] block:

Insert your [list of values] block where the gear/list block is. Insert your [stored variable SignIn_UserId] where “b” is.

That block will give you the row # you need without requiring a loop. Assign that block’s value to a variable and then use that variable for the row # as I described above.

@tatiang I can do the search part and match part as shown in the photo. But how can I stop if not found and create a new row in the database?

@tatiang If I do what is shown in this photo program does not respond.

Those screenshots are too small for me to see. But to answer your question, if the row # variable is still 0 when the loop completes or if [find first occurrence] returns 0, then you know that the user ID was not found.

@tatiang I can start a loop and it can search for duplicate data but I don’t know how to stop this loop when the list is over and if a duplicate is not found what to do. I have shared my project link and my database screenshot. this app is meant to use for daily report submission. where a team of people will submit their report So the same user Id would have multiple reporting dates. I need help to solve this as I am unable to understand.


https://x.thunkable.com/copy/24c5d314936fe18c45016b3b15541ec9

@conroy I think it probably makes sense to merge this topic with Stop Duplicate Entry.

1 Like

This topic has been merged with: What blocks should I use to look for "User Id" and "Dates" in my database

My first time merging topics :joy_cat: so posts and times may look a little out of order. Be kind, all.

1 Like

@tatiang Thank you for your tremendous support.

  1. If the user enters data that doesn’t exactly match ONLY “USER ID” AND “DATE” (REGARDLESS OF WHETHER OTHER COLUMNS IN THE DATABASE SUCH AS ON LEAVE/ON DUTY; ON LEAVE FROM; ON LEAVE TO MATCH OR NOT) of the row data, it should be allowed
  2. If the user enters data that exactly matches ONLY “USER ID” AND “DATE” of the row data, it should not be allowed.

Loop has to stop where the database row ends.

And if that’s true, is this also true?

  1. A user who entered UDER ID: 4211102193 DATE: Dec 15, 2022 for the columns in the screenshot would NOT be allowed to enter that as a new row.
  2. Else create a new row.