How to write script to avoid duplicate record creation before record submit using suitescript 2.0

I want to create a user event script to avoid custom record creation if record is duplicate.

Also this custom record is being attached to another custom record. I have to stop from creating and attaching custom duplicate record.

Please Help!!

Beginner Asked on February 23, 2023 in SuiteScript.
Add Comment
10 Answer(s)

Are the two fields globally unique, or just unique for the record they are attached to?  If they are globally unique, you could combine both field values into a single string and set that as the external id, assuming you aren’t already setting a different value in the externalid.  Netsuite will prevent duplicate creation and throw a DUP_CSTM_RCRD_ENTRY error.  If the two values are not globally unique, then maybe a combination of those two values and the internal id of the record they are being attached to would work instead.  For example:  custrecord_field1 + ‘~’ + custrecord_field2   or   otherRecord.id + ‘~’ + custrecord_field1 + ‘~’ + custrecord_field2, using whatever separator character makes sense.

Beginner Answered on February 23, 2023.
Add Comment

What is your criteria to determine whether it is a duplicate?

Intermediate Answered on February 23, 2023.
Add Comment

There are 2 fields on custom record, if user tries to create custom record with same field values, then it will be duplicate record.

Beginner Answered on February 23, 2023.
Add Comment

In beforeSubmit you can run a search and check that there is no existing record. Would that work for you?

Intermediate Answered on February 23, 2023.
Add Comment

Can you put code here please?

Beginner Answered on February 23, 2023.
Add Comment
const beforeSubmit = (scriptContext) => {
    let field1 = scriptContext.newRecord.getValue({fieldId: 'custrecord_field1'});
    let field2 = scriptContext.newRecord.getValue({fieldId: 'custrecord_field2'});
let existing_record = false;
search.create({ type: 'customrecord_my_record', columns: ['internalid'],
filters: [
['custrecord_field1', search.Operator.IS, field1],
'and',
['custrecord_field2', search.Operator.IS, field2],
]
}).run().each((result) => {
existing_record = true;

return false;
})
if (existing_record) {
throw error.create({...})
}
}
Intermediate Answered on February 23, 2023.
Add Comment

You need to define your function before you call it.

Declaring it outside of beforeSubmit keeps things nice and neat:

/**
 *@NApiVersion 2.x
 *@NScriptType UserEventScript
 */
