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 https://ascentbusiness.co.uk/zoho-services/uk-zoho-support.  For larger projects, talk to our experts and receive dedicated support from our hands-on project consultants at https://ascentbusiness.co.uk/zoho-services/zoho-crm-implementation.

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 https://ascentbusiness.co.uk.
ZohoCRM to ZohoBooks API: Rounding the cents or pennies when creating an invoice

ZohoCRM to ZohoBooks API: Rounding the cents or pennies when creating an invoice

Category: Zoho
Hits: 4898
What?
A frustrated article about an issue that took me a fair few hours to resolve. So I'm putting it here as I thought I covered this previously but couldn't find it on my website. This was previously titled something along the lines of truncating to 2 decimals but finance is so much more complicated.

Why?
Playing around with VAT / Tax, inclusive /exclusive, but in this case it is the rate that gets rounded. The numbers at the end still need to match what's in CRM and with ZohoBooks rounding differently can make cent/penny errors into 100s of dollar/pound errors.

Zoho CRM has a fun way of rounding which differs to Zoho Books so when pushing an invoice, there's a strong chance of being a cent/penny off. Let's take the following example of an item with a list price of 12345.545:

ZohoCRM Line Item
ZohoCRM to ZohoBooks API: Truncate number to 2 decimal points without Rounding - Zoho CRM Line Item

ZohoBooks Line Item
ZohoCRM to ZohoBooks API: Truncate number to 2 decimal points without Rounding - Zoho Books Line Item

How?

Here's the immediate solution I have applied now until Zoho enable an option to round via API with a 10th of a penny/cent or less.

After creating or updating the invoice, add in the response line something like the following:
copyraw 
// ... UPDATE INVOICE
		r_UpdateBooksInvoice = zoho.books.updateRecord("invoices",v_BooksOrgID,v_ZohoBooksID,m_InvoiceData,"zbooks");
		if(!isNull(r_UpdateBooksInvoice.get("invoice")))
		{
			if(!isNull(r_UpdateBooksInvoice.get("invoice").get("invoice_id")))
			{
				//
				v_OutputResponse = r_UpdateBooksInvoice.get("invoice").get("invoice_number") + ": " + r_UpdateBooksInvoice.get("message");
				//
				// adjustments
				if(r_UpdateBooksInvoice.get("invoice").get("total") != v_Crm_Invoice_GrandTotal)
				{
					v_TotalAdjustments = v_Crm_Invoice_GrandTotal - r_UpdateBooksInvoice.get("invoice").get("total");
					m_ZB_Update = Map();
					//m_ZB_Update.put("roundoff_value",v_TotalAdjustments);
					m_ZB_Update.put("adjustment",v_TotalAdjustments);
					m_ZB_Update.put("adjustment_description","Rounding Off");
					r_ZB_Update = zoho.books.updateRecord("invoices", v_OrgId, r_UpdateBooksInvoice.get("invoice").get("invoice_id"), m_ZB_Update, "zbooks");
				}
		}
		else if(!isNull(r_UpdateBooksInvoice.get("message")))
		{
			v_OutputResponse = "FAILED: " + r_UpdateBooksInvoice.get("message");
		}
  1.  // ... UPDATE INVOICE 
  2.          r_UpdateBooksInvoice = zoho.books.updateRecord("invoices",v_BooksOrgID,v_ZohoBooksID,m_InvoiceData,"zbooks"); 
  3.          if(!isNull(r_UpdateBooksInvoice.get("invoice"))) 
  4.          { 
  5.              if(!isNull(r_UpdateBooksInvoice.get("invoice").get("invoice_id"))) 
  6.              { 
  7.                  // 
  8.                  v_OutputResponse = r_UpdateBooksInvoice.get("invoice").get("invoice_number") + ": " + r_UpdateBooksInvoice.get("message"); 
  9.                  // 
  10.                  // adjustments 
  11.                  if(r_UpdateBooksInvoice.get("invoice").get("total") != v_Crm_Invoice_GrandTotal) 
  12.                  { 
  13.                      v_TotalAdjustments = v_Crm_Invoice_GrandTotal - r_UpdateBooksInvoice.get("invoice").get("total"); 
  14.                      m_ZB_Update = Map(); 
  15.                      //m_ZB_Update.put("roundoff_value",v_TotalAdjustments); 
  16.                      m_ZB_Update.put("adjustment",v_TotalAdjustments); 
  17.                      m_ZB_Update.put("adjustment_description","Rounding Off"); 
  18.                      r_ZB_Update = zoho.books.updateRecord("invoices", v_OrgId, r_UpdateBooksInvoice.get("invoice").get("invoice_id"), m_ZB_Update, "zbooks"); 
  19.                  } 
  20.          } 
  21.          else if(!isNull(r_UpdateBooksInvoice.get("message"))) 
  22.          { 
  23.              v_OutputResponse = "FAILED: " + r_UpdateBooksInvoice.get("message"); 
  24.          } 
It's a cop-out I know, but it beats the accounts staff having to enter the adjustment each time. The create invoice process would be treated the same.


The below are all the various methods I tried to resolve this but the truth is that I do not have a clue how ZohoBooks comes up with its rounding figures. They're wrong in any case as they don't match the ZohoCRM... or both are just not matching... either way, your client will likely only accept the previous solution.

I wanted to use a regex rather than treat the number as a string and truncate it that way. Given the possible options of numbers given, it has to work with every number so here's both in Zoho Deluge Tryout:
copyraw 
v_Rate = 12345.545;
info "Rate: " + v_Rate;

// built-in rounding
v_Rounded = v_Rate.round(2);
info "a) " + v_Rounded;
// Yields: 12345.55

// Regexed
v_RegexedNumber = v_Rate.toString().replaceAll("^(\\d+\\.\\d{2})\\d*$", "$1");
info "b) " + v_RegexedNumber; 
// Yields: 12345.54

// 2 characters after period
v_NotRegexedNumber = v_Rate.toString().subString(0,v_Rate.toString().indexOf(".")+3);
info "c) " + v_NotRegexedNumber;
// Yields: 12345.54

// -------------------------

v_RateUp = 12345.546;
info "Rate Up: " + v_RateUp;

// built-in rounding
v_Rounded = v_RateUp.round(2);
info "d) " + v_Rounded;
// Yields: 12345.55

// Regexed
v_RegexedNumber = v_RateUp.toString().replaceAll("^(\\d+\\.\\d{2})\\d*$", "$1");
info "e) " + v_RegexedNumber; 
// Yields: 12345.54

// 2 characters after period
v_NotRegexedNumber = v_RateUp.toString().subString(0,v_Rate.toString().indexOf(".")+3);
info "f) " + v_NotRegexedNumber;
// Yields: 12345.54

// -------------------------

v_RateDown = 12345.544;
info "Rate Down: " + v_RateDown;

// built-in rounding
v_Rounded = v_RateDown.round(2);
info "g) " + v_Rounded;
// Yields: 12345.54

// Regexed
v_RegexedNumber = v_RateDown.toString().replaceAll("^(\\d+\\.\\d{2})\\d*$", "$1");
info "h) " + v_RegexedNumber; 
// Yields: 12345.54

// 2 characters after period
v_NotRegexedNumber = v_RateDown.toString().subString(0,v_Rate.toString().indexOf(".")+3);
info "i) " + v_NotRegexedNumber;
// Yields: 12345.54
  1.  v_Rate = 12345.545; 
  2.  info "Rate: " + v_Rate; 
  3.   
  4.  // built-in rounding 
  5.  v_Rounded = v_Rate.round(2); 
  6.  info "a) " + v_Rounded; 
  7.  // Yields: 12345.55 
  8.   
  9.  // Regexed 
  10.  v_RegexedNumber = v_Rate.toString().replaceAll("^(\\d+\\.\\d{2})\\d*$", "$1"); 
  11.  info "b) " + v_RegexedNumber; 
  12.  // Yields: 12345.54 
  13.   
  14.  // 2 characters after period 
  15.  v_NotRegexedNumber = v_Rate.toString().subString(0,v_Rate.toString().indexOf(".")+3); 
  16.  info "c) " + v_NotRegexedNumber; 
  17.  // Yields: 12345.54 
  18.   
  19.  // ------------------------- 
  20.   
  21.  v_RateUp = 12345.546; 
  22.  info "Rate Up: " + v_RateUp; 
  23.   
  24.  // built-in rounding 
  25.  v_Rounded = v_RateUp.round(2); 
  26.  info "d) " + v_Rounded; 
  27.  // Yields: 12345.55 
  28.   
  29.  // Regexed 
  30.  v_RegexedNumber = v_RateUp.toString().replaceAll("^(\\d+\\.\\d{2})\\d*$", "$1"); 
  31.  info "e) " + v_RegexedNumber; 
  32.  // Yields: 12345.54 
  33.   
  34.  // 2 characters after period 
  35.  v_NotRegexedNumber = v_RateUp.toString().subString(0,v_Rate.toString().indexOf(".")+3); 
  36.  info "f) " + v_NotRegexedNumber; 
  37.  // Yields: 12345.54 
  38.   
  39.  // ------------------------- 
  40.   
  41.  v_RateDown = 12345.544; 
  42.  info "Rate Down: " + v_RateDown; 
  43.   
  44.  // built-in rounding 
  45.  v_Rounded = v_RateDown.round(2); 
  46.  info "g) " + v_Rounded; 
  47.  // Yields: 12345.54 
  48.   
  49.  // Regexed 
  50.  v_RegexedNumber = v_RateDown.toString().replaceAll("^(\\d+\\.\\d{2})\\d*$", "$1"); 
  51.  info "h) " + v_RegexedNumber; 
  52.  // Yields: 12345.54 
  53.   
  54.  // 2 characters after period 
  55.  v_NotRegexedNumber = v_RateDown.toString().subString(0,v_Rate.toString().indexOf(".")+3); 
  56.  info "i) " + v_NotRegexedNumber; 
  57.  // Yields: 12345.54 
Of course ZohoCRM functions have to treat Regex's slightly differently in ZohoCRM, so if we went with the Regex to truncate, we would need to get rid of the excess escaping backslashes:
copyraw 
v_ListPrice = ifnull(m_ZCrm_LineItem.get("List_Price"),0);
m_ZBooks_LineItem.put("rate",v_ListPrice.toString().replaceAll("^(\d+\.\d{2})\d*$", "$1", false));
  1.  v_ListPrice = ifnull(m_ZCrm_LineItem.get("List_Price"),0); 
  2.  m_ZBooks_LineItem.put("rate",v_ListPrice.toString().replaceAll("^(\d+\.\d{2})\d*$", "$1", false)); 

Works a treat at truncating but still does NOT match how ZohoBooks does it. ZohoBooks rounds the .545 to .55 and .544 to .54... so let's abandon the truncating and simply deduct 1/10th of a cent/penny:
copyraw 
// -------------------------
// Make .545 = .54 and .546 = .55

// CRM rounding
v_RateCrm = 12345.545;
info "Rate CRM 545: " + v_RateCrm;
v_Rounded = (v_RateCrm - 0.001).round(2);
info "j) " + v_Rounded;
// Yields: 12345.54

// CRM rounding
v_RateCrm = 12345.544;
info "Rate CRM 544: " + v_RateCrm;
v_Rounded = (v_RateCrm - 0.001).round(2);
info "k) " + v_Rounded;
// Yields: 12345.54

// CRM rounding
v_RateCrm = 12345.546;
info "Rate CRM 546: " + v_RateCrm;
v_Rounded = (v_RateCrm - 0.001).round(2);
info "l) " + v_Rounded;
// Yields: 12345.55

// CRM rounding
v_RateCrm = 12345.555;
info "Rate CRM 555: " + v_RateCrm;
v_Rounded = (v_RateCrm - 0.001).round(2);
info "m) " + v_Rounded;
// Yields: 12345.55
  1.  // ------------------------- 
  2.  // Make .545 = .54 and .546 = .55 
  3.   
  4.  // CRM rounding 
  5.  v_RateCrm = 12345.545; 
  6.  info "Rate CRM 545: " + v_RateCrm; 
  7.  v_Rounded = (v_RateCrm - 0.001).round(2); 
  8.  info "j) " + v_Rounded; 
  9.  // Yields: 12345.54 
  10.   
  11.  // CRM rounding 
  12.  v_RateCrm = 12345.544; 
  13.  info "Rate CRM 544: " + v_RateCrm; 
  14.  v_Rounded = (v_RateCrm - 0.001).round(2); 
  15.  info "k) " + v_Rounded; 
  16.  // Yields: 12345.54 
  17.   
  18.  // CRM rounding 
  19.  v_RateCrm = 12345.546; 
  20.  info "Rate CRM 546: " + v_RateCrm; 
  21.  v_Rounded = (v_RateCrm - 0.001).round(2); 
  22.  info "l) " + v_Rounded; 
  23.  // Yields: 12345.55 
  24.   
  25.  // CRM rounding 
  26.  v_RateCrm = 12345.555; 
  27.  info "Rate CRM 555: " + v_RateCrm; 
  28.  v_Rounded = (v_RateCrm - 0.001).round(2); 
  29.  info "m) " + v_Rounded; 
  30.  // Yields: 12345.55 
So in practice, and for the sake of your finance team's sanity, the most acceptable solution is to deduct the 10th of a cent/penny and as per the documentation for adjustments, we'll run a check after the invoice is created/updated and check that the total matches the CRM grand total and then add the difference as an adjustment with the description "Rounding Off":
copyraw 
v_TotalAdjustments = 0.00;
for each m_ZCrm_LineItem in l_ZCrm_LineItems
{
	v_ListPrice = ifnull(m_ZCrm_LineItem.get("List_Price"),0);
	if(v_ListPrice.toString().contains("."))
	{
		if(v_ListPrice.toString().toList(".").get(1).subString(2,3) == "5")
		{
			v_TotalAdjustments = v_TotalAdjustments + 0.005;
		}
	}
	m_ZB_LineItem.put("rate",v_ListPrice);
	l_ZB_LineItems.add(m_ZB_LineItem);
}
m_ZB_InvoiceData.put("line_items",l_ZB_LineItems);
if(v_TotalAdjustments > 0)
{
	m_ZB_InvoiceData.put("adjustment",v_TotalAdjustments * (-1));
	m_ZB_InvoiceData.put("adjustment_description","Rounding Off");
}
/*
// don't round if no more than 2 decimals
if(v_ListPrice.toString().contains("."))
{
	v_ListPrice = if(v_ListPrice.toString().toList(".").get(1).length()>2, (v_ListPrice - 0.001).round(2), v_ListPrice);
}
*/

// OR


	//
	if(v_ListPrice.toString().contains("."))
	{
		if(v_ListPrice.toString().getSuffix(".").length() > 2)
		{
			v_ThisAdjustment = "0.00" + v_ListPrice.toString().toList(".").get(1).subString(2);
			if(v_ListPrice.toString().startsWith("-"))
			{
				v_TotalAdjustments = v_TotalAdjustments - v_ThisAdjustment.toDecimal();
			}
			else 
            {
				v_TotalAdjustments = v_TotalAdjustments + v_ThisAdjustment.toDecimal();
            }
			info "Adjust: " + v_ThisAdjustment;
			//v_ListPrice = if(v_ListPrice.toString().toList(".").get(1).length() > 2,(v_ListPrice - 0.001).round(2),v_ListPrice.round(2));
		}
	}
  1.  v_TotalAdjustments = 0.00; 
  2.  for each m_ZCrm_LineItem in l_ZCrm_LineItems 
  3.  { 
  4.      v_ListPrice = ifnull(m_ZCrm_LineItem.get("List_Price"),0); 
  5.      if(v_ListPrice.toString().contains(".")) 
  6.      { 
  7.          if(v_ListPrice.toString().toList(".").get(1).subString(2,3) == "5") 
  8.          { 
  9.              v_TotalAdjustments = v_TotalAdjustments + 0.005; 
  10.          } 
  11.      } 
  12.      m_ZB_LineItem.put("rate",v_ListPrice); 
  13.      l_ZB_LineItems.add(m_ZB_LineItem); 
  14.  } 
  15.  m_ZB_InvoiceData.put("line_items",l_ZB_LineItems); 
  16.  if(v_TotalAdjustments > 0) 
  17.  { 
  18.      m_ZB_InvoiceData.put("adjustment",v_TotalAdjustments * (-1)); 
  19.      m_ZB_InvoiceData.put("adjustment_description","Rounding Off"); 
  20.  } 
  21.  /* 
  22.  // don't round if no more than 2 decimals 
  23.  if(v_ListPrice.toString().contains(".")) 
  24.  { 
  25.      v_ListPrice = if(v_ListPrice.toString().toList(".").get(1).length()>2, (v_ListPrice - 0.001).round(2), v_ListPrice); 
  26.  } 
  27.  */ 
  28.   
  29.  // OR 
  30.   
  31.   
  32.      // 
  33.      if(v_ListPrice.toString().contains(".")) 
  34.      { 
  35.          if(v_ListPrice.toString().getSuffix(".").length() > 2) 
  36.          { 
  37.              v_ThisAdjustment = "0.00" + v_ListPrice.toString().toList(".").get(1).subString(2); 
  38.              if(v_ListPrice.toString().startsWith("-")) 
  39.              { 
  40.                  v_TotalAdjustments = v_TotalAdjustments - v_ThisAdjustment.toDecimal(); 
  41.              } 
  42.              else 
  43.              { 
  44.                  v_TotalAdjustments = v_TotalAdjustments + v_ThisAdjustment.toDecimal(); 
  45.              } 
  46.              info "Adjust: " + v_ThisAdjustment; 
  47.              //v_ListPrice = if(v_ListPrice.toString().toList(".").get(1).length() > 2,(v_ListPrice - 0.001).round(2),v_ListPrice.round(2)); 
  48.          } 
  49.      } 
 Ta daa!
ZohoCRM to ZohoBooks API: Truncate number to 2 decimal points without Rounding - Zoho Books Line Item After Fix

Source(s):
I would mention a few community forum pages I went through which showed me how not to do it, but as none were the solution, I'm not sure putting them here would make this easier for you.

Category: Zoho :: Article: 901

Add comment

Your rating:

Submit

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

Latest Articles

Accreditation

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

Joes Word Cloud

Please publish modules in offcanvas position.