Avoid duplicates in subform

Questions related to customising nuBuilder Forte with Javascript or PHP.

Avoid duplicates in subform

Postby Tinka » Tue Mar 05, 2019 1:00 am

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
Tinka
 
Posts: 72
Joined: Mon Feb 24, 2014 11:28 pm

Re: Avoid duplicates in subform

Postby kev1n » Tue Mar 05, 2019 1:23 am

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
 
Posts: 242
Joined: Mon Oct 15, 2018 2:13 am

Re: Avoid duplicates in subform

Postby kev1n » Tue Mar 05, 2019 1:30 am

The second question is not trivial. Probably one would have to remove or add the select values with Javascript.
kev1n
 
Posts: 242
Joined: Mon Oct 15, 2018 2:13 am

Re: Avoid duplicates in subform

Postby Tinka » Tue Mar 05, 2019 11:40 pm

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
 
Posts: 72
Joined: Mon Feb 24, 2014 11:28 pm

Re: Avoid duplicates in subform

Postby Tinka » Wed Mar 06, 2019 12:30 am

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 ) {
Tinka
 
Posts: 72
Joined: Mon Feb 24, 2014 11:28 pm

Re: Avoid duplicates in subform

Postby admin » Sun Mar 10, 2019 8:32 am

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
admin
Site Admin
 
Posts: 3108
Joined: Mon Jun 15, 2009 9:53 am


Return to Custom Code