[Tip] Find duplicate entries

by Enrico Arata (automazione)

This tip is all about the way you can check if a Serial number, social security, VAT number or any other unique code is already used somewere in your table?

You can just use an SQL query and check the number of found records. If any.... your unique code has already been used!!!

var query = 'select companiesid, vat_code, companyname from companies where vat_code = ' + globals.newVatCode;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 1);

if (dataset.getMaxRowIndex() > 0) //Found at least one record
{
    dataset.rowIndex = 1;    // put "focus" on first dataset record
    application.beep();        // warning acoustic sound
    plugins.dialogs.showWarningDialog( "Warning", "VAT number: " + globals.newVatCode + " already in use\nMoving to company: " + dataset[3] ,"Ok"); // Show warning (two line message)
foundset.selectRecord(dataset[1]);    // jumps using primary key value
}

As usual, I hope you will like it,

bye

Enrico

Comments (3)

Karel Broer 2004-11-09

Hi Enrico, Thanks for this great tip! I do have this issue... I want to check of the number that I type into a field (in a portal) allready exist in the same field from other records (in same portal). Is there a neat way to do this (besides a loop script..) ?

Marcel Trapman 2004-11-10

Karel, unless I misinterpret your question that's exactly what Enrico is talking about here. You already know the name of the table and the column so you can easily create a sql statement to check for the existance of that number...

David Workman 2004-11-10

More specifically Karel, modify the SQL statement to have two parameters that need to match to produce a true result (a record found). One for the parent record (parent ID probably) and one for the column you are testing for an existing value. Using Enrico's example, let's assume that there is a portal on a companies layout showing related contacts. To check to see if there is a particular value in a last_name column of the contacts table for the current company record: SELECT * FROM contacts WHERE companiesid = (concatenate current company record ID here) AND last_name = (concatenate value you are checking for here)