Compare in 4 fields for duplicates

[starquest321]

When an agent enters a name I need to compare against A SECOND name field for duplicate. PHPMaker allows me to compare against the field itself - but I don't know how to compare against another field.
What makes maters more complicated is that I need the comparison against first and last name fields.

So for example:

Agent enter name in field:
lead.first
lead.last

But I want to make sure there is no duplicate in
lead.first1
lead.first2

How can that be done?

[danielc]

If you want to prevent duplicate for your last/first name field, you need to do a search in database to see if the record with the name field has added before row inserted. Try this code in Row_Inserting server event:
$count = ew_ExecuteScalar("SELECT count(*) FROM YourTable WHERE YourField1 = '" . ew_AdjustSql($rsnew["YourField1"]) . "' AND YourField2 = '" . ew_AdjustSql($rsnew["YourField2"]) . "'"); // your two fields comparison to prevent duplicate
if ($count >=1)
return FALSE; // cancel insert

If you want to check your field to prevent duplicate in field1 and field 2, use OR:
ew_ExecuteScalar("SELECT ... WHERE ... OR ...");
To check one field:
ew_ExecuteScalar("SELECT ... WHERE ...");

[starquest321]

Excellent. And if I want to allow the ADD - but simply display a warning box?

Warning - please check with admin - a duplicate might exist for record.

[danielc]

Excellent. And if I want to allow the ADD - but simply display a warning box?

From:
if ($count >=1)
return FALSE; // cancel insert

To:
if ($count >=1)
$this->setWarningMessage("xxx"); // warning message will add in addition to success message
return TRUE; // allow insert

[inside83]

Thank you for this but I have a question.
This is my Row inserting event:

// Row Inserting event
function Row_Inserting($rsold, &$rsnew)
{$count = ew_ExecuteScalar("SELECT count(*) FROM my_table WHERE
field1 = '" . ew_AdjustSql($rsnew["field1"]) . "' AND
field2= '" . ew_AdjustSql($rsnew["field2"]) . "' AND
field3= '" . ew_AdjustSql($rsnew["field3"]) . "'"); // your two fields comparison to prevent duplicate
if ($count >=1) {
$this->setWarningMessage("Some message"); // warning message will add in addition to success message
return FALSE; // cancel insert
}
ELSE {
return TRUE;
}
}

and it works great.
I just don't want
'Insert cancelled' message bellow my 'Some message'.
How to NOT show 'Insert cancelled' or customize it?

[arbei]

You can remove the message in "Message_Showing" Server Event.

Read the example in help file topic: "Server Events and Client Scripts" -> "Message_Showing" for more information.

[inside83]

thank you!

[inside83]

actually it doesn't work
I've put

// Message Showing event
// $type = ''|'success'|'failure'|'warning'
function Message_Showing(&$msg, $type) {
if ($type == 'success') {
//$msg = "your success message";
} elseif ($type == 'failure') {
//$msg = "your failure message";
if (strpos($msg, "some standard message") !== FALSE) // The original message contains some keywords you want to replace
$msg = "My message";
} else {
//$msg = "your message";
}
}

And 'My message' doesn't show.
It still shows 'Insert cancelled'.

[inside83]

But this works:

// Row Inserting event
function Row_Inserting($rsold, &$rsnew)
{$count = ew_ExecuteScalar("SELECT count(*) FROM my_table WHERE
field1 = '" . ew_AdjustSql($rsnew["field1"]) . "' AND
field2= '" . ew_AdjustSql($rsnew["field2"]) . "' AND
field3= '" . ew_AdjustSql($rsnew["field3"]) . "'"); // your two fields comparison to prevent duplicate
if ($count >=1) {
$this->setWarningMessage("Some message"); // warning message will add in addition to success message
$this->setFailureMessage("Please choose another X");
return FALSE; // cancel insert
}
ELSE {
return TRUE;
}
}

And now it shows 'Please choose another X' bellow 'Some message' instead of 'Insert cancelled'.

[mobhar]

If you want to obviously hide/remove the "Insert cancelled" message, then simply put the following code in "Page_Load" server event that belongs to the "Add Page":

Language()->setPhrase("InsertCancelled", "");