Check for duplicates (not unique)

[Empowering]

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?

[ChrisCarlson]

Hello,

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.

[Empowering]

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? 

[jhernandez]

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.

[Empowering]

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.

[NailDyanC]

Just want to add and update this post, you may check the links below on a possible workaround to this concern:

 

[Queso]

Hello!

You may check on this another Caspio Forum post if in case you would like to create a Triggered Action setup that avoids creating duplicates on the table: 

 

[RuisiHansamu]

There is a howto article in Caspio that may be helpful here: https://howto.caspio.com/troubleshooting/errors-and-messages/#:~:text=Duplicates values are not allowed,enter a duplicate email address. 

[Flowers4Algernon]

Hello! Maybe this forum post may be helpful as you go along: 

 

[Kurumi]

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:

CASE WHEN (SELECT COUNT(FIELD_NAME) FROM TABLE_NAME WHERE FIELD_NAME=target.[@field:FIELD_NAME]) > 1
THEN 'Has Duplicate'
ELSE
'No Duplicate'
END

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.

References:
- 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>

<script>

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

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

</script>

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!