Jump to content
  • 0

Check for duplicates (not unique)



I would like to have Caspio alert a user if they are about to enter a duplicate appointment on a particular day for a particular resource.  Basically, any time a record is added or updated from a data page (form, calendar details, report listing).  If a row exists in the table for this [@Resource] and [@Date] (and it is not the current row in the case of a record being updated), then give me a message telling me that there may be a duplicate and I should go look.  If not, then no message is needed.  The table does allow duplicates, but we want to give the users a warning when one is potentially created.  Is this possible?

Link to comment
Share on other sites

9 answers to this question

Recommended Posts

  • 0


You can create a formula field in the table and string the fields together, then mark that formula field as unique. Then modify the message for the unique value (localization message element 151) to display a custom message saying you have already submitted this appointment etc. Of course this warning message will only appear when they try to submit the form, not as they are selecting the fields.

Link to comment
Share on other sites

  • 0
On 4/2/2018 at 9:40 AM, Empowering said:

Thanks Carlson!  That sounds like it will provide a hard stop rather than a just a warning message still allowing the user to continue.  Right? 

To further explain on this point, it would depend on how you have everything set.

For example, if your table has three fields like Date, Time, Person, you can combined all these to be a unique formula field.

So if Admin 1 books Client 1, let's say, 1/1/2018 at 9:00am with John, then when Admin 2 tries to book Client 2 at the same time, they will get an error because the new entry will attempt to create a duplicate record.

Now, you have to think about your database critically and think of everything that a user can do to bypass your failsafe(s). For example, if Admin 2 decided to do 1/1/2018 at 9:01am with John then you have a totally new unique field with the added minute. You also have to be careful on your date/time fields since Caspio has options for Date and Date & Time. Date & Time fields are BAD for creating unique fields because they can track up to the second something is recorded.

Link to comment
Share on other sites

  • 0

Thanks everyone!  We really didn't want to set a unique field of any sort because of the ability to get around that, and we really don't want a hard stop for any reason.  So, instead of a pop up message, we created a table that checks for duplicates and the records are populated from a triggered action on our calendar table using an inner join on the two tables.  When that condition exists, a record is added to the dupes table and our managers can run a report to see if there are any potential duplicates.  Then, they check off that they've verified the record and that record is deleted.  Not perfect, but definitely does the trick.

Link to comment
Share on other sites

  • 0

Hello! If you want to highlight duplicate records/columns in the Results Page, you can use this workaround:

1. Insert a Calculated Field that will check whether the record (specific field) has duplicates or not. The formula would be:

THEN 'Has Duplicate'
'No Duplicate'

This formula uses a CASE WHEN statement that will determine if the COUNT of the field is more than 1. More than 1 means that the record has duplicates.

- https://howto.caspio.com/function-reference/#:~:text=Return-,Case,-Evaluates a list
- https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/

2. Insert an HTML Block that will highlight the column. Insert this code:

<div id="visi[@cbRecordIndex#]"></div>


var isi = document.getElementById("visi[@cbRecordIndex#]");

if('[@calcfield:1]' == 'Has Duplicate'){
isi.parentNode.parentNode.querySelector("td:nth-child(4)").style.cssText = 'background: yellow';


Make sure to disable the HTML Editor on the Advanced tab before pasting the code: https://howto.caspio.com/datapages/datapage-components/disabling-html-editor-in-datapage-header-footer-and-html-blocks/

The code means that it will check the value of the Calculated Field and highlight the specific column 4 if it meets the criteria. You can change the number 4 based on the arrangement of columns. To know the number, you can select the column and then copy its selector. For more information: https://www.scrapingbee.com/tutorials/how-to-extract-css-selectors-using-chrome/#:~:text=The process is very simple,and click on Copy selector. 

3. Hide the column Calculated Field. The solution comes from this post: 

I hope this helps!

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...