A Deluge script that pulls records from Books/CRM/another database into a Zoho Creator Form as Subform Rows, and then push changes to each individual record (or perform other actions like creating records) in the parent system.
Suppose you have a long list of records in Books/CRM/another database that needs to be manually reviewed before you decide to execute certain actions. Instead of going through each and every record on the database (which is tideous and inefficient), custom functions can be written to pull relevant record information from the database into Creator as Subform Rows. Edits to those subform rows can affect the related record upon submission. Custom actions could also be performed around each record via a Decision Box field on the subform row that would act as a general trigger.
To aid the illustration, we will use an example case: Periodically, the accounting team needs to review all overdue invoices from Zoho Books, and decide if they want to levy a finance charge (late fee) on each of the invoices.
The following connections need to be set up in Creator (Main Dashboard > Account Setup > Extensions > Connections):
Get all Overdue Invoices from Zoho Books, and pull the necessary fields to a subform in Creator. Here, accountants get to view all overdue invoices in one screen and tick the rows where late fees should be charged. The following script needs to be written on load of the Creator form.
Org ID refers to your organization ID as specified in Zoho Books. It will be needed for the API calls later.
//Get the Org ID
getOrganizations = invokeurl
[
url :"https://books.zoho.com/api/v3/organizations"
type :GET
connection:"zohobooks" // Change this to your connection name
];
orgId = getOrganizations.get("organizations").get(0).get("organization_id");
info orgId;d,{"Company_Overview":company_overview});
info update2;
}
}
Get all required Records in a List
A pagination iterator is used to get > 200 records (API limit) – read more about API pagination here.
// List of page numbers. This should start with '1' and be much longer than the expected number of pages.
pageIterationList = {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25};
// Number of results to return per page. DO NOT exceed the per_page limit of your API, or this function will not perform correctly.
perPageLimit = 200;
// List to accumulate all records
allRecords = List();
// The 'while' condition for evaluation. While this is 'false', the API requests will continue.
iterationComplete = false;
// Loop over each page in the page list
for each page in pageIterationList
{
// Evaluate whether 'while' condition is satisfied
if(iterationComplete == false)
{
paramap = Map(); //Change the paramap based on your requirement
paramap.put("status","overdue");
paramap.put("cf_fc",false);
paramap.put("sort_column","customer_name");
response = invokeurl
[
url :"https://books.zoho.com/api/v3/invoices?organization_id=" + orgId + "&page=" + page + "&per_page=" + perPageLimit
type :GET
parameters:paramap
connection:"zohobooks" // Change this to your connection name
];
// Get records from API response. Add them to allRecords list.
records = response.get("invoices");
allRecords.addAll(records);
// Update 'while' condition status
if(records.size() < perPageLimit)
{
iterationComplete = true;
}
}
}
// Check for correctness
info "Number of Records: " + allRecords.size();
Pull required record info to the Creator Subform
For each record, we get the necessary fields from Books, and assign values to the subform fields. When this is completed, you will get all the fields of the records you need populated in the Creator subform upon the load of the form, ready for review.
n = 0;
for each r in allRecords
{
// Declaring the row
row_n = Invoice_Financing.Overdue_Invoices();
// Assigning values to subform fields in the row
row_n.Invoice_No=r.get("invoice_number");
row_n.Invoice_ID=r.get("invoice_id");
row_n.Customer_Name=r.get("customer_name");
row_n.Due_Date=r.get("due_date");
row_n.Outstanding_Amount=r.get("balance");
row_n.Invoice_URL="https://books.zoho.com/app#/invoices/" + r.get("invoice_id");
// The .002739 is 1/365, i.e. the rate of interest charged per day
row_n.Interest_Charge=days360(r.get("due_date").toDate(),today) * 0.18 * r.get("balance") * .002739; // Arbitrary calculation for interest charge
row_n.Customer_ID=r.get("customer_id");
row_n.Current_Finance_Charge_Date=r.get("custom_field_hash").get("cf_next_finance_charge_unformatted");
// declare a variable to hold the collection of rows
update = Collection();
update.insert(row_n);
// insert the rows into the subform through the variable
input.Overdue_Invoices.insert(update);
n = n + 1;
}
Note: Change the row names row_n & field API names r.get("field_name") based on your requirements.
When the accounting team is done reviewing the invoices, upon submission of the form, 2 main actions are executed for rows where the “Levy Finance Charge” field is ticked (detailed mechanics and criteria to be explained as we go):
The following script needs to be written on submission of the Creator form.
Org ID refers to your organization ID as specified in Zoho Books. It will be needed for the API calls later.
//Get the Org ID
getOrganizations = invokeurl
[
url :"https://books.zoho.com/api/v3/organizations"
type :GET
connection:"zohobooks" // Change this to your connection name
];
orgId = getOrganizations.get("organizations").get(0).get("organization_id");
info orgId;
For each row with “Levy Finance Charge” ticked, print the customer ID in a list. This will get you a unique list of customer IDs (some customers may have 2 or more overdue invoices) – this is to ensure that only 1 invoice is created per customer. The subsequent part of the script will be iterating through this list.
customerIDs = List();
for each row in Overdue_Invoices
{
if(row.Levy_Finance_Charge = true)
{
if(!customerIDs.contains(row.Customer_ID))
{
customerIDs.add(row.Customer_ID);
}
}
}
The script below executes the 2 main actions:
for each c in customerIDs
{
prevFC = "";
currFC = "";
line_items = List();
invoiceIDList = List();
for each row in Overdue_Invoices
{
if(row.Levy_Finance_Charge = true)
{
if(c = row.Customer_ID)
{
line_item = Map();
line_item.put("description",row.Invoice_No);
line_item.put("rate",row.Interest_Charge);
line_item.put("item_id",2066391000003720076); // This is a custom item created for Finance Charge. Create your own and replace the ID here.
line_item.put("quantity",1);
line_items.add(line_item);
invoiceIDList.add(row.Invoice_ID);
}
}
}
invoicemap = Map();
invoicemap.put("customer_id",c);
invoicemap.put("date",today);
invoicemap.put("due_date",today.addBusinessDay(10));
invoicemap.put("line_items",line_items);
// Create List of Custom Fields to Check the FC field
customFieldList2 = List();
customFieldMap3 = Map();
customFieldMap3.put("label","FC");
customFieldMap3.put("value",true);
customFieldList2.add(customFieldMap3);
invoicemap.put("custom_fields",customFieldList2);
// Create the Invoice
createInvoice = zoho.books.createRecord("Invoices",orgId.toString(),invoicemap);
info createInvoice;
//Mark created invoice as sent
marksent = invokeurl
[
url :"https://books.zoho.com/api/v3/invoices/" + createInvoice.get("invoice").get("invoice_id") + "/status/sent?organization_id=" + orgId
type :POST
connection:"zohobooks" // Change this to your connection name
];
info marksent;
currentDate = zoho.currentdate.toString("yyyy-MM-dd");
//info currentDate;
// Create List of Custom Fields for Current Finance Charge
financeCharge = Map();
customFieldList = List();
customFieldMap1 = Map();
customFieldMap1.put("label","Current Finance Charge");
customFieldMap1.put("value",currentDate);
customFieldList.add(customFieldMap1);
for each row in Overdue_Invoices
{
if(row.Levy_Finance_Charge = true)
{
if(c = row.Customer_ID)
{
// Get current CF Invoice no
currFC = createInvoice.get("invoice").get("invoice_number");
// Get the original Invoice no
oriInvoice = zoho.books.getRecordsByID("invoices",orgId,row.Invoice_ID);
// Get previous CF invoice no. if exists
if(oriInvoice.get("invoice").get("custom_field_hash").get("cf_finance_charge_invoices") != null)
{
prevFC = oriInvoice.get("invoice").get("custom_field_hash").get("cf_finance_charge_invoices");
prevFC = prevFC + "\n";
fcInvoice = prevFC + currFC;
}
else
{
fcInvoice = currFC;
}
info "oriInvoice" : + oriInvoice;
info "fcInvoice: " + fcInvoice;
}
}
}
// Create List of Custom Fields for Finance Charge Invoice(s)
customFieldMap2 = Map();
customFieldMap2.put("label","Finance Charge Invoice(s)");
customFieldMap2.put("value",fcInvoice);
customFieldList.add(customFieldMap2);
// Add all Custom Field Maps into Custom Field List
financeCharge.put("custom_fields",customFieldList);
for each id in invoiceIDList
{
updateInvoice = invokeurl
[
url :"https://books.zoho.com/api/v3/invoices/" + id + "?organization_id=" + orgId
type :PUT
parameters:financeCharge + ""
connection:"zohobooks" // Change this to your connection name
];
info updateInvoice;
}
}
We have demonstrated how this works with Zoho Books. Now, we will use another example case for Zoho CRM: You are running an online course business and you would like your teachers to leave a remark on certain students which are exceptional (all students are stored in the Contacts module).
The following connections need to be set up in Creator (Main Dashboard > Account Setup > Extensions > Connections):
Get all Contacts from Zoho CRM, and pull the necessary fields to a subform in Creator. Here, teachers get to view all students in one screen and leave their remarks. The following script needs to be written on load of the Creator form.
A pagination iterator is used to get > 200 records (API limit) – read more about API pagination here: https://github.com/TheWorkflowAcademy/api-pagination-zohocrm.
// CONFIG: Name of Zoho CRM record module (Contacts, Leads, Custom Modules, etc.)
zohoCrmModule = "Contacts";
// CONFIG: Number of results to return per page. DO NOT exceed the per_page limit of your API, or this function will not perform correctly.
perPageLimit = 200;
// List of page numbers. This should start with '1' and be much longer than the expected number of pages.
pageIterationList = {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25};
// List to accumulate all records
allRecords = List();
// The 'while' condition for evaluation. While this is 'false', the API requests will continue.
iterationComplete = false;
// Loop over each page in the page list
for each page in pageIterationList
{
// Evaluate whether 'while' condition is satisfied
if(iterationComplete == false)
{
// Get records from Zoho CRM API.
// CONFIG: Name of Zoho CRM API Connection
response = invokeurl
[
url :"https://www.zohoapis.com/crm/v2/" + zohoCrmModule + "?page=" + page + "&per_page=" + perPageLimit
type :GET
connection:"zohocrm" // Change this to your connection name
];
// Get records from API response. Add them to allRecords list.
records = response.get("data");
allRecords.addAll(records);
// Update 'while' condition status
if(records.size() < perPageLimit)
{
iterationComplete = true;
}
}
}
// Check for correctness
info "Number of Records: " + allRecords.size();
For each record, we get the necessary fields from Books, and assign values to the subform fields. When this is completed, you will get all the fields of the records you need populated in the Creator subform upon the load of the form, ready for review.
n = 0;
for each r in allRecords
{
// Declaring the row
row_n = Student_Review_Form.List_of_Students();
// Assigning values to subform fields in the row
row_n.Student_Name = r.get("First_Name") + " " + r.get("Last_Name");
row_n.Student_ID = r.get("id");
// declare a variable to hold the collection of rows
update = Collection();
update.insert(row_n);
// insert the rows into the subform through the variable
input.List_of_Students.insert(update);
n = n + 1;
}
Note: Change the row names row_n & field API names r.get("field_name") based on your requirements.
When the teachers are done reviewing the students, upon submission of the form, the function will then push the remark and update into CRM. The following script needs to be written on submission of the Creator form.
For each student in the subform, if the “Update” Decision Box is ticked, the function will get the remark keyed in by the teacher, and update the similar “Teacher’s Remark” field in CRM accordingly.
for each row in List_of_Students
{
if(row.Update == true)
{
//Create map for update
data = List();
content = Map();
content.put("id", row.Student_ID);
content.put("Teacher_s_Remark",row.Teacher_s_Remark);
data.add(content);
mp = Map();
mp.put("data", data);
//Update record in CRM
response = invokeurl
[
url :"https://www.zohoapis.com/crm/v2/Contacts"
type :PUT
parameters:mp.toString()
connection:"zohocrm" // Change this to your connection name
];
info response;
}
}
Click here to copy these scripts. For more Zoho-wizardry, check out our GitHub page.
When scripting, ask yourself this question – will this value ever be null? If it’s a yes, that’s a place for a null check! Here are 3 tips and best practices to help you kickstart the habit.
Learn to create a customized inventory report with Zoho Analytics. This tutorial contains a link to our GitHub page for SQL code that will help with your table creations.
How nice would it be if you could, at the press of a button, send clients an email w/a Zoho Books invoice? Replete with “Pay Now” buttons that link to PayPal/Stripe/other payment gateways?
Whether you work primarily out of CRM or the Zoho Finance Suite, you can use Analytics to build commissions dashboards. This involves some fairly simple SQL code.
If you are an inventory manager, this blog post could change your life. If you are not, it will at least teach you how to build some wicked inventory tracking for your business.
Convert fields, related activities, attachments, notes and more from one record to another across modules via custom function.