For Zoho Services only:


I'm actually part of a bigger team at Ascent Business Solutions where we have support technicians and project consultants. Support is for smaller technical fixes but this can include developments, reports or integrations; depending on the size of the task. Projects are for more time-consuming developments such as revamps of the Zoho Suite of apps or on-site training. The advantage of a team is that if I am out-of-office for a day or so, there is always someone at Ascent Business Solutions who can deal with any queries/issues you may have.

Our support rates can be found and purchased at http://ascentbusiness.co.uk/zoho-support-2. A support bundle doesn't have an expiry date. So whether we can do what you want within the bundle and a year later need further support, if there are minutes left on the bundle then there is no additional charge.

Our project rates for bigger developments can be found at http://ascentbusiness.co.uk/crm-solutions/zoho-crm-packages-prices and will involve a dedicated project consultant along with developers who will hold your hand through the development process.

If you want help building a solution for one of the Zoho Apps in the Zoho Suite, contact us on 0121 392 8140 (UK) or by email at info@ascentbusiness.co.uk. You can also visit our website at http://ascentbusiness.co.uk.

I regularly build and specialize in 2-way API integrations for Xero, Shopify and eBay.

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: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used 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 - Valid till 8 May 2022 bc1qjtp4l4ra452wzvuk9a45yfj82zkahsyy2z379y
© 2023 Joel Lipman .com. All Rights Reserved.