For Zoho Services only:


I'm actually part of something bigger at Ascent Business Solutions recognized as the top Zoho Premium Solutions Partner in the United Kingdom.

Ascent Business Solutions offer support for smaller technical fixes and projects for larger developments, such as migrating to a ZohoCRM.  A team rather than a one-man-band is always available to ensure seamless progress and address any concerns. You'll find our competitive support rates with flexible, no-expiration bundles at http://ascentbusiness.co.uk/zoho-support-2.  For larger projects, check our bespoke pricing structure and receive dedicated support from our hands-on project consultants and developers at http://ascentbusiness.co.uk/crm-solutions/zoho-crm-packages-prices.

The team I manage specializes in coding API integrations between Zoho and third-party finance/commerce suites such as Xero, Shopify, WooCommerce, and eBay; to name but a few.  Our passion lies in creating innovative solutions where others have fallen short as well as working with new businesses, new sectors, and new ideas.  Our success is measured by the growth and ROI we deliver for clients, such as transforming a garden shed hobby into a 250k monthly turnover operation or generating a +60% return in just three days after launch through online payments and a streamlined e-commerce solution, replacing a paper-based system.

If you're looking for a partner who can help you drive growth and success, we'd love to work with you.  You can reach out to us on 0121 392 8140 (UK) or info@ascentbusiness.co.uk.  You can also visit our website at http://ascentbusiness.co.uk.

Zoho CRM: Manage a subform using Client Script

What?
This is an article with a snippet of code demonstrating how to manage a subform using a Zoho CRM Client Script.

Why?
A client wanted an automation on-the-fly that when selecting a specific value in a field, a row would be added to a subform contained herein.

How?
Well I won't go in to detail on how to add a client script, this article is just to show the snippet of code used in the client script which will manage a subform.

The module we are applying this client script to is a custom module called "Deployments". The field which triggers it is called "Which_rate_card_is_this_based_off". The expected outcome is the appending of a row to the subform based on the start date (first field in the subform) match in which case it should re-add the row with an "End Date" specified (blank or not).

copyraw
console.log("Joel Lipman - 20221018 -------------------------");

// get some values from the current record
var v_CurrencyCode = ZDK.Page.getField('Currency').getValue();
var v_ConsultantLocation = ZDK.Page.getField('Location').getValue();

