Prevent Form from Adding Duplicate

💬 App Building
1
  1. My goal: Develop a reusable code pattern to prevent a form from adding a duplicate record where the duplication is not detectable from the primary key.
  2. Issue: For example, we want to add vendor invoices to a Retool DB table, but we don't want to add any vendor invoice more than once. When a user clicks "Submit", how do we test whether the invoice is a duplicate where duplication means the same vendor and invoice number.
  3. Steps I've taken to troubleshoot: The pattern I've developed is to:
  • Use a JS Query as the action to occur when the Submit button is clicked
  • In the JS Query, run a SQL query that looks for a record with the vendor ID and invoice number entered in the form
  • If the SQL query finds a record, then show a pop up message that the invoice record already exists
  • If the SQL query does not find a record, then add the form data to the DB

Does this reflect a best practice? Is there a better way given that Retool DB doesn't have composite, non-primary keys?

Thanks

2

Hi @haj - you can create composite keys in RetoolDB. Something like this

image
image822×582 48.4 KB

1 Like
3

Thanks, yes. As the primary key, but not as a secondary key. That's what I need, or another way to prevent duplicates.

4

Ok. I think in that case what you have described above will work nicely