define(['N/record', 'N/email', 'N/url', 'N/runtime', 'N/search', 'N/ui/serverWidget'],
function(record, email, url, runtime, search, serverWidget) {
    function CheckDuplicate(){
        var res=[];
        var customrecord_gbs_tax_rule_expt_nexusSearchObj = search.create({
            type: "customrecord_gbs_tax_rule_expt_nexus",
            filters:
                [
                ],
            columns:
                [
                    search.createColumn({name: "internalid", label: "Internal ID"}),
                    search.createColumn({name: "custrecord_gbs_tren_expt_nexus", label: "Exempt Nexus"})
                ]
        });
        var searchResultCount = customrecord_gbs_tax_rule_expt_nexusSearchObj.runPaged().count;
        //log.debug("expensereportSearchObj result count",searchResultCount);
        // alert('Expenses Count: '+searchResultCount);
        expensereportSearchObj.run().each(function(result){
            // .run().each has a limit of 4,000 results
            var category=result.getValue({name:"expensedate"});
            var exemptNexus =result.getValue({name:"expensecategory"});
            //if(result.id != recordId){// Exclude the current record
            res.push({
                'category':category,
                'exemptNexus':exemptNexus
            });
            //}
            return true;
        });
        return res;
    }
Intermediate Answered on February 23, 2023.
Add Comment

@scottvonduhn – A good suggestion too!

Intermediate Answered on February 23, 2023.
Add Comment

Thanks,

I have written code in 2.0. But record saves even if record is duplicate. Could you understand what is wrong here?

 

/**

 

 *@NApiVersion 2.x

 

 *@NScriptType UserEventScript

 

 */

 

 define([‘N/record’, ‘N/email’, ‘N/url’, ‘N/runtime’, ‘N/search’, ‘N/ui/serverWidget’,’N/error’],

 

  function(record, email, url, runtime, search, serverWidget,error) {

 

 

 

  function beforeSubmit(scriptContext){

 

    if(scriptContext.type === ‘create’ || scriptContext.type === ‘edit’ ){

 

      var rec=scriptContext.newRecord;

 

      var recordId=rec.id;

 

      log.debug(“recordId”,recordId);

 

          var dupList = CheckDuplicate();

 

      log.debug(“dupList”,dupList);

 

            var isDuplicate =false;

 

            for(var i=0; i<dupList.length; i++)

 

            {

 

 

 

                    var category=rec.getValue({ fieldId:’custrecord_gbs_tren_cat’});

 

                    var exemptNexus=rec.getValue({ fieldId:’custrecord_gbs_tren_expt_nexus’});

 

                    if(dupList[i].category == category && dupList[i].exemptNexus == exemptNexus){

 

                        isDuplicate = true;  // There is a duplicate line.

 

                         return false;

 

 

 

 

 

                }//if

 

                else{

 

                  return true;

 

                }

 

                //throw error.create(“You cannot create duplicate record”);

 

      }//for

 

    }//if close

 

  }//beforesubmit close

 

  function CheckDuplicate(recordId){

 

    var res=[];

 

    var customrecord_gbs_tax_rule_expt_nexusSearchObj = search.create({

 

      type: “customrecord_gbs_tax_rule_expt_nexus”,

 

      filters:

 

      [

 

      ],

 

      columns:

 

      [

 

         search.createColumn({name: “custrecord_gbs_tren_cat”, label: “Category”}),

 

         search.createColumn({name: “custrecord_gbs_tren_expt_nexus”, label: “Exempt Nexus”})

 

      ]

 

     });

 

   var searchResultCount = customrecord_gbs_tax_rule_expt_nexusSearchObj.runPaged().count;

 

        //log.debug(“expensereportSearchObj result count”,searchResultCount);

 

       // alert(‘Expenses Count: ‘+searchResultCount);

 

       customrecord_gbs_tax_rule_expt_nexusSearchObj.run().each(function(result){

 

            // .run().each has a limit of 4,000 results

 

 

 

         var category=result.getValue({name: “custrecord_gbs_tren_cat”, label: “Category”});

 

         var exemptNexus =result.getValue({name: “custrecord_gbs_tren_expt_nexus”, label: “Exempt Nexus”});

 

         if(result.id != recordId){// Exclude the current record

 

            res.push({

 

                ‘category’:category,

 

                ‘exemptNexus’:exemptNexus

 

            });

 

         }

 

            return true;

 

        });

 

         return res;

 

}//function dupcheck

 

  return {

 

    beforeSubmit:beforeSubmit

 

}

 

});

 

Beginner Answered on February 23, 2023.
Add Comment

One thing to be careful of, this User Event runs for both Create and Edit, but you probably only want or need to prevent a duplicate in Create mode.  I’d still recommend the use of the external id just for the simplicity of it.  Two ore more user events could run at nearly the same time, and there could be a race condition, where multiple UE scripts find no existing record with the two values, so they all proceed to create their records regardless.  The problem is that time passes between checking for the existing record, and the eventual saving of the record.  To ensure it never created duplicates, the check and save would have to be a single operation, but there is no API in SuiteScript for that.  The externalid approach is the only way I know of that avoids the possibility of race conditions for this kind of scenario.  It is very difficult to achieve that in SuiteScript, unless you are using a scheduled script where only one deployment can run at a time.  The best you can do otherwise is to prepare the record, then do the duplicate check at the very end, to shorten the time duration before the record is saved as much as possible.

Also, the saved search in the code has no filters, which means it will take longer to run, and then even more time to loop through the results.  You would instead want a search that only looked for records with the two values you want to find.  Then, you just need to check if there were any results at all.  For the duplicate check to be successful, the function has to be as efficient as possible, because even then there is still a race condition, but you can limit the impact of it.

Beginner Answered on February 23, 2023.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Become a Sponsor   Become a Sponsor