I have created a text from an image reader for a client who wants to run a competition with shoppers uploading their till slips.
It works perfectly, but now I have to ensure that there are no duplicate till slip entries, can anyone please advise on how I can achieve this?
Thank you in anticipation!
Can you share a little bit more about the process and user experience please?
How are these slips added to your App, and how do you define a duplicate?
And at what point in the process do you want to detect duplicates?
Most importantly, do you want to detect duplicates before data is saved, and stop it?
Or do you just want to be able to detect any duplicates that have been entered and flag them in some way?
I used the Cloud Vision integration with the API key to extract the text from the image.
The shopper enters their name and surname, next field email address, next field mobile number then uploads the till slip.
What I need is for the data to be checked and compared before it is saved to the database to preserve capacity and for an error message to pop out stating it’s a duplicate capture.
Okay, in that case you will need to use a Custom Form, and have the Cloud Vision results written to a User Specific column. You can then extract the value that you need and use it to build a relation to your existing data. If that relation is not empty, then you have a duplicate and you can present an error message and prevent form submission.
Thank you very much for the advise Darren, much appreciated!
I will try it and let you know if I came right…
I do something similar in my app. In a custom form connected to a Helper Table, I use Cloud Vision to OCR the receipt. I then use OpenAI to extract the Vendor Name and Transaction ID #.
I then create a relation between the extracted Vendor Name in my Helper Table and my Receipts Table, which stores all the receipts that have been previously uploaded including the Vendor Name and respective Transaction ID #. In my Helper Table, I use a Lookup to show all Transaction ID #s previously uploaded for that Vendor.
Lastly, I use an ITE to determine if the Lookup contains that extracted Transaction ID. If so, it’s considered a duplicate and I write True. If True, I show a hint component stating its a duplication and also hiding my Submit button.
You could just create a relation between Transaction ID #s as well, but different vendors could theoretically have the same Transaction ID # and thus not a true duplicate.
I think you could replace all that with a single Query column?
Filter conditions would be something like:
- Vendor Name is This Row → Vendor Name, AND
- Transaction ID is This Row → Transaction ID
And then show your hint component when the Query column is not empty?
Mmm that sounds like a great idea! I use many queries but didnt think of it when I created this helper table
One problem I did run into was that I have row owners in my receipt table to protect that info, so I am seeing that multiple users can upload the same receipt, but still prevents the same user to upload duplicates. Any idea on a workaround for that?
Without row owners the above solution works perfectly
There is no data editor magic you can use to see data that isn’t there, I’m afraid
The only thing I can think of would be to use the Query Tables API. But that would only be an option if you have Business/Enterprise, AND the table in question is a Big Table. And it would also introduce a delay, so probably not a good option anyway
Probably storing a separate table of vendor and transaction IDs in Make storage, but it introduces more steps to your process.
I’m not coming right with this, too complicated for me to understand.
Is there an easier way to detect a duplicate AFTER the entry has been submitted to the database so that we can just simply ignore the entry and un-validate the slip?
You could use the same method (described above) with a relation column or query column to find matching rows. Then use a single value column to return the RowID from the relation. Then create an IF column that compares the RowID in the row to the RowID from the single value column. If they match, return ‘true’. Change your logic to only pay attention to rows that have an IF column value of true, or checked.
We are on a Business plan and do have Big Queries activated Darren.
I recreated the app in Big Query and must now find out where and how to get the Query Tables API.
Kindly point me in the right direction, thanks!
Big Query and the Query Tables API are two completely different things. If you happen to be using Big Tables, you can run filtered queries (SQL style) through the API. See below:
There is no way to protect only certain colulmns using Glide Tables, so we can only show select columns to a “Row Owner” while allowing other columns to be publicly available, correct?
Because ownership is determined on the row level, I believe a row owner would have access to all columns that you don’t use the “protect column” feature on. The protected column wouldn’t be available for anyone.
I guess what I was meaning was something like Row Owners, but only protecting a few columns.
For example, if I had an Email Column and a Comment Column, protecting the Email Column while making the Comment Column viewable to anybody. Basically like a hybrid Row Owner where select columns are protected and others are not.
Nope. It’s all or nothing when it comes to Row Owners.