// only run the rest if record matches criteria
if (v_CurrencyCode == "GBP" && v_ConsultantLocation == "UK") {

    // get value of trigger field
    var v_RateCard = ZDK.Page.getField('Which_rate_card_is_this_based_off').getValue();

    // get opportunity lookup (related deal) of this record
    let r_DealLookup = ZDK.Page.getField('Opportunity_Name').getValue();
    let v_DealID = r_DealLookup.id;
    let r_DealDetails = ZDK.Apps.CRM.Deals.fetchById(v_DealID);

    // get Deal/Opportunity fields we need to refer to
    var v_OppType = r_DealDetails.Type_of_Opportunity;
    var v_DealRate0 = r_DealDetails.Rate_Year_1_0;
    var v_DealRate1 = r_DealDetails.Rate_Year_1_5;
    var v_DealRate2 = r_DealDetails.Rate_Year_2_0;
    var v_DealRate3 = r_DealDetails.Rate_Year_3_0;
    var v_DealRate4 = r_DealDetails.Rate_Year_4_0;

    // default setting for alerting user
    let b_ShowAlertMessage = true;

    // specific values based on trigger field
    if (v_RateCard == 'New Rate Card (Post Aug 22)' && (v_OppType == 'Client'||v_OppType == 'People') && v_CurrencyCode == 'GBP') {

        // hard-coded values if this criteria met
        v_DealRate0 = 350;
        v_DealRate1 = 390;
        v_DealRate2 = 465;
        v_DealRate3 = 535;
        v_DealRate4 = 590;
        b_ShowAlertMessage = false;

    } else if (v_RateCard == 'Old Rate Card (Pre Aug 22)' && (v_OppType == 'Client'||v_OppType == 'People') && v_CurrencyCode == 'GBP') {

        // hard-coded values if this criteria met
        v_DealRate0 = 345;
        v_DealRate1 = 345;
        v_DealRate2 = 400;
        v_DealRate3 = 475;
        v_DealRate4 = 525;
        b_ShowAlertMessage = false;

    }

    // show advisory
    if (b_ShowAlertMessage) {

        ZDK.Client.showAlert("You have selected a rate without any preset values.  Rates will be taken from the Opportunity record.  If these are blank then enter the rates manually below: Rate Year 1.0, Rate Year 1.5, ...");

    }

    // update the fields (that were blank on the existing record - but now pulled from Opp or from set values)
    ZDK.Page.getForm().setValues({ 'Rate_Year_1_0': v_DealRate0 });
    ZDK.Page.getForm().setValues({ 'Rate_Year_1_5': v_DealRate1 });
    ZDK.Page.getForm().setValues({ 'Rate_Year_2_0': v_DealRate2 });
    ZDK.Page.getForm().setValues({ 'Rate_Year_3_0': v_DealRate3 });
    ZDK.Page.getForm().setValues({ 'Rate_Year_4_0': v_DealRate4 });


    // start subform management
    // *****************************************

    // declare the new row and JSON argument
    var json_Row = new Array();
    var json_Arg = new Object();

    // get some values beforehand which allows us to compare to the rows already present in the subform

    // first column to add in this row is start date (retrieved from field "Actual Start Date" on this record)
    var v_StartDateValue = ZDK.Page.getField('Actual_Start_Date').getValue();
    var v_LocalStartDate = new Date(v_StartDateValue);
    // accommodate for timezone offset
    v_LocalStartDate.setMinutes(v_LocalStartDate.getMinutes() - v_LocalStartDate.getTimezoneOffset());
    // return in yyyy-mm-dd format (on save this will display as per the format on the CRM record)
    v_StartDateFormatted = v_LocalStartDate.toJSON().slice(0, 10);
    // add this as one argument in the JSON
    json_Arg.Start_Date = v_StartDateFormatted;

    // check if end date is specified and if it is then add to this row
    v_EndDate = null;
    v_EndDateValue = ZDK.Page.getField('Actual_Finish_Date').getValue();
    if (v_EndDateValue !== null) {
        var v_LocalEndDate = new Date(v_EndDateValue);
        v_LocalEndDate.setMinutes(v_LocalEndDate.getMinutes() - v_LocalEndDate.getTimezoneOffset());
        v_EndDateFormatted = v_LocalEndDate.toJSON().slice(0, 10);
        json_Arg.End_Date = v_EndDateFormatted;
    }

    // add currency code into column "Agreed Currency"
    json_Arg.Agreed_Currency = v_CurrencyCode;

    // the rate values we determine earlier
    json_Arg.Rate_Year_1_0 = v_DealRate0;
    json_Arg.Rate_Year_1_5 = v_DealRate1;
    json_Arg.Rate_Year_2_0 = v_DealRate2;
    json_Arg.Rate_Year_3_0 = v_DealRate3;
    json_Arg.Rate_Year_4_0 = v_DealRate4;

    // get current user to put as the new row owner (other possible values: id, full_name, email)
    v_CrmUser_FullName = $Crm.user.full_name;
    json_Arg.Rate_Change_Owner = v_CrmUser_FullName;

    // other fields to fill in for this subform row
    v_RateCardDisp = v_RateCard.indexOf(" (")>0 ? v_RateCard.substring(0, v_RateCard.indexOf(" (")) : v_RateCard;
    json_Arg.Rate_Change_Notes = v_RateCardDisp;

    // get existing subform with only the displayed rows (not been deleted)
    var l_existingSubformRows = ZDK.Page.getField('Deployment_Rate_Log').getValue();

    // loop through to exclude any rows we don't want based on criteria
    l_NewSubformRows = new Array();
    for (i = 0; i < l_existingSubformRows.length; i++) {

        // check row hasn't already been entered, here we are using the start date to determine if this has already been entered
        v_CheckField1 = l_existingSubformRows[i].Start_Date;
        var v_LocalCheckDate = new Date(v_CheckField1);
        v_LocalCheckDate.setMinutes(v_LocalCheckDate.getMinutes() - v_LocalCheckDate.getTimezoneOffset());
        v_CheckField1Formatted = v_LocalCheckDate.toJSON().slice(0, 10);

        // if the new row to add matches an existing one but ignoring the end date, then it will omit this to re-add it with an end date.
        if (v_CheckField1Formatted != v_StartDateFormatted) {
            l_NewSubformRows.push(l_existingSubformRows[i]);
        }
    }
    json_Row = l_NewSubformRows;

    // put the arguments into a single row (json array)
    json_Row.push(json_Arg);

    // need to stringify AND parse otherwise this won't work
    var json_Parsed = JSON.parse(JSON.stringify(json_Row));

    // set new subform rows
    ZDK.Page.getField('Deployment_Rate_Log').setValue(json_Parsed);

}

