How to add unique name validation to accounts module

How do I add a custom validation to the Accounts module that prevents save if the name of the account is not unique?

I have been refereing to the Adding Field Validation to the Record View documentation from the Sugar developer guide and have succesfully added a validation function, I just don't know how to query the system to see if there already exists an account with the same name. Can anyone help?

I am on version 7.6 Enterprise.

Here is what I have so far. I created a new file in custom/modules/Accounts/clients/base/views/create-actions/create-actions.js with the following contents:

({
    extendsFrom: 'CreateActionsView',


    initialize: function (options) {
        this._super('initialize', [options]);


        //add validation tasks
        this.model.addValidationTask('check_name', _.bind(this._doValidateUniqueName, this));
    },


    _doValidateUniqueName: function(fields, errors, callback) {        
        var accounts = app.data.createBeanCollection("Accounts");
       accounts.fetch({
          filter: [{'name':{'$equals':this.model.get('name')}}],
       });
        
        if (???) // How do I use the results of the fetch here?
        {
            errors['name'] = errors['name'] || {};
            errors['name'].required = true; // I plan on changing this to a custom error message
        }

        callback(null, fields, errors);
    },
})

The code is called correctly, I just don't know how to use the results of the accounts.fetch to determine if there are any accounts with the same name already.

Parents
  • Hello Brett,

    Step 1: Create an API

    /custom/modules/Accounts/clients/base/api/duplicatename.php

    ---begin source

    <?php

        //if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');       

        //https://community.sugarcrm.com/thread/22999

        class duplicatename extends SugarApi

        {   

          public function registerApiRest()

          {

          return array(

                'duplicatename' => array(

                    // What type of HTTP request to match against, we support GET/PUT/POST/DELETE

                    'reqType' => 'POST',           

                    //set authentication

                    //'noLoginRequired' => false,           

                    // This is the path you are hoping to match, it also accepts wildcards of ? and <module>

                    'path' => array('Accounts','duplicatename'),                           

                    //These take elements from the path and use them to populate $args

                    'pathVars' => array('', ''),           

                    // This is the method name in this class that the url maps to

                    'method' => 'duplicatename',           

                    //short help string to be displayed in the help documentation

                    'shortHelp' => 'Name is unique.',

                    'longHelp' => '',

                ),

            );

          }

          public function duplicatename($api,$args)

          {

          global $sugar_config, $db;

          $name= $args['name'];

          $recordId    = $args['id'];

          //Write the logic to query the database with the same id       

          $duplicateCheckQuery = $db->query("SELECT DISTINCT COUNT(id) AS totalcount FROM [dbo].[accounts] a WHERE a.[id]<>'".$recordId."' AND a.[name]='".$name."' AND a.deleted=0");         

       

          $duplicateCheck = $db->fetchByAssoc($duplicateCheckQuery);

          COUNT'.$duplicateCheck['totalcount']);

          if($duplicateCheck['totalcount'] > 0)

          {   

            return "true";

          } else {

            return "false";

          }   

          } 

        }

    ?>

    ---end source

    STEP 2: Then in your create-actions.js  -->custom/modules/Accounts/clients/base/views/create-actions/create-actions.js

    This is okay but change your name:

    this.model.addValidationTask('name', _.bind(this.doCheckNameDuplicate_Validate, this));

    INSERT CODE IN YOUR CREATE-ACTIONS.JS

    doCheckNameDuplicate: function(fields, errors, callback) { 

    if (!_.isEmpty(this.model.get('name'))) {

    var account_id = this.model.get('id');

    var name = this.model.get('name');

    dataArray = {name : this.model.get('name'), id: this.model.get('id')}

    app.api.call('create',app.api.buildURL('Accounts/duplicatename'),dataArray,{ 

       success:function(response){   

          result=response;  

         if(result == 'true'){   

          errors['name'] = errors['name'] || {};

         errors['name'].custom_message_name = true; 

      }   

    callback(null, fields, errors);   

    },  

    error: function(err){

      app.alert.show('Validate', {

      level  : 'error',

      messages : 'FAILED SERIVED REQUESTED',

      autoClose : false,

    });  

    callback(null, fields, errors); 

    }

    });

    } else {

    callback(null, fields, errors);

    }

    },

  • Peter,

    Thank you for your detailed answer with code. It was very helpful. In the end I didn't implement it exactly as you laid out but you got me pointed in the right direction. I was able to leverage the built in app.data.createBeanCollection method and fetched the bean collection records with a filter applied. This approach cut down the step of creating my own custom api REST endpoint.

    To hopefully help others in the future here is all of my code:

    Step 1.

    Create a custom create-actions controller

    ./custom/modules/Accounts/clients/base/views/create-actions/create-actions.js

    ({
        extendsFrom: 'CreateActionsView',
    
    
        initialize: function (options) {
            this._super('initialize', [options]);
         
            //add custom message key
            app.error.errorName2Keys['cstm_accountNameExists'] = 'CSTM_ERROR_ACCOUNT_NAME_EXISTS';
    
    
            //add validation tasks
            this.model.addValidationTask('cstm_check_account_name', _.bind(this.cstm_doCheckAccountName, this));
        },
    
        cstm_doCheckAccountName: function(fields, errors, callback)
        {     
            var account_name = this.model.get('name');
            if (account_name)
            {
                var accounts = app.data.createBeanCollection("Accounts");
                accounts.fetch({
                    filter: [{'name':{'$equals':account_name}}],
                    success: function(data){
                        if ((typeof(data) !== 'undefined') && (typeof(data.models) !== 'undefined') && (data.models instanceof Array) && (data.models.length > 0))
                        {
                            errors['name'] = errors['name'] || {};
                            errors['name'].cstm_accountNameExists = true;
                        }
                        callback(null, fields, errors);
                    },
                    error: function(err){
                        app.alert.show('Validate', {
                            level  : 'error',
                            messages : 'Unique name validation failed.',
                            autoClose : false,
                        });
                        callback(null, fields, errors);
                    }
                });
            }
            else
            {
                callback(null, fields, errors);
            }
        },
    })
    

    Step 2.

    Create custom error message

    ./custom/Extension/application/Ext/Language/en_us.err_custom_message.php

    <?php
    
    $app_strings['CSTM_ERROR_ACCOUNT_NAME_EXISTS'] = 'Error. Account name already exists.';
    

    Step 3.

    Do a repair/rebuild

    You should now have custom validation when creating an account that will block saving the record if the account name is not unique.

    References:

    http://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_7.6/UI_Model/Views/Examples/Adding_Field…

    Angel's Blog: SugarCRM Quick Hit: SugarBean and JavaScript

  • Found one problem with my solution, the bean collection fetch is restricting the results to only beans with teams that the user belongs to. Is there a way to tell the fetch to ignore team security?

  • So after playing around with this for a while I could never come up with a way to use the out of the box endpoints to fully accomplish the validation. Thanks to Peter's post giving steps on how to accomplish it through a custom API endpoint I was able to create a custom REST endpoint that I was able to use in the create-actions, record view, and list view validations.

    For those interested here is how I accomplished it:

    Step 1 - Create the REST endpoint

    Add a new file at .\custom\clients\base\api\CustomIsDuplicateName.php with the following contents:

    <?php

    class CustomIsDuplicateName extends SugarApi
    {
        // This function is only called whenever the rest service cache file is deleted.
        // This shoud return an array of arrays that define how different paths map to different functions
        public function registerApiRest() {
            return array(
                'getNameExists' => array(
                    // What type of HTTP request to match against, we support GET/PUT/POST/DELETE
                    'reqType' => 'POST',
                    // This is the path you are hoping to match, it also accepts wildcards of ? and <module>         
                    'path' => array('<module>', 'custom_is_duplicate_name', '?'),
                    // These take elements from the path and use them to populate $args
                    'pathVars' => array('module', '', 'name'),
                    // This is the method name in this class that the url maps to
                    'method' => 'getIsDuplicateName',
                    // The shortHelp is vital, without it you will not see your endpoint in the /help
                    'shortHelp' => 'Check if module already contains a record with the same name.',
                    // The longHelp points to an HTML file and will be there on /help for people to expand and show
                    'longHelp' => '',
                ),
            );
        }
       
        function getIsDuplicateName($api, $args)
        {
            require_once('include/SugarQuery/SugarQuery.php');
            $sugarQuery = new SugarQuery();
            //add fields to select
            $sugarQuery->select(array('id', 'name'));


            //fetch the bean of the module to query
            $bean = BeanFactory::newBean($args['module']);


            //add from table of the module we are querying
            $sugarQuery->from($bean, array('team_security' => false));
           
            //add the where clause
            $sugarQuery->where()->equals('name', $args['name']);
           
            if (!empty($args['id']))
            {
                $sugarQuery->where()->notEquals('id', $args['id']);
            }
           
            //set the limit
            $sugarQuery->limit(1);
           
            //fetch the result
            $result = $sugarQuery->execute();
           
            return !empty($result);
        }
    }
    ?>

    This creates an endpoint with an address of http://{site url}/rest/v10/custom_is_duplicate_name/{name}. If a record ID is supplied with the arguments then it will exclude that record from the query. This is done so that if you are editing an existing record it will not count that record as a duplicate of the name.

    Step 2 - Add custom validation to the Create-Actions controller. This validation will run when creating a new account

    Add a new file at .\custom\modules\Accounts\clients\base\views\create-actions\create-actions.js with the following content:

    ({
        extendsFrom: 'CreateActionsView',

        initialize: function (options) {
            this._super('initialize', [options]);
           
            //add custom message key
            app.error.errorName2Keys['custom_nameExists'] = 'CSTM_ERROR_NAME_EXISTS';

            //add validation tasks
            this.model.addValidationTask('custom_check_name', _.bind(this.doCheckAccountNameUnique, this));
        },
       
        doCheckAccountNameUnique: function(fields, errors, callback)
        {
            if (!_.isEmpty(this.model.get('name')))
            {          
                var url = app.api.buildURL('Accounts/custom_is_duplicate_name/' + this.model.get('name'));
                var data = null;
                var callbacks = {
                    success:function(response)
                    { 
                        if(response === true)
                        {  
                            errors['name'] = errors['name'] || {};
                            errors['name'].custom_nameExists = true;
                        }  
                        callback(null, fields, errors);  
                    }, 
                    error: function(err)
                    {
                        app.alert.show('Validate', {
                            level  : 'error',
                            messages : 'Unique name validation failed.',
                            autoClose : false,
                        }); 
                        callback(null, fields, errors);
                    }
                };
                app.api.call('create', url, data, callbacks);
            }
            else
            {
                callback(null, fields, errors);
            }
        },
    })

    The initialize function adds a custom validation task and also registers a custom error message to display when the validation fails.

    Step 3 - Add custom validation to the Record view. This validation will be called when a user edits an existing record.

    Add a new file at .\custom\modules\Accounts\clients\base\views\record\record.js with the following contents:

    ({
        /* because accounts already has a record view, we need to extend it */
        extendsFrom: 'AccountsRecordView',


        initialize: function (options) {
            this._super('initialize', [options]);


            //add custom message key
            app.error.errorName2Keys['custom_nameExists'] = 'CSTM_ERROR_NAME_EXISTS';

            //add validation tasks
            this.model.addValidationTask('custom_check_name', _.bind(this.doCheckAccountNameUnique, this));
        },


        doCheckAccountNameUnique: function(fields, errors, callback)
        {
            if (!_.isEmpty(this.model.get('name')) && this.model.hasChanged('name'))
            {
                var url = app.api.buildURL('Accounts/custom_is_duplicate_name/' + this.model.get('name'));
                var data = {id:this.model.get('id')};
                var callbacks = {
                    success:function(response)
                    { 
                        if(response === true)
                        {  
                            errors['name'] = errors['name'] || {};
                            errors['name'].custom_nameExists = true;
                        }  
                        callback(null, fields, errors);  
                    }, 
                    error: function(err)
                    {
                        app.alert.show('Validate', {
                            level  : 'error',
                            messages : 'Unique name validation failed.',
                            autoClose : false,
                        }); 
                        callback(null, fields, errors);
                    }
                };
                app.api.call('create', url, data, callbacks);         
            }
            else
            {
                callback(null, fields, errors);
            }
        },
    })

    You will notice that this is very similar to the create-actions.js file. The difference is that the record.js file adds additional checking to only call the REST endpoint if the name field has changed and it also adds the records ID to the post data so that it is excluded from the duplicates.

    Step 4 - Add custom validation to the list view. This validation is called when a user does an inline edit to a record on the Accounts list view.

    Add a new file at .\custom\modules\Accounts\clients\base\fields\editablelistbutton\editablelistbutton.js with the following contents:

    ({
        //Extend the Class. 
        extendsFrom:'EditablelistbuttonField', 
       
        _validationComplete: function(isValid)
        {       
            if (!_.isEmpty(this.model.get('name')) && this.model.hasChanged('name'))
            {
                var url = app.api.buildURL('Accounts/custom_is_duplicate_name/' + this.model.get('name'));
                var data = {id:this.model.get('id')};
                var callbacks = {
                    success:function(response)
                    { 
                        if(response === true)
                        {
                            isValid = false; 
                            app.alert.show('Validate', { 
                                 level: 'error', 
                                 messages: 'Name already exists.', 
                                 autoClose: false, 
                            });
                        }                
                        this._super('_validationComplete',[isValid]);
                    }.bind(this), 
                    error: function(err)
                    {
                        app.alert.show('Validate', {
                            level  : 'error',
                            messages : 'Unique name validation failed.',
                            autoClose : false,
                        });                 
                        this._super('_validationComplete',[isValid]);
                    }.bind(this)
                };
                app.api.call('create', url, data, callbacks);         
            }
            else
            {
                this._super('_validationComplete',[isValid]);
            }
        }, 
    })

    Step 5 - Add the custom error message

    Add a new file at .\custom\Extension\application\Ext\Language\en_us.custom_err_name_exists.php with the following content:

    <?php


    $app_strings['CSTM_ERROR_NAME_EXISTS'] = 'Error. Name already exists.';

    Step 6- Do a repair/rebuild.

    Hopefully this helps others who want to accomplish similar behavior.

Reply
  • So after playing around with this for a while I could never come up with a way to use the out of the box endpoints to fully accomplish the validation. Thanks to Peter's post giving steps on how to accomplish it through a custom API endpoint I was able to create a custom REST endpoint that I was able to use in the create-actions, record view, and list view validations.

    For those interested here is how I accomplished it:

    Step 1 - Create the REST endpoint

    Add a new file at .\custom\clients\base\api\CustomIsDuplicateName.php with the following contents:

    <?php

    class CustomIsDuplicateName extends SugarApi
    {
        // This function is only called whenever the rest service cache file is deleted.
        // This shoud return an array of arrays that define how different paths map to different functions
        public function registerApiRest() {
            return array(
                'getNameExists' => array(
                    // What type of HTTP request to match against, we support GET/PUT/POST/DELETE
                    'reqType' => 'POST',
                    // This is the path you are hoping to match, it also accepts wildcards of ? and <module>         
                    'path' => array('<module>', 'custom_is_duplicate_name', '?'),
                    // These take elements from the path and use them to populate $args
                    'pathVars' => array('module', '', 'name'),
                    // This is the method name in this class that the url maps to
                    'method' => 'getIsDuplicateName',
                    // The shortHelp is vital, without it you will not see your endpoint in the /help
                    'shortHelp' => 'Check if module already contains a record with the same name.',
                    // The longHelp points to an HTML file and will be there on /help for people to expand and show
                    'longHelp' => '',
                ),
            );
        }
       
        function getIsDuplicateName($api, $args)
        {
            require_once('include/SugarQuery/SugarQuery.php');
            $sugarQuery = new SugarQuery();
            //add fields to select
            $sugarQuery->select(array('id', 'name'));


            //fetch the bean of the module to query
            $bean = BeanFactory::newBean($args['module']);


            //add from table of the module we are querying
            $sugarQuery->from($bean, array('team_security' => false));
           
            //add the where clause
            $sugarQuery->where()->equals('name', $args['name']);
           
            if (!empty($args['id']))
            {
                $sugarQuery->where()->notEquals('id', $args['id']);
            }
           
            //set the limit
            $sugarQuery->limit(1);
           
            //fetch the result
            $result = $sugarQuery->execute();
           
            return !empty($result);
        }
    }
    ?>

    This creates an endpoint with an address of http://{site url}/rest/v10/custom_is_duplicate_name/{name}. If a record ID is supplied with the arguments then it will exclude that record from the query. This is done so that if you are editing an existing record it will not count that record as a duplicate of the name.

    Step 2 - Add custom validation to the Create-Actions controller. This validation will run when creating a new account

    Add a new file at .\custom\modules\Accounts\clients\base\views\create-actions\create-actions.js with the following content:

    ({
        extendsFrom: 'CreateActionsView',

        initialize: function (options) {
            this._super('initialize', [options]);
           
            //add custom message key
            app.error.errorName2Keys['custom_nameExists'] = 'CSTM_ERROR_NAME_EXISTS';

            //add validation tasks
            this.model.addValidationTask('custom_check_name', _.bind(this.doCheckAccountNameUnique, this));
        },
       
        doCheckAccountNameUnique: function(fields, errors, callback)
        {
            if (!_.isEmpty(this.model.get('name')))
            {          
                var url = app.api.buildURL('Accounts/custom_is_duplicate_name/' + this.model.get('name'));
                var data = null;
                var callbacks = {
                    success:function(response)
                    { 
                        if(response === true)
                        {  
                            errors['name'] = errors['name'] || {};
                            errors['name'].custom_nameExists = true;
                        }  
                        callback(null, fields, errors);  
                    }, 
                    error: function(err)
                    {
                        app.alert.show('Validate', {
                            level  : 'error',
                            messages : 'Unique name validation failed.',
                            autoClose : false,
                        }); 
                        callback(null, fields, errors);
                    }
                };
                app.api.call('create', url, data, callbacks);
            }
            else
            {
                callback(null, fields, errors);
            }
        },
    })

    The initialize function adds a custom validation task and also registers a custom error message to display when the validation fails.

    Step 3 - Add custom validation to the Record view. This validation will be called when a user edits an existing record.

    Add a new file at .\custom\modules\Accounts\clients\base\views\record\record.js with the following contents:

    ({
        /* because accounts already has a record view, we need to extend it */
        extendsFrom: 'AccountsRecordView',


        initialize: function (options) {
            this._super('initialize', [options]);


            //add custom message key
            app.error.errorName2Keys['custom_nameExists'] = 'CSTM_ERROR_NAME_EXISTS';

            //add validation tasks
            this.model.addValidationTask('custom_check_name', _.bind(this.doCheckAccountNameUnique, this));
        },


        doCheckAccountNameUnique: function(fields, errors, callback)
        {
            if (!_.isEmpty(this.model.get('name')) && this.model.hasChanged('name'))
            {
                var url = app.api.buildURL('Accounts/custom_is_duplicate_name/' + this.model.get('name'));
                var data = {id:this.model.get('id')};
                var callbacks = {
                    success:function(response)
                    { 
                        if(response === true)
                        {  
                            errors['name'] = errors['name'] || {};
                            errors['name'].custom_nameExists = true;
                        }  
                        callback(null, fields, errors);  
                    }, 
                    error: function(err)
                    {
                        app.alert.show('Validate', {
                            level  : 'error',
                            messages : 'Unique name validation failed.',
                            autoClose : false,
                        }); 
                        callback(null, fields, errors);
                    }
                };
                app.api.call('create', url, data, callbacks);         
            }
            else
            {
                callback(null, fields, errors);
            }
        },
    })

    You will notice that this is very similar to the create-actions.js file. The difference is that the record.js file adds additional checking to only call the REST endpoint if the name field has changed and it also adds the records ID to the post data so that it is excluded from the duplicates.

    Step 4 - Add custom validation to the list view. This validation is called when a user does an inline edit to a record on the Accounts list view.

    Add a new file at .\custom\modules\Accounts\clients\base\fields\editablelistbutton\editablelistbutton.js with the following contents:

    ({
        //Extend the Class. 
        extendsFrom:'EditablelistbuttonField', 
       
        _validationComplete: function(isValid)
        {       
            if (!_.isEmpty(this.model.get('name')) && this.model.hasChanged('name'))
            {
                var url = app.api.buildURL('Accounts/custom_is_duplicate_name/' + this.model.get('name'));
                var data = {id:this.model.get('id')};
                var callbacks = {
                    success:function(response)
                    { 
                        if(response === true)
                        {
                            isValid = false; 
                            app.alert.show('Validate', { 
                                 level: 'error', 
                                 messages: 'Name already exists.', 
                                 autoClose: false, 
                            });
                        }                
                        this._super('_validationComplete',[isValid]);
                    }.bind(this), 
                    error: function(err)
                    {
                        app.alert.show('Validate', {
                            level  : 'error',
                            messages : 'Unique name validation failed.',
                            autoClose : false,
                        });                 
                        this._super('_validationComplete',[isValid]);
                    }.bind(this)
                };
                app.api.call('create', url, data, callbacks);         
            }
            else
            {
                this._super('_validationComplete',[isValid]);
            }
        }, 
    })

    Step 5 - Add the custom error message

    Add a new file at .\custom\Extension\application\Ext\Language\en_us.custom_err_name_exists.php with the following content:

    <?php


    $app_strings['CSTM_ERROR_NAME_EXISTS'] = 'Error. Name already exists.';

    Step 6- Do a repair/rebuild.

    Hopefully this helps others who want to accomplish similar behavior.

Children
No Data