Zoho CRM: Manage a subform using Client Script

: Joel Lipman; Zoho; 09 December 2022; Hits: 7907

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).

copy raw

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;

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;

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.
copyrawstyler
```
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:

copy raw

// 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;
}

// 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;
}