// don't save record?
return false;
  1.  console.log("Joel Lipman - 20221018 -------------------------")
  2.   
  3.  // get some values from the current record 
  4.  var v_CurrencyCode = ZDK.Page.getField('Currency').getValue()
  5.  var v_ConsultantLocation = ZDK.Page.getField('Location').getValue()
  6.   
  7.  // only run the rest if record matches criteria 
  8.  if (v_CurrencyCode == "GBP" && v_ConsultantLocation == "UK") { 
  9.   
  10.      // get value of trigger field 
  11.      var v_RateCard = ZDK.Page.getField('Which_rate_card_is_this_based_off').getValue()
  12.   
  13.      // get opportunity lookup (related deal) of this record 
  14.      let r_DealLookup = ZDK.Page.getField('Opportunity_Name').getValue()
  15.      let v_DealID = r_DealLookup.id; 
  16.      let r_DealDetails = ZDK.Apps.crm.Deals.fetchById(v_DealID)
  17.   
  18.      // get Deal/Opportunity fields we need to refer to 
  19.      var v_OppType = r_DealDetails.Type_of_Opportunity; 
  20.      var v_DealRate0 = r_DealDetails.Rate_Year_1_0; 
  21.      var v_DealRate1 = r_DealDetails.Rate_Year_1_5; 
  22.      var v_DealRate2 = r_DealDetails.Rate_Year_2_0; 
  23.      var v_DealRate3 = r_DealDetails.Rate_Year_3_0; 
  24.      var v_DealRate4 = r_DealDetails.Rate_Year_4_0; 
  25.   
  26.      // default setting for alerting user 
  27.      let b_ShowAlertMessage = true
  28.   
  29.      // specific values based on trigger field 
  30.      if (v_RateCard == 'New Rate Card (Post Aug 22)' && (v_OppType == 'Client'||v_OppType == 'People') && v_CurrencyCode == 'GBP') { 
  31.   
  32.          // hard-coded values if this criteria met 
  33.          v_DealRate0 = 350
  34.          v_DealRate1 = 390
  35.          v_DealRate2 = 465
  36.          v_DealRate3 = 535
  37.          v_DealRate4 = 590
  38.          b_ShowAlertMessage = false
  39.   
  40.      } else if (v_RateCard == 'Old Rate Card (Pre Aug 22)' && (v_OppType == 'Client'||v_OppType == 'People') && v_CurrencyCode == 'GBP') { 
  41.   
  42.          // hard-coded values if this criteria met 
  43.          v_DealRate0 = 345
  44.          v_DealRate1 = 345
  45.          v_DealRate2 = 400
  46.          v_DealRate3 = 475
  47.          v_DealRate4 = 525
  48.          b_ShowAlertMessage = false
  49.   
  50.      } 
  51.   
  52.      // show advisory 
  53.      if (b_ShowAlertMessage) { 
  54.   
  55.          ZDK.Client.showAlert("You have selected a rate without any preset values.  Rates will be taken from the Opportunity record.  If these are blank then enter the rates manually below: Rate Year 1.0, Rate Year 1.5, ...")
  56.   
  57.      } 
  58.   
  59.      // update the fields (that were blank on the existing record - but now pulled from Opp or from set values) 
  60.      ZDK.Page.getForm().setValues({ 'Rate_Year_1_0': v_DealRate0 })
  61.      ZDK.Page.getForm().setValues({ 'Rate_Year_1_5': v_DealRate1 })
  62.      ZDK.Page.getForm().setValues({ 'Rate_Year_2_0': v_DealRate2 })
  63.      ZDK.Page.getForm().setValues({ 'Rate_Year_3_0': v_DealRate3 })
  64.      ZDK.Page.getForm().setValues({ 'Rate_Year_4_0': v_DealRate4 })
  65.   
  66.   
  67.      // start subform management 
  68.      // ***************************************** 
  69.   
  70.      // declare the new row and JSON argument 
  71.      var json_Row = new Array()
  72.      var json_Arg = new Object()
  73.   
  74.      // get some values beforehand which allows us to compare to the rows already present in the subform 
  75.   
  76.      // first column to add in this row is start date (retrieved from field "Actual Start Date" on this record) 
  77.      var v_StartDateValue = ZDK.Page.getField('Actual_Start_Date').getValue()
  78.      var v_LocalStartDate = new Date(v_StartDateValue)
  79.      // accommodate for timezone offset 
  80.      v_LocalStartDate.setMinutes(v_LocalStartDate.getMinutes() - v_LocalStartDate.getTimezoneOffset())
  81.      // return in yyyy-mm-dd format (on save this will display as per the format on the CRM record) 
  82.      v_StartDateFormatted = v_LocalStartDate.toJSON().slice(0, 10)
  83.      // add this as one argument in the JSON 
  84.      json_Arg.Start_Date = v_StartDateFormatted; 
  85.   
  86.      // check if end date is specified and if it is then add to this row 
  87.      v_EndDate = null
  88.      v_EndDateValue = ZDK.Page.getField('Actual_Finish_Date').getValue()
  89.      if (v_EndDateValue !== null) { 
  90.          var v_LocalEndDate = new Date(v_EndDateValue)
  91.          v_LocalEndDate.setMinutes(v_LocalEndDate.getMinutes() - v_LocalEndDate.getTimezoneOffset())
  92.          v_EndDateFormatted = v_LocalEndDate.toJSON().slice(0, 10)
  93.          json_Arg.End_Date = v_EndDateFormatted; 
  94.      } 
  95.   
  96.      // add currency code into column "Agreed Currency" 
  97.      json_Arg.Agreed_Currency = v_CurrencyCode; 
  98.   
  99.      // the rate values we determine earlier 
  100.      json_Arg.Rate_Year_1_0 = v_DealRate0; 
  101.      json_Arg.Rate_Year_1_5 = v_DealRate1; 
  102.      json_Arg.Rate_Year_2_0 = v_DealRate2; 
  103.      json_Arg.Rate_Year_3_0 = v_DealRate3; 
  104.      json_Arg.Rate_Year_4_0 = v_DealRate4; 
  105.   
  106.      // get current user to put as the new row owner (other possible values: id, full_name, email) 
  107.      v_CrmUser_FullName = $Crm.user.full_name; 
  108.      json_Arg.Rate_Change_Owner = v_CrmUser_FullName; 
  109.   
  110.      // other fields to fill in for this subform row 
  111.      v_RateCardDisp = v_RateCard.indexOf((")>0 ? v_RateCard.substring(0, v_RateCard.indexOf((")) : v_RateCard; 
  112.      json_Arg.Rate_Change_Notes = v_RateCardDisp; 
  113.   
  114.      // get existing subform with only the displayed rows (not been deleted) 
  115.      var l_existingSubformRows = ZDK.Page.getField('Deployment_Rate_Log').getValue()
  116.   
  117.      // loop through to exclude any rows we don't want based on criteria 
  118.      l_NewSubformRows = new Array()
  119.      for (i = 0; i < l_existingSubformRows.length; i++) { 
  120.   
  121.          // check row hasn't already been entered, here we are using the start date to determine if this has already been entered 
  122.          v_CheckField1 = l_existingSubformRows[i].Start_Date; 
  123.          var v_LocalCheckDate = new Date(v_CheckField1)
  124.          v_LocalCheckDate.setMinutes(v_LocalCheckDate.getMinutes() - v_LocalCheckDate.getTimezoneOffset())
  125.          v_CheckField1Formatted = v_LocalCheckDate.toJSON().slice(0, 10)
  126.   
  127.          // if the new row to add matches an existing one but ignoring the end date, then it will omit this to re-add it with an end date. 
  128.          if (v_CheckField1Formatted != v_StartDateFormatted) { 
  129.              l_NewSubformRows.push(l_existingSubformRows[i])
  130.          } 
  131.      } 
  132.      json_Row = l_NewSubformRows; 
  133.   
  134.      // put the arguments into a single row (json array) 
  135.      json_Row.push(json_Arg)
  136.   
  137.      // need to stringify AND parse otherwise this won't work 
  138.      var json_Parsed = JSON.parse(JSON.stringify(json_Row))
  139.   
  140.      // set new subform rows 
  141.      ZDK.Page.getField('Deployment_Rate_Log').setValue(json_Parsed)
  142.   
  143.  } 
  144.   
  145.  // don't save record? 
  146.  return false

Note(s)
  • Using the following line returns all subform rows, even deleted ones, which is why we didn't use this command in the system. But keeping it for future reference.
    copyraw
    var l_existingSubformRows = ZDK.Apps.CRM.Deployment_Rate_Log.fetch();
    1.  var l_existingSubformRows = ZDK.Apps.crm.Deployment_Rate_Log.fetch()


Another example of looping through a subform
In the following snippet of code, we are going to loop through a subform called "Skillset" which has a column called "Skill" which is a lookup to the "Skills" module. This client script is triggered when the user hits the "Save" button (event: onSave) and will stop the user from saving the record and continuing if 3 different skills are not specified in the subform:
copyraw
// initialize
var v_CalculatedNumberOfSkills = 0;

// get the Opp stage
var v_OppStage = ZDK.Page.getField('Stage').getValue();

// get the Skills subform
var l_SkillsSubform = ZDK.Page.getField('Skillset').getValue();
var l_AlreadyListed = Array();

// loop through subform rows and check if column "Skill" has unique values
for (var i = 0; i < l_SkillsSubform.length; i++) {

    // convert this row to an object
    o_SkillName = JSON.parse(JSON.stringify(l_SkillsSubform[i]));

    // check the skill is not blank
    if (o_SkillName.Skill != null) {

        // parse out the name (Skill is a lookup field)
        v_SkillName = o_SkillName.Skill.name;

        // check if skill is not null and has not already been listed
        if (v_SkillName != null && l_AlreadyListed.indexOf(v_SkillName) < 0) {

            // increment
            v_CalculatedNumberOfSkills += 1;

            // add to list of used values
            l_AlreadyListed.push(v_SkillName);

        }
        
    }

}

// set the value of No_Skills_Listed field
ZDK.Page.getForm().setValues({ 'No_Skills_Listed': v_CalculatedNumberOfSkills });

// if not 3 or greater then alert the user and prevent the save
if (v_CalculatedNumberOfSkills < 3 && v_OppStage == "Qualified") {
    ZDK.Client.showAlert("Please list at least 3 different skills relevant to the opportunity in the 'Skills' section before progressing.");

    // prevent save of the record
    return false;
}
  1.  // initialize 
  2.  var v_CalculatedNumberOfSkills = 0
  3.   
  4.  // get the Opp stage 
  5.  var v_OppStage = ZDK.Page.getField('Stage').getValue()
  6.   
  7.  // get the Skills subform 
  8.  var l_SkillsSubform = ZDK.Page.getField('Skillset').getValue()
  9.  var l_AlreadyListed = Array()
  10.   
  11.  // loop through subform rows and check if column "Skill" has unique values 
  12.  for (var i = 0; i < l_SkillsSubform.length; i++) { 
  13.   
  14.      // convert this row to an object 
  15.      o_SkillName = JSON.parse(JSON.stringify(l_SkillsSubform[i]))
  16.   
  17.      // check the skill is not blank 
  18.      if (o_SkillName.Skill != null) { 
  19.   
  20.          // parse out the name (Skill is a lookup field) 
  21.          v_SkillName = o_SkillName.Skill.name; 
  22.   
  23.          // check if skill is not null and has not already been listed 
  24.          if (v_SkillName != null && l_AlreadyListed.indexOf(v_SkillName) < 0) { 
  25.   
  26.              // increment 
  27.              v_CalculatedNumberOfSkills +1
  28.   
  29.              // add to list of used values 
  30.              l_AlreadyListed.push(v_SkillName)
  31.   
  32.          } 
  33.   
  34.      } 
  35.   
  36.  } 
  37.   
  38.  // set the value of No_Skills_Listed field 
  39.  ZDK.Page.getForm().setValues({ 'No_Skills_Listed': v_CalculatedNumberOfSkills })
  40.   
  41.  // if not 3 or greater then alert the user and prevent the save 
  42.  if (v_CalculatedNumberOfSkills < 3 && v_OppStage == "Qualified") { 
  43.      ZDK.Client.showAlert("Please list at least 3 different skills relevant to the opportunity in the 'Skills' section before progressing.")
  44.   
  45.      // prevent save of the record 
  46.      return false
  47.  } 

Source(s):
Category: Zoho :: Article: 827

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.