Avoid duplicates in subform

[Tinka]

Hi
I'd like to avoid duplicate values being saved in the subform rows.
How can I use the nuSubformObject for this? I figure that the entry "columns" in the object could be checked for duplicates - but how do I do that in Javascript?

In nuBuilder Pro it was solved with this code (Javascript of main form):

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;
}

Also, my subform object is a lookup, and I figured out how to filter the lookup for values that already exist in the database, hereby avoiding that a duplicate value can be chosen. But this, of course, does not work while adding/editing the subform values before they are saved.
Sql in Browse of the lookup form:

Code: Select all

SELECT DISTINCT bop_pos
FROM boxposition
Left join boxpos_mm on bop_id = bom_bop_id_FK

WHERE bop_pos NOT IN
(

SELECT DISTINCT bop_pos
FROM boxposition
Left join boxpos_mm on bop_id = bom_bop_id_FK

WHERE bom_ab_id_FK = "#dispID#"
)
GROUP BY bop_pos

[kev1n]

Hi,

This is an adaptation of your duplicate function which works under nuBuilder4:

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 nuSubformRowArray(pSubformName, fieldName) {
    var a = Array();
    var sf = nuSubformObject(pSubformName);
    var column = sf.fields.indexOf(fieldName);
    for(var i = 0; i < sf.rows.length; i++) {
        var value = sf.rows[i][column];
        if(value != '') {
            a.push(value);
        }
    }
    return a;
}

function isArrayUnique(arr) {
    return arr.length === new Set(arr).size;
}

function isDuplicateEntry(pSubformName, fieldName) {
    var arr = nuSubformRowArray(pSubformName, fieldName);
    return isArrayUnique(arr) === false;
}

[kev1n]

The second question is not trivial. Probably one would have to remove or add the select values with Javascript.

[Tinka]

Thanks kev1n, this works, I added just a line to notify the user.

Code: Select all

function nuBeforeSave() {
    // change this two parameters according to your needs
    if(isDuplicateEntry('bom_id','bom_bop_id_FK')) {
        alert('Duplicate entry in subform!');
        return false;
    } else {
        return true;
    }
}

We only need to check those subformlines not checked for deletion for duplicates - in which function can this be added? Column nuDelete.

In nuBuilder Pro we could use nuSubformArray('sf', true).

The second question can wait, since it is not possible to save duplicates, but would be nice to filter the lookup for already chosen values and remove those by Javascript.

[Tinka]

This extra condition in function nuSubformRowArray will only check lines not checked for deletion for duplicates:

Code: Select all

if(value !== '' && sf.deleted[i] === 0 ) {

[admin]

Tinka,

Try this...

Code: Select all

function checkd(subform,column){

    var s       = nuSubformObject(subform).rows
    var checked = s[0].length - 1;
    var d       = [];
    
    for(var i = 0 ; i < s.length ; i++){
        
        var v = s[i][column];
        var t = s[i][checked];
        
        if(d.indexOf(v) != -1 && t == 0){
            console.log( "Duplicate on row " + i);
            return true;
        }
        
        d.push(v);
        
    }
    
    return false;
    
}

Steven