Subform: Stop Duplicates

[ruiascensao]

Hi,

It seems that this option "Stop Duplicates" does not work in subforms.

Is there other option that I can use?

Thanks!

BR
Rui

[massiws]

Rui,

you're right, but this can be done using AJAX to check database.

You can adapt this code to your work with few changes:

Code: Select all

/*
 * Check field presence in database
 */
function checkField(fieldName)
{
    var num = $("#"+fieldName).val();
    if (num.length == 0) {
        $("#htmlResponse").html("");
    } else {
        // Search DB via AJAX call
        $.ajax({
        type: "GET",
        url: "/path/to/checkField.php",
        data: "f="+fieldName+"&p="+num,
        dataType: "html",
        success: function(msg){
            var offset = $("#"+fieldName).offset();
            var ot = offset.top;
            var ol = offset.left;
            $("#htmlResponse").html(msg);
            $("#htmlResponse").css({
                "position"      :"absolute",
                "top"           :ot - 90,
                "left"          :ol + 150,
                "color"         :"red",
                "font-weight"   :"bold"
          });
        },
        error: function(){
            $("htmlResponse").html("No AJAX call!");
        }
      });
    }
}

In my case I have only to alert user if a value is already in database, but you can stop the save inserting the function in Before Save and returning false on AJAX success.

Hope this helps.

[ruiascensao]

Hi massiws,

Thanks!
Could you please share the checkField.php?
I'm not an php developer.

Thanks again.
BR
Rui

[massiws]

Rui,

happy to share my work, hoping to be helpful (and someone can improve it).

This is what I've done:
1) create an html object called htmlResponse on the form with this in html tab:

Code: Select all

<div id="htmlResponse"></div>

2) add this code in On Blur field of the object to control:

Code: Select all

checkField("client", "cli_phone");    // change these according to your needs

3) add this code in Custom Code > Javascript:

Code: Select all

/*
 * Check field value in database
 * 
 * @param   string  tableName   Table name to search in
 * @param   string  fieldName   Field name to search in
 * 
 * @return  string  Text message is displayed in 'htmlResponse' html object
 */
function checkField(tableName, fieldName)
{
    var fieldValue = $("#"+fieldName).val();
    if (fieldValue.length == 0) {
        $("#htmlResponse").html("");
    } else {
        // Search DB via AJAX call
        $.ajax({
            type: "GET",
            url: "/db/yourDBName/checkField.php",
            data: "t="+tableName+"&f="+fieldName+"&v="+fieldValue,
            dataType: "html",
            success: function(msg){
                var offset = $("#"+fieldName).offset();
                var ot = offset.top;
                var ol = offset.left;
                $("#htmlResponse").html(msg);
                $("#htmlResponse").css({
                    // set some styles for returning text message
                    "position"      :"absolute",
                    "top"           :ot - 90,
                    "left"          :ol + 150,
                    "color"         :"red",
                    "font-weight"   :"bold"
                });
            },
            error: function(){
                $("htmlResponse").html("No AJAX call!");
            }
      });
    }
}

4) create a file checkField.php in db/yourDBName folder whit this code:

Code: Select all

<?php
/**
 * Check if field value already exist in database
 * 
 * @param   string  $t  Table name to search in
 * @param   string  $f  Field name to search in
 * @param   string  $v  Value to search
 * 
 * @return  string      Text message if value is found
 */

// Text message to return if value is found
define('MSG_ON_FOUND', 'This value is already in database.');
$msg = '';

$table = isset($_GET['t']) ? filter_var($_GET['t'], FILTER_SANITIZE_STRING) : '';
$field = isset($_GET['f']) ? filter_var($_GET['f'], FILTER_SANITIZE_STRING) : '';
$value = isset($_GET['v']) ? filter_var($_GET['v'], FILTER_SANITIZE_STRING) : '';

if ($table != '' && $field != '' && $value != '') {
    // Connect to database
    require 'config.php';
    $DB = new mysqli($DBHost, $DBUser, $DBPassword, $DBName);
    if ($DB->connect_errno) {
            $msg = 'Connection error (' . $DB->connect_errno . ') '
                . $DB->connect_error;
    } else {
		// Search field value in DB
        $query = "SELECT COUNT($field) FROM $table WHERE $field = '$value' ";
		$res = $DB->query($query);
		$num = $res->fetch_row();
		$num = $num[0];
        
		if (!is_null($num) && $num > 0) {
			$msg = MSG_ON_FOUND;
		}
        
		/* Free result set and close connection */
		$res->close();
		$DB->close();
	}
}

print $msg;

?>

If a duplicate value is found in database, user is immediately alert when he leaves (blur) the field.

Message shown if the value is found in database

result.png (5.23 KiB) Viewed 3899 times

That's all!

But if you want to stop duplicates before save a better and simpler way is to check all values in JavaScript > beforeSave() function.
In this case, insert this in Costom Code > BeforeSave (change the two parameters according to your needs):

Code: Select all

function nuBeforeSave()
{
    // change this two parameters according to your needs
    if ( !isDuplicateEntry('your_subform_name', 'your_field_name') ) {
        return true;
    }
}
/*
 * Check duplicate field in a subform
 * 
 * @param   string  pSubformName    The name of the subform
 * @param   string  fieldName       The field name
 * 
 * @return  boolean                 True if a duplicate value is found
 */
function isDuplicateEntry(pSubformName, fieldName)
{
    // Creates an array of the prefixes used for each row of the subform
    var rowID = nuSubformRowArray(pSubformName, true);

    // check if there is a duplicate value in subform field
    tempArr = Array();
    for (var i = 0 ; i < rowID.length; i++) {
        // get the value of the field
        fieldValue = $("#"+rowID[i]+fieldName).val();

        if (tempArr.indexOf(fieldValue) >= 0) {
            alert('Duplicate entry!');
            return true;
        } else {
            tempArr.push($("#"+rowID[i]+fieldName).val());
        }
    }
    
    return false;
}

Hope this helps.
Max

[ruiascensao]

Hi massiws,

Thanks a lot for your help!

[admin]

.