ZohoCRM & Xero: Function to pull most recent quotes

: Category: Zoho; 13 October 2025; Hits: 369

What?
A follow-on article from my previous article ZohoCRM & Xero: Function to pull most recent invoices - along with their Contacts and Items (accounts/contact & products respectively).

Why?
This took me so much longer than I thought it would. It was meant to be based on the pull from invoices article I wrote earlier but with quotes from Xero, things panned out differently:

Date/Time values don't include a timezone
Issues with tax rates meant replicating a tax list copy from Xero to Zoho
the Client doesn't use the Items module in Xero and instead puts the product name in the description field

How?
Because my head is a little fried, I'm putting the two functions I used here and I'll document further if I remember. Note that the pre-amble is to generate a Xero access token which I documented in another article - it's behind a userwall because I usually charge for my Xero integration to Zoho CRM but user registration is free; you need to click on "Account" at the top of my website then login, then search for Xero.

fn_Xero_MapTaxRates
This function used to take a Xero TaxType name and return a Zoho Tax ID (64-bit unsigned 19 digit integer) but that's more use when writing to Zoho Books. Within ZohoCRM, we only need the name and in this case the percentage rate:

copy raw

string standalone.fn_Xero_MapTaxRates()
{
	/* *******************************************************************************
	Function:       string standalone.fn_Xero_MapTaxRates()
	Label:          Fn - Xero - Map Tax Rates
	Trigger:        Standalone / On-Demand
	Purpose:	 	Function used to map Xero tax rates to Zoho ones
	Inputs:         -
	Outputs:        -

	Date Created:   2025-10-13 (Joel Lipman)
					- Initial release
	Date Modified:	???
					- ???

	More Information:
					Ensure that all possible tax rates in Xero match those in Zoho; the output of this function will give you a copy&paste list

	******************************************************************************* */
	// init
	v_Output = "";
	m_OutputTaxRates = Map();
	m_ZohoTaxRatesByName = Map();
	m_ZohoTaxRatesByRate = Map();
	l_ZohoCrmTaxRatesList = List();
	v_XeroIntegrationRecordID = "123456000000789012";
	//
	r_IntegrationRecord = zoho.crm.getRecordById("Integrations",v_XeroIntegrationRecordID);
	v_TenantID = r_IntegrationRecord.get("Tenant_ID");
	v_AccessToken = standalone.fn_API_GetXeroAccessToken();
	//
	// get Zoho tax rates
	r_TaxRates = invokeurl
	[
		url :"https://www.zohoapis.eu/crm/v8/org/taxes"
		type :GET
		connection:"ab_crm"
	];
	r_OrgTaxes = ifnull(r_TaxRates.get("org_taxes"),Map());
	l_TaxRates = ifnull(r_OrgTaxes.get("taxes"),List());
	//
	for each  m_TaxRate in l_TaxRates
	{
		if(!isNull(m_TaxRate.get("id")))
		{
			m_ZohoTaxRatesByName.put(m_TaxRate.get("name"),m_TaxRate.get("id"));
			m_ZohoTaxRatesByRate.put(m_TaxRate.get("value").toDecimal().round(3).toString(),m_TaxRate.get("id"));
		}
	}
	info m_ZohoTaxRatesByName;
	info m_ZohoTaxRatesByRate;
	//
	// do Xero stuff
	if(v_AccessToken != "")
	{
		m_Header = Map();
		m_Header.put("Authorization","Bearer " + v_AccessToken);
		m_Header.put("Accept","application/json");
		m_Header.put("Xero-tenant-id",v_TenantID);
		//
		// get CRM invoice details
		v_TaxRateEndpoint = "https://api.xero.com/api.xro/2.0/TaxRates";
		r_XeroResponse = invokeurl
		[
			url :v_TaxRateEndpoint
			type :GET
			headers:m_Header
		];
		info r_XeroResponse;
		l_TaxRates = ifnull(r_XeroResponse.get("TaxRates"),List());
		for each  m_ThisTaxRate in l_TaxRates
		{
			if(!isNull(m_ThisTaxRate.get("Name")))
			{
				v_ThisXeroTaxRateName = m_ThisTaxRate.get("Name");
				v_ThisXeroTaxRateRef = m_ThisTaxRate.get("TaxType");
				//
				v_ThisXeroTaxRateRate = 0.0;
				if(!isEmpty(m_ThisTaxRate.get("TaxComponents")))
				{
					for each  m_TaxComponent in m_ThisTaxRate.get("TaxComponents")
					{
						v_ThisXeroTaxRateRate = m_TaxComponent.get("Rate").toDecimal().round(3).toString();
					}
				}
				//
				// map
				if(!isNull(m_ZohoTaxRatesByName.get(v_ThisXeroTaxRateName)))
				{
					m_OutputTaxRates.put(v_ThisXeroTaxRateRef,v_ThisXeroTaxRateRate);
				}
				else if(!isNull(m_ZohoTaxRatesByName.get(v_ThisXeroTaxRateRef)))
				{
					m_OutputTaxRates.put(v_ThisXeroTaxRateRef,v_ThisXeroTaxRateRate);
				}
				else if(!isNull(m_ZohoTaxRatesByRate.get(v_ThisXeroTaxRateRate)))
				{
					m_OutputTaxRates.put(v_ThisXeroTaxRateRef,v_ThisXeroTaxRateRate);
				}
				//
				// list to copy in Zoho (copy and paste)
				m_ZohoCrmTaxRatesList = Map();
				m_ZohoCrmTaxRatesList.put(v_ThisXeroTaxRateRef,v_ThisXeroTaxRateRate);
				l_ZohoCrmTaxRatesList.add(m_ZohoCrmTaxRatesList);
			}
		}
		info "Copy this list: ";
		info l_ZohoCrmTaxRatesList;
		v_Output = m_OutputTaxRates.toString();
	}
	return v_Output;
}

string standalone.fn_Xero_MapTaxRates()
{
/* *******************************************************************************
Function: string standalone.fn_Xero_MapTaxRates()
Label: Fn - Xero - Map Tax Rates
Trigger: Standalone / On-Demand
Purpose: Function used to map Xero tax rates to Zoho ones
Inputs: -
Outputs: -
Date Created: 2025-10-13 (Joel Lipman)
- Initial release
Date Modified: ???
- ???
More Information:
Ensure that all possible tax rates in Xero match those in Zoho; the output of this function will give you a copy&paste list
******************************************************************************* */
// init
v_Output = "";
m_OutputTaxRates = Map();
m_ZohoTaxRatesByName = Map();
m_ZohoTaxRatesByRate = Map();
l_ZohoCrmTaxRatesList = List();
v_XeroIntegrationRecordID = "123456000000789012";
//
r_IntegrationRecord = zoho.crm.getRecordById("Integrations",v_XeroIntegrationRecordID);
v_TenantID = r_IntegrationRecord.get("Tenant_ID");
v_AccessToken = standalone.fn_API_GetXeroAccessToken();
//
// get Zoho tax rates
r_TaxRates = invokeUrl
[
url :"https://www.zohoapis.eu/crm/v8/org/taxes"
type :GET
connection:"ab_crm"
];
r_OrgTaxes = ifnull(r_TaxRates.get("org_taxes"),Map());
l_TaxRates = ifnull(r_OrgTaxes.get("taxes"),List());
//
for each m_TaxRate in l_TaxRates
{
if(!isNull(m_TaxRate.get("id")))
{
m_ZohoTaxRatesByName.put(m_TaxRate.get("name"),m_TaxRate.get("id"));
m_ZohoTaxRatesByRate.put(m_TaxRate.get("value").toDecimal().round(3).toString(),m_TaxRate.get("id"));
}
}
info m_ZohoTaxRatesByName;
info m_ZohoTaxRatesByRate;
//
// do Xero stuff
if(v_AccessToken != "")
{
m_Header = Map();
m_Header.put("Authorization","Bearer " + v_AccessToken);
m_Header.put("Accept","application/json");
m_Header.put("Xero-tenant-id",v_TenantID);
//
// get CRM invoice details
v_TaxRateEndpoint = "https://api.xero.com/api.xro/2.0/TaxRates";
r_XeroResponse = invokeUrl
[
url :v_TaxRateEndpoint
type :GET
headers:m_Header
];
info r_XeroResponse;
l_TaxRates = ifnull(r_XeroResponse.get("TaxRates"),List());
for each m_ThisTaxRate in l_TaxRates
{
if(!isNull(m_ThisTaxRate.get("Name")))
{
v_ThisXeroTaxRateName = m_ThisTaxRate.get("Name");
v_ThisXeroTaxRateRef = m_ThisTaxRate.get("TaxType");
//
v_ThisXeroTaxRateRate = 0.0;
if(!isEmpty(m_ThisTaxRate.get("TaxComponents")))
{
for each m_TaxComponent in m_ThisTaxRate.get("TaxComponents")
{
v_ThisXeroTaxRateRate = m_TaxComponent.get("Rate").toDecimal().round(3).toString();
}
}
//
// map
if(!isNull(m_ZohoTaxRatesByName.get(v_ThisXeroTaxRateName)))
{
m_OutputTaxRates.put(v_ThisXeroTaxRateRef,v_ThisXeroTaxRateRate);
}
else if(!isNull(m_ZohoTaxRatesByName.get(v_ThisXeroTaxRateRef)))
{
m_OutputTaxRates.put(v_ThisXeroTaxRateRef,v_ThisXeroTaxRateRate);
}
else if(!isNull(m_ZohoTaxRatesByRate.get(v_ThisXeroTaxRateRate)))
{
m_OutputTaxRates.put(v_ThisXeroTaxRateRef,v_ThisXeroTaxRateRate);
}
//
// list to copy in Zoho (copy and paste)
m_ZohoCrmTaxRatesList = Map();
m_ZohoCrmTaxRatesList.put(v_ThisXeroTaxRateRef,v_ThisXeroTaxRateRate);
l_ZohoCrmTaxRatesList.add(m_ZohoCrmTaxRatesList);
}
}
info "Copy this list: ";
info l_ZohoCrmTaxRatesList;
v_Output = m_OutputTaxRates.toString();
}
return v_Output;
}

fn_Xero_GetQuotes
The main function that took me over half a day:

copy raw

string standalone.fn_Xero_GetQuotes()
{
/* *******************************************************************************
	Function:       string standalone.fn_Xero_GetQuotes()
	Label:          Fn - Xero - Get Quotes
	Trigger:        Standalone / On-Demand / Callable
	Purpose:	 	Function to get the first page of most recent quotes from Xero and pull them into ZohoCRM
	Inputs:         -
	Outputs:        -

	Date Created:   2025-10-13 (Joel Lipman)
					- Initial release
					- Parsing Xero Dates and Times to include timezone of Xero instance for accurate time comparisons
	Date Modified:	???
					- ???

	More Information:
					http://www.joellipman.com/articles/crm/zoho/zoho-deluge-sync-to-xero-api.html

	******************************************************************************* */
	//
	// init
	v_OutputMessage = "ERROR: No Access Token or Tenant Connection specified.";
	v_Count_FoundInXero = 0;
	v_Count_Created = 0;
	v_Count_Updated = 0;
	v_AccessToken = "";
	l_Pages = {1};
	v_PageSize = 1;
	b_Debug = false;
	//
	// Login to Xero > Accounting > Accounting Settings > Financial Settings > Time Zone
	v_XeroTimeZone = "Europe/London";
	//
	// hard-code Zoho timezone
	v_ZohoTimeZone = "Europe/London";
	//
	// Xero Quote Statuses vs your CRM Quote Statuses
	m_TranslateStatuses = Map();
	m_TranslateStatuses.put("DRAFT","Draft");
	m_TranslateStatuses.put("SENT","Sent to Customer");
	m_TranslateStatuses.put("DECLINED","Cancelled / Rejected");
	m_TranslateStatuses.put("ACCEPTED","Approved by Customer");
	m_TranslateStatuses.put("INVOICED","Converted to Sales Order");
	m_TranslateStatuses.put("DELETED","Cancelled / Rejected");
	//
	// Xero Tax Rates translated to Zoho Tax Rate (manual reference)
	r_ZohoTaxRates = standalone.fn_Xero_MapTaxRates();
	m_ZohoTaxRates = r_ZohoTaxRates.toMap();
	if(b_Debug)
	{
		info m_ZohoTaxRates;
	}
	//
	// enter the CRM record ID of your integrations record (Xero Integration API)
	v_TokenCrmID = 123456000000789012;
	r_TokenDetails = zoho.crm.getRecordById("Integrations",v_TokenCrmID);
	v_DataEndpoint = ifnull(r_TokenDetails.get("Data_Endpoint"),"");
	v_TenantID = ifnull(r_TokenDetails.get("Tenant_ID"),"");
	//
	// get access token (does not need REST API url as we're calling it from within CRM)
	v_AccessToken = standalone.fn_API_GetXeroAccessToken();
	//
	// do Xero stuff
	if(v_AccessToken != "" && v_TenantID != "")
	{
		// set header
		m_Header = Map();
		m_Header.put("Authorization","Bearer " + v_AccessToken);
		m_Header.put("Accept","application/json");
		m_Header.put("Xero-tenant-id",v_TenantID);
		//
		// get Xero Quotes (page 1 - first 100 - default order is updated date)
		for each  v_Page in l_Pages
		{
			m_Params = Map();
			m_Params.put("page",v_Page);
			//
			// keep the page size low as this function will be creating contacts and products if required
			m_Params.put("pageSize",v_PageSize);
			//
			// order by date descending (most recent first) - sometimes need to use Date%20DESC
			m_Params.put("order","UpdatedDateUTC DESC");
			//
			// get the first page of Xero Quotes
			r_AllXeroQuotes = invokeurl
			[
				url :v_DataEndpoint + "/Quotes"
				type :GET
				parameters:m_Params
				headers:m_Header
			];
			if(b_Debug)
			{
				info r_AllXeroQuotes;
			}
			//
			if(!isnull(r_AllXeroQuotes.get("Quotes")))
			{
				for each  m_ThisQuote in r_AllXeroQuotes.get("Quotes")
				{
					if(!isnull(m_ThisQuote.get("QuoteID")))
					{
						//
						// counter
						v_Count_FoundInXero = v_Count_FoundInXero + 1;
						//
						// Xero Quote identifier
						v_XeroQuoteID = m_ThisQuote.get("QuoteID");
						info "From Xero:";
						info m_ThisQuote;
						//
						m_UpsertCrmQuote = Map();
						m_UpsertCrmQuote.put("Subject",m_ThisQuote.get("QuoteNumber"));
						m_UpsertCrmQuote.put("Xero_Reference",ifnull(m_ThisQuote.get("Reference"),"").trim());
						m_UpsertCrmQuote.put("Terms_and_Conditions",m_ThisQuote.get("Terms"));
						//
						// some standard CRM Quote fields we can populate
						v_CrmQuoteStatus = m_TranslateStatuses.get(m_ThisQuote.get("Status"));
						m_UpsertCrmQuote.put("Quote_Stage",v_CrmQuoteStatus);
						if(!isNull(m_ThisQuote.get("Date")))
						{
							v_XeroQuoteDate = m_ThisQuote.get("Date");
							// quote module has /Date(1744675200000)/ as opposed to invoice which includes timezone
							d_XeroQuoteDate = v_XeroQuoteDate.replaceAll("/Date\((\d+)([+-]\d{4})?\)/","$1",false).toLong().toTime("yyyy-MM-dd HH:mm:ss",v_XeroTimeZone);
							m_UpsertCrmQuote.put("Quote_Date",d_XeroQuoteDate.toString("yyyy-MM-dd"));
						}
						if(!isNull(m_ThisQuote.get("ExpiryDate")))
						{
							v_XeroQuoteDueDate = m_ThisQuote.get("ExpiryDate");
							d_XeroQuoteDueDate = v_XeroQuoteDueDate.replaceAll("/Date\((\d+)([+-]\d{4})?\)/","$1",false).toLong().toTime("yyyy-MM-dd HH:mm:ss",v_XeroTimeZone);
							m_UpsertCrmQuote.put("Valid_Till",d_XeroQuoteDueDate.toString("yyyy-MM-dd"));
						}
						else 
						{
							m_UpsertCrmQuote.put("Valid_Till",d_XeroQuoteDate.addDay(30).toString("yyyy-MM-dd"));
						}
						m_UpsertCrmQuote.put("Currency",m_ThisQuote.get("CurrencyCode"));
						m_UpsertCrmQuote.put("Exchange_Rate",m_ThisQuote.get("CurrencyRate"));
						//
						// some custom fields I created in CRM to store the data
						m_UpsertCrmQuote.put("Xero_Ref_ID",m_ThisQuote.get("QuoteID"));
						m_UpsertCrmQuote.put("Xero_Updated",zoho.currenttime.toString("yyyy-MM-dd'T'HH:mm:ss",v_ZohoTimeZone));
						//
						// -------------------------------- Quote Customer --------------------------------
						//
						// initialize
						v_CrmAccountID = "";
						v_CrmContactID = "";
						v_CrmPhone = "";
						v_CrmMobile = "";
						b_CreateAccount = true;
						b_CreateContact = true;
						//
						// set date/time of account last sync'd to Xero (100 years ago by default - so that it will be oldest)
						d_CrmAccountLastUpdated = zoho.currenttime.toString("yyyy-MM-dd HH:mm:ss").toTime().subYear(100);
						v_XeroContactID = m_ThisQuote.get("Contact").get("ContactID");
						v_XeroContactName = m_ThisQuote.get("Contact").get("Name");
						//
						// search CRM for this account/customer
						l_SearchAccounts = zoho.crm.searchRecords("Accounts","Xero_Ref_ID:equals:" + v_XeroContactID,1,2,{"approved":"both","converted":"both"});
						for each  m_SearchAccount in l_SearchAccounts
						{
							if(!isNull(m_SearchAccount.get("id")))
							{
								b_CreateAccount = false;
								v_CrmAccountID = m_SearchAccount.get("id");
								//
								// if sync'd before then let's use that date/time
								d_CrmAccountLastUpdated = ifnull(m_SearchAccount.get("Xero_Updated"),zoho.currenttime).toString("yyyy-MM-dd HH:mm:ss","Europe/London").toTime();
								if(b_Debug)
								{
									info "Found CRM Account: " + v_CrmAccountID;
								}
							}
						}
						//
						// get account/contact details from Xero (Quote doesn't necessarily hold the details: address, phone, etc)
						r_XeroContact = invokeurl
						[
							url :v_DataEndpoint + "/Contacts/" + v_XeroContactID
							type :GET
							parameters:m_Params
							headers:m_Header
						];
						l_XeroContacts = ifnull(r_XeroContact.get("Contacts"),List());
						for each  m_XeroContact in l_XeroContacts
						{
							if(!isNull(m_XeroContact.get("ContactID")))
							{
								//
								// to check if we want to update the CRM record for the account
								v_XeroTime = m_XeroContact.get("UpdatedDateUTC");
								d_XeroAccountLastUpdated = v_XeroTime.replaceAll("/Date\((\d+)([+-]\d{4})?\)/","$1",false).toLong().toTime("yyyy-MM-dd HH:mm:ss",v_XeroTimeZone);
								//
								// build upsert for CRM account
								m_CrmAccount = Map();
								m_CrmAccount.put("Account_Name",m_ThisQuote.get("Contact").get("Name"));
								m_CrmAccount.put("Xero_Ref_ID",m_XeroContact.get("ContactID"));
								m_CrmAccount.put("Xero_Updated",zoho.currenttime.toString("yyyy-MM-dd'T'HH:mm:ss",v_ZohoTimeZone));
								//
								// addresses
								for each  m_XeroAddress in m_XeroContact.get("Addresses")
								{
									if(!isNull(m_XeroAddress.get("AddressLine1")))
									{
										v_XeroAddressLine1 = m_XeroAddress.get("AddressLine1");
										v_XeroAddressLine2 = m_XeroAddress.get("AddressLine2");
										v_XeroAddressCity = m_XeroAddress.get("City");
										v_XeroAddressZip = m_XeroAddress.get("PostalCode");
										v_XeroAddressAttn = m_XeroAddress.get("AttentionTo");
									}
								}
								//
								l_AddressStreet = List({v_XeroAddressLine1});
								if(!isBlank(v_XeroAddressLine2))
								{
									l_AddressStreet.add(v_XeroAddressLine2);
								}
								m_CrmAccount.put("Billing_Street",l_AddressStreet.toString(", "));
								m_CrmAccount.put("Billing_City",v_XeroAddressCity);
								m_CrmAccount.put("Billing_Code",v_XeroAddressZip);
								//
								// loop through phones
								for each  m_XeroPhone in m_XeroContact.get("Phones")
								{
									if(!isNull(m_XeroPhone.get("PhoneNumber")))
									{
										v_XeroPhoneType = m_XeroPhone.get("PhoneType");
										l_XeroFullPhoneNumberParts = List();
										if(!isNull(m_XeroPhone.get("PhoneCountryCode")))
										{
											l_XeroFullPhoneNumberParts.add(m_XeroPhone.get("PhoneCountryCode"));
										}
										if(!isNull(m_XeroPhone.get("PhoneAreaCode")))
										{
											l_XeroFullPhoneNumberParts.add(m_XeroPhone.get("PhoneAreaCode"));
										}
										if(!isNull(m_XeroPhone.get("PhoneNumber")))
										{
											l_XeroFullPhoneNumberParts.add(m_XeroPhone.get("PhoneNumber"));
										}
										v_XeroFullPhoneNumber = l_XeroFullPhoneNumberParts.toString(" ");
										if(v_XeroPhoneType == "DEFAULT" || v_XeroPhoneType == "PHONE")
										{
											v_CrmPhone = v_XeroFullPhoneNumber;
										}
										else if(v_XeroPhoneType == "MOBILE")
										{
											v_CrmMobile = v_XeroFullPhoneNumber;
										}
									}
								}
								m_CrmAccount.put("Phone",v_CrmPhone);
								//
								// balances
								v_XeroReceivables = 0.0;
								v_XeroPayables = 0.0;
								for each  m_XeroBalance in m_XeroContact.get("Balances")
								{
									if(!isNull(m_XeroBalance.get("AccountsReceivable")))
									{
										v_XeroReceivables = m_XeroBalance.get("AccountsReceivable").get("Outstanding");
										v_XeroReceivables = v_XeroReceivables + m_XeroBalance.get("AccountsReceivable").get("Overdue");
										v_XeroReceivables = v_XeroReceivables * -1;
									}
									if(!isNull(m_XeroBalance.get("AccountsPayable")))
									{
										v_XeroPayables = m_XeroBalance.get("AccountsPayable").get("Outstanding");
										v_XeroPayables = v_XeroPayables + m_XeroBalance.get("AccountsReceivable").get("Overdue");
									}
								}
								v_XeroBalance = v_XeroPayables - v_XeroReceivables;
								m_CrmAccount.put("Xero_Balance",v_XeroBalance);
								//
								// create CRM account for other contact records
								if(b_CreateAccount)
								{
									r_CreateAccount = zoho.crm.createRecord("Accounts",m_CrmAccount);
									if(b_Debug)
									{
										info "Creating CRM Account: " + r_CreateAccount;
									}
									if(!isNull(r_CreateAccount.getJSON("id")))
									{
										v_CrmAccountID = r_CreateAccount.get("id");
									}
								}
								//
								// create a contact
								v_SearchContactsCriteria = "Email:equals:" + if(isBlank(m_XeroContact.get("EmailAddress")),"Unknown",m_XeroContact.get("EmailAddress"));
								l_SearchContacts = zoho.crm.searchRecords("Contacts",v_SearchContactsCriteria);
								for each  m_SearchContact in l_SearchContacts
								{
									if(!isNull(m_SearchContact.getJSON("id")))
									{
										b_CreateContact = false;
										v_CrmContactID = m_SearchContact.get("id");
										if(b_Debug)
										{
											info "Found CRM Contact: " + v_CrmContactID;
										}
									}
								}
								//
								// build upsert for CRM contact
								m_CrmContact = Map();
								m_CrmContact.put("First_Name",m_XeroContact.get("FirstName"));
								// last name is mandatory for a CRM contact so we're going to put a placeholder one if this is not given
								v_CrmContactLastName = ifnull(m_XeroContact.get("LastName"),"-Unknown-");
								m_CrmContact.put("Last_Name",v_CrmContactLastName);
								m_CrmContact.put("Email",m_XeroContact.get("EmailAddress"));
								m_CrmContact.put("Phone",v_CrmPhone);
								m_CrmContact.put("Mobile",v_CrmMobile);
								m_CrmContact.put("Xero_Ref_ID",m_XeroContact.get("ContactID"));
								m_CrmContact.put("Xero_Updated",zoho.currenttime.toString("yyyy-MM-dd'T'HH:mm:ss",v_ZohoTimeZone));
								m_CrmContact.put("Mailing_Street",l_AddressStreet.toString(", "));
								m_CrmContact.put("Mailing_City",v_XeroAddressCity);
								m_CrmContact.put("Mailing_Zip",v_XeroAddressZip);
								m_CrmContact.put("Account_Name",v_CrmAccountID);
								// last name is mandatory, let's not bother if it wasn't provided
								if(b_CreateContact && v_CrmContactLastName != "-Unknown-")
								{
									r_CreateContact = zoho.crm.createRecord("Contacts",m_CrmContact);
									if(b_Debug)
									{
										info "Creating Primary Contact: " + r_CreateContact;
									}
									if(!isNull(r_CreateContact.get("id")))
									{
										v_CrmContactID = r_CreateContact.get("id");
									}
									//
									// create other contacts (retain the map and only change first name, last name, and email)
									for each  m_OtherContact in m_XeroContact.get("ContactPersons")
									{
										m_CrmContact.put("First_Name",m_OtherContact.get("FirstName"));
										m_CrmContact.put("Last_Name",m_OtherContact.get("LastName"));
										m_CrmContact.put("Email",m_OtherContact.get("EmailAddress"));
										r_CreateContact2 = zoho.crm.createRecord("Contacts",m_CrmContact);
										if(b_Debug)
										{
											info "Creating Secondary Contact: " + r_CreateContact2;
										}
									}
								}
							}
						}
						//
						// if Xero record is more recently updated than the CRM one, then update the account
						if(d_XeroAccountLastUpdated >= d_CrmAccountLastUpdated)
						{
							r_UpdateCrmAccount = zoho.crm.updateRecord("Accounts",v_CrmAccountID,m_CrmAccount);
							r_UpdateCrmContact = zoho.crm.updateRecord("Contacts",v_CrmContactID,m_CrmContact);
						}
						//
						// add account/contact to the Quote
						m_UpsertCrmQuote.put("Account_Name",v_CrmAccountID);
						m_UpsertCrmQuote.put("Contact_Name",v_CrmContactID);
						//
						// -------------------------------- Quote Line Items --------------------------------
						//
						// initializing
						l_CrmLineItems = List();
						//
						// loop through line items on the Xero Quote
						for each  m_XeroLineItem in m_ThisQuote.get("LineItems")
						{
							//
							// initialize
							v_CrmProductID = "";
							l_CrmProductAvailableTaxes = List();
							//
							// checking this is a valid line item and not an error message by it having an ItemCode
							//v_CrmProductName = ifnull(m_XeroLineItem.get("ItemCode"),m_XeroLineItem.get("Description"));
							//v_CrmProductName = if(isBlank(v_CrmProductName),ifnull(m_XeroLineItem.get("Item"),{"Name":"Product"}).get("Name"),"Product").replaceAll("\n", " ");
							//
							// override for this client
							v_CrmProductName = m_XeroLineItem.get("Description");
							if(b_Debug)
							{
								info "This Line Item Product: " + v_CrmProductName;
							}
							if(!isBlank(v_CrmProductName))
							{
								//
								// adapt this search for matching CRM product based on how the product is referred to in Xero (eg. if no code or name, then use description)
								v_CrmProductName = if(v_CrmProductName.length() >= 200,v_CrmProductName.subString(0,199),v_CrmProductName);
								v_CrmProductNameSafe = zoho.encryption.urlEncode(v_CrmProductName);
								v_SearchCriteria = "Product_Name:equals:" + v_CrmProductNameSafe;
								l_SearchProducts = zoho.crm.searchRecords("Products",v_SearchCriteria,1,2,{"approved":"both"});
								if(b_Debug)
								{
									info "Searching CRM Products: " + v_SearchCriteria + " RESULT(S): " + l_SearchProducts;
								}
								for each  m_SearchProduct in l_SearchProducts
								{
									if(!isNull(m_SearchProduct.get("id")))
									{
										v_CrmProductID = m_SearchProduct.get("id");
										l_CrmProductAvailableTaxes = ifnull(m_SearchProduct.get("Tax"),List());
									}
								}
								//
								// build up creation or updation of product
								m_CrmProduct = Map();
								//
								// some companies don't use the product lookup in Xero so you would need a placeholder product from CRM.
								if(!isNull(m_XeroLineItem.get("Item")))
								{
									v_CrmProductName = m_XeroLineItem.get("Item").get("Name");
									m_CrmProduct.put("Xero_Ref_ID",m_XeroLineItem.get("Item").get("ItemID"));
									m_CrmProduct.put("Product_Code",m_XeroLineItem.get("Item").get("Code"));
								}
								m_CrmProduct.put("Product_Name",v_CrmProductName);
								m_CrmProduct.put("Product_Active",true);
								//
								// eg. Delivery should just be the name of the product not both the title and description
								if(!v_CrmProductName.equalsIgnoreCase(m_XeroLineItem.get("Description")))
								{
									m_CrmProduct.put("Description",m_XeroLineItem.get("Description"));
								}
								m_CrmProduct.put("Unit_Price",m_XeroLineItem.get("UnitAmount"));
								//
								// map over tax
								v_XeroLineItemTaxAmount = ifnull(m_XeroLineItem.get("TaxAmount"),0).toDecimal();
								m_CrmProduct.put("Taxable",true);
								if(!l_CrmProductAvailableTaxes.contains(m_XeroLineItem.get("TaxType")))
								{
									l_CrmProductAvailableTaxes.add(m_XeroLineItem.get("TaxType"));
								}
								m_CrmProduct.put("Tax",l_CrmProductAvailableTaxes);
								//
								m_CrmProduct.put("Xero_Updated",zoho.currenttime.toString("yyyy-MM-dd'T'HH:mm:ss",v_ZohoTimeZone));
								//
								// couldn't find it so let's create it
								if(v_CrmProductID == "")
								{
									r_CreateCrmProduct = zoho.crm.createRecord("Products",m_CrmProduct);
									if(b_Debug)
									{
										info "Creating/Updated CRM Product: " + r_CreateCrmProduct;
									}
									if(!isNull(r_CreateCrmProduct.get("id")))
									{
										v_CrmProductID = r_CreateCrmProduct.get("id");
									}
									else if(r_CreateCrmProduct.get("code").equalsIgnoreCase("DUPLICATE_DATA"))
									{
										v_CrmProductID = r_CreateCrmProduct.get("details").get("id");
										if(b_Debug)
										{
											info "Duplicate CRM Product: Re-using " + v_CrmProductID;
										}
									}
								}
								//
								// update the product (mainly for new applicable taxes)
								r_UpdateCrmProduct = zoho.crm.updateRecord("Products",v_CrmProductID,m_CrmProduct);
								if(b_Debug)
								{
									info "Update CRM Product";
									info m_CrmProduct;
									info r_UpdateCrmProduct;
								}
								//
								// let's do the rest of the line item (note that we are going to upsert using CRM API v8)
								m_CrmLineItem = Map();
								m_CrmLineItem.put("Product_Name",v_CrmProductID);
								m_CrmLineItem.put("Description","");
								if(!v_CrmProductName.equalsIgnoreCase(m_XeroLineItem.get("Description")))
								{
									m_CrmLineItem.put("Description",m_XeroLineItem.get("Description"));
								}
								m_CrmLineItem.put("List_Price",m_XeroLineItem.get("UnitAmount"));
								m_CrmLineItem.put("Quantity",m_XeroLineItem.get("Quantity"));
								l_CrmLineItemTax = List();
								m_CrmLineItemTax = Map();
								v_ZohoLineItemTaxPercent = ifnull(m_ZohoTaxRates.get(m_XeroLineItem.get("TaxType")),0.0).toDecimal();
								m_CrmLineItemTax.put("percentage",v_ZohoLineItemTaxPercent);
								m_CrmLineItemTax.put("name",ifnull(m_XeroLineItem.get("TaxType"),"NO VAT"));
								m_CrmLineItemTax.put("value",m_XeroLineItem.get("TaxAmount"));
								l_CrmLineItemTax.add(m_CrmLineItemTax);
								m_CrmLineItem.put("Line_Tax",l_CrmLineItemTax);
								v_DiscountPercent = ifnull(m_XeroLineItem.get("DiscountRate"),0.0);
								v_DiscountAmount = ifnull(m_XeroLineItem.get("DiscountAmount"),0.0);
								if(v_DiscountPercent != 0)
								{
									// just qty vs unit excluding discount and tax
									v_LineItemTotal = m_XeroLineItem.get("Quantity") * m_XeroLineItem.get("UnitAmount");
									v_DiscountFactor = v_DiscountPercent / 100;
									v_DiscountAmount = v_LineItemTotal * v_DiscountFactor;
								}
								m_CrmLineItem.put("Discount",v_DiscountAmount);
								m_CrmLineItem.put("Tax",m_XeroLineItem.get("TaxAmount"));
								l_CrmLineItems.add(m_CrmLineItem);
							}
						}
						//
						// if the CRM Quote already exists, we are going to upsert so we need to remove the current line items in the CRM Quote
						l_SearchQuotes = zoho.crm.searchRecords("Quotes","Xero_Ref_ID:equals:" + v_XeroQuoteID);
						for each  m_QuoteResult in l_SearchQuotes
						{
							if(!isNull(m_QuoteResult.get("id")))
							{
								for each  m_ExistingLineItem in m_QuoteResult.get("Product_Details")
								{
									m_MiniDeleteMe = Map();
									m_MiniDeleteMe.put("id",m_ExistingLineItem.get("id"));
									m_MiniDeleteMe.put("_delete",null);
									l_CrmLineItems.add(m_MiniDeleteMe);
								}
							}
						}
						//
						// add line items to the Quote
						m_UpsertCrmQuote.put("Quoted_Items",l_CrmLineItems);
						//
						// -------------------------------- Quote Address --------------------------------
						//
						//
						// let's add the billing address retrieved earlier to the Quote
						m_UpsertCrmQuote.put("Billing_Street",l_AddressStreet.toString(", "));
						m_UpsertCrmQuote.put("Billing_City",v_XeroAddressCity);
						m_UpsertCrmQuote.put("Billing_Code",v_XeroAddressZip);
						//
						// -------------------------------- Quote Upsert --------------------------------
						//
						// initializing
						v_UpsertedCrmID = "";
						//
						// let's upsert
						info "Sending to Zoho: ";
						info m_UpsertCrmQuote;
						m_Data = Map();
						m_Data.put("data",List({m_UpsertCrmQuote}));
						m_Data.put("trigger",{"workflow","approval","blueprint"});
						r_UpsertQuote = invokeurl
						[
							url :"https://www.zohoapis.eu/crm/v8/Quotes/upsert"
							type :POST
							parameters:m_Data.toString()
							connection:"ab_crm"
						];
						if(b_Debug)
						{
							info r_UpsertQuote;
						}
						l_ResponseData = ifnull(r_UpsertQuote.get("data"),List());
						for each  m_ResponseData in l_ResponseData
						{
							if(!isNull(m_ResponseData.get("code")))
							{
								v_Action = m_ResponseData.get("action");
								if(!isNull(m_ResponseData.get("details")))
								{
									v_UpsertedCrmID = m_ResponseData.get("details").get("id");
								}
							}
						}
						if(v_Action == "insert")
						{
							v_Count_Created = v_Count_Created + 1;
							//
							// override the auto-naming to take the Quote Subject from Xero
							if(v_UpsertedCrmID != "")
							{
								m_OverrideCrmQuote = Map();
								m_OverrideCrmQuote.put("Subject",m_ThisQuote.get("QuoteNumber"));
								r_OverrideCrmQuote = zoho.crm.updateRecord("Quotes",v_UpsertedCrmID,m_OverrideCrmQuote);
								if(b_Debug)
								{
									info "Overriding CRM Quote Subject: " + r_OverrideCrmQuote;
								}
							}
						}
						else if(v_Action == "update")
						{
							v_Count_Updated = v_Count_Updated + 1;
						}
					}
				}
			}
		}
		v_OutputMessage = "Created " + v_Count_Created + " and Updated " + v_Count_Updated + " from " + v_Count_FoundInXero;
	}
	return v_OutputMessage;
}

string standalone.fn_Xero_GetQuotes()
{
/* *******************************************************************************
Function: string standalone.fn_Xero_GetQuotes()
Label: Fn - Xero - Get Quotes
Trigger: Standalone / On-Demand / Callable
Purpose: Function to get the first page of most recent quotes from Xero and pull them into ZohoCRM
Inputs: -
Outputs: -
Date Created: 2025-10-13 (Joel Lipman)
- Initial release
- Parsing Xero Dates and Times to include timezone of Xero instance for accurate time comparisons
Date Modified: ???
- ???
More Information:
http://www.joellipman.com/articles/crm/zoho/zoho-deluge-sync-to-xero-api.html
******************************************************************************* */
//
// init
v_OutputMessage = "ERROR: No Access Token or Tenant Connection specified.";
v_Count_FoundInXero = 0;
v_Count_Created = 0;
v_Count_Updated = 0;
v_AccessToken = "";
l_Pages = {1};
v_PageSize = 1;
b_Debug = false;
//
// Login to Xero > Accounting > Accounting Settings > Financial Settings > Time Zone
v_XeroTimeZone = "Europe/London";
//
// hard-code Zoho timezone
v_ZohoTimeZone = "Europe/London";
//
// Xero Quote Statuses vs your CRM Quote Statuses
m_TranslateStatuses = Map();
m_TranslateStatuses.put("DRAFT","Draft");
m_TranslateStatuses.put("SENT","Sent to Customer");
m_TranslateStatuses.put("DECLINED","Cancelled / Rejected");
m_TranslateStatuses.put("ACCEPTED","Approved by Customer");
m_TranslateStatuses.put("INVOICED","Converted to Sales Order");
m_TranslateStatuses.put("DELETED","Cancelled / Rejected");
//
// Xero Tax Rates translated to Zoho Tax Rate (manual reference)
r_ZohoTaxRates = standalone.fn_Xero_MapTaxRates();
m_ZohoTaxRates = r_ZohoTaxRates.toMap();
if(b_Debug)
{
info m_ZohoTaxRates;
}
//
// enter the CRM record ID of your integrations record (Xero Integration API)
v_TokenCrmID = 123456000000789012;
r_TokenDetails = zoho.crm.getRecordById("Integrations",v_TokenCrmID);
v_DataEndpoint = ifnull(r_TokenDetails.get("Data_Endpoint"),"");
v_TenantID = ifnull(r_TokenDetails.get("Tenant_ID"),"");
//
// get access token (does not need REST API url as we're calling it from within CRM)
v_AccessToken = standalone.fn_API_GetXeroAccessToken();
//
// do Xero stuff
if(v_AccessToken != "" && v_TenantID != "")
{
// set header
m_Header = Map();
m_Header.put("Authorization","Bearer " + v_AccessToken);
m_Header.put("Accept","application/json");
m_Header.put("Xero-tenant-id",v_TenantID);
//
// get Xero Quotes (page 1 - first 100 - default order is updated date)
for each v_Page in l_Pages
{
m_Params = Map();
m_Params.put("page",v_Page);
//
// keep the page size low as this function will be creating contacts and products if required
m_Params.put("pageSize",v_PageSize);
//
// order by date descending (most recent first) - sometimes need to use Date%20DESC
m_Params.put("order","UpdatedDateUTC DESC");
//
// get the first page of Xero Quotes
r_AllXeroQuotes = invokeUrl
[
url :v_DataEndpoint + "/Quotes"
type :GET
parameters:m_Params
headers:m_Header
];
if(b_Debug)
{
info r_AllXeroQuotes;
}
//
if(!isnull(r_AllXeroQuotes.get("Quotes")))
{
for each m_ThisQuote in r_AllXeroQuotes.get("Quotes")
{
if(!isnull(m_ThisQuote.get("QuoteID")))
{
//
// counter
v_Count_FoundInXero = v_Count_FoundInXero + 1;
//
// Xero Quote identifier
v_XeroQuoteID = m_ThisQuote.get("QuoteID");
info "From Xero:";
info m_ThisQuote;
//
m_UpsertCrmQuote = Map();
m_UpsertCrmQuote.put("Subject",m_ThisQuote.get("QuoteNumber"));
m_UpsertCrmQuote.put("Xero_Reference",ifnull(m_ThisQuote.get("Reference"),"").trim());
m_UpsertCrmQuote.put("Terms_and_Conditions",m_ThisQuote.get("Terms"));
//
// some standard CRM Quote fields we can populate
v_CrmQuoteStatus = m_TranslateStatuses.get(m_ThisQuote.get("Status"));
m_UpsertCrmQuote.put("Quote_Stage",v_CrmQuoteStatus);
if(!isNull(m_ThisQuote.get("Date")))
{
v_XeroQuoteDate = m_ThisQuote.get("Date");
// quote module has /Date(1744675200000)/ as opposed to invoice which includes timezone
d_XeroQuoteDate = v_XeroQuoteDate.replaceAll("/Date$(\d+)([+-]\d{4})?$/","$1",false).toLong().toTime("yyyy-MM-dd HH:mm:ss",v_XeroTimeZone);
m_UpsertCrmQuote.put("Quote_Date",d_XeroQuoteDate.toString("yyyy-MM-dd"));
}
if(!isNull(m_ThisQuote.get("ExpiryDate")))
{
v_XeroQuoteDueDate = m_ThisQuote.get("ExpiryDate");
d_XeroQuoteDueDate = v_XeroQuoteDueDate.replaceAll("/Date$(\d+)([+-]\d{4})?$/","$1",false).toLong().toTime("yyyy-MM-dd HH:mm:ss",v_XeroTimeZone);
m_UpsertCrmQuote.put("Valid_Till",d_XeroQuoteDueDate.toString("yyyy-MM-dd"));
}
else
{
m_UpsertCrmQuote.put("Valid_Till",d_XeroQuoteDate.addDay(30).toString("yyyy-MM-dd"));
}
m_UpsertCrmQuote.put("Currency",m_ThisQuote.get("CurrencyCode"));
m_UpsertCrmQuote.put("Exchange_Rate",m_ThisQuote.get("CurrencyRate"));
//
// some custom fields I created in CRM to store the data
m_UpsertCrmQuote.put("Xero_Ref_ID",m_ThisQuote.get("QuoteID"));
m_UpsertCrmQuote.put("Xero_Updated",zoho.currenttime.toString("yyyy-MM-dd'T'HH:mm:ss",v_ZohoTimeZone));
//
// -------------------------------- Quote Customer --------------------------------
//
// initialize
v_CrmAccountID = "";
v_CrmContactID = "";
v_CrmPhone = "";
v_CrmMobile = "";
b_CreateAccount = true;
b_CreateContact = true;
//
// set date/time of account last sync'd to Xero (100 years ago by default - so that it will be oldest)
d_CrmAccountLastUpdated = zoho.currenttime.toString("yyyy-MM-dd HH:mm:ss").toTime().subYear(100);
v_XeroContactID = m_ThisQuote.get("Contact").get("ContactID");
v_XeroContactName = m_ThisQuote.get("Contact").get("Name");
//
// search CRM for this account/customer
l_SearchAccounts = zoho.crm.searchRecords("Accounts","Xero_Ref_ID:equals:" + v_XeroContactID,1,2,{"approved":"both","converted":"both"});
for each m_SearchAccount in l_SearchAccounts
{
if(!isNull(m_SearchAccount.get("id")))
{
b_CreateAccount = false;
v_CrmAccountID = m_SearchAccount.get("id");
//
// if sync'd before then let's use that date/time
d_CrmAccountLastUpdated = ifnull(m_SearchAccount.get("Xero_Updated"),zoho.currenttime).toString("yyyy-MM-dd HH:mm:ss","Europe/London").toTime();
if(b_Debug)
{
info "Found CRM Account: " + v_CrmAccountID;
}
}
}
//
// get account/contact details from Xero (Quote doesn't necessarily hold the details: address, phone, etc)
r_XeroContact = invokeUrl
[
url :v_DataEndpoint + "/Contacts/" + v_XeroContactID
type :GET
parameters:m_Params
headers:m_Header
];
l_XeroContacts = ifnull(r_XeroContact.get("Contacts"),List());
for each m_XeroContact in l_XeroContacts
{
if(!isNull(m_XeroContact.get("ContactID")))
{
//
// to check if we want to update the CRM record for the account
v_XeroTime = m_XeroContact.get("UpdatedDateUTC");
d_XeroAccountLastUpdated = v_XeroTime.replaceAll("/Date$(\d+)([+-]\d{4})?$/","$1",false).toLong().toTime("yyyy-MM-dd HH:mm:ss",v_XeroTimeZone);
//
// build upsert for CRM account
m_CrmAccount = Map();
m_CrmAccount.put("Account_Name",m_ThisQuote.get("Contact").get("Name"));
m_CrmAccount.put("Xero_Ref_ID",m_XeroContact.get("ContactID"));
m_CrmAccount.put("Xero_Updated",zoho.currenttime.toString("yyyy-MM-dd'T'HH:mm:ss",v_ZohoTimeZone));
//
// addresses
for each m_XeroAddress in m_XeroContact.get("Addresses")
{
if(!isNull(m_XeroAddress.get("AddressLine1")))
{
v_XeroAddressLine1 = m_XeroAddress.get("AddressLine1");
v_XeroAddressLine2 = m_XeroAddress.get("AddressLine2");
v_XeroAddressCity = m_XeroAddress.get("City");
v_XeroAddressZip = m_XeroAddress.get("PostalCode");
v_XeroAddressAttn = m_XeroAddress.get("AttentionTo");
}
}
//
l_AddressStreet = List({v_XeroAddressLine1});
if(!isBlank(v_XeroAddressLine2))
{
l_AddressStreet.add(v_XeroAddressLine2);
}
m_CrmAccount.put("Billing_Street",l_AddressStreet.toString(", "));
m_CrmAccount.put("Billing_City",v_XeroAddressCity);
m_CrmAccount.put("Billing_Code",v_XeroAddressZip);
//
// loop through phones
for each m_XeroPhone in m_XeroContact.get("Phones")
{
if(!isNull(m_XeroPhone.get("PhoneNumber")))
{
v_XeroPhoneType = m_XeroPhone.get("PhoneType");
l_XeroFullPhoneNumberParts = List();
if(!isNull(m_XeroPhone.get("PhoneCountryCode")))
{
l_XeroFullPhoneNumberParts.add(m_XeroPhone.get("PhoneCountryCode"));
}
if(!isNull(m_XeroPhone.get("PhoneAreaCode")))
{
l_XeroFullPhoneNumberParts.add(m_XeroPhone.get("PhoneAreaCode"));
}
if(!isNull(m_XeroPhone.get("PhoneNumber")))
{
l_XeroFullPhoneNumberParts.add(m_XeroPhone.get("PhoneNumber"));
}
v_XeroFullPhoneNumber = l_XeroFullPhoneNumberParts.toString(" ");
if(v_XeroPhoneType == "DEFAULT" || v_XeroPhoneType == "PHONE")
{
v_CrmPhone = v_XeroFullPhoneNumber;
}
else if(v_XeroPhoneType == "MOBILE")
{
v_CrmMobile = v_XeroFullPhoneNumber;
}
}
}
m_CrmAccount.put("Phone",v_CrmPhone);
//
// balances
v_XeroReceivables = 0.0;
v_XeroPayables = 0.0;
for each m_XeroBalance in m_XeroContact.get("Balances")
{
if(!isNull(m_XeroBalance.get("AccountsReceivable")))
{
v_XeroReceivables = m_XeroBalance.get("AccountsReceivable").get("Outstanding");
v_XeroReceivables = v_XeroReceivables + m_XeroBalance.get("AccountsReceivable").get("Overdue");
v_XeroReceivables = v_XeroReceivables * -1;
}
if(!isNull(m_XeroBalance.get("AccountsPayable")))
{
v_XeroPayables = m_XeroBalance.get("AccountsPayable").get("Outstanding");
v_XeroPayables = v_XeroPayables + m_XeroBalance.get("AccountsReceivable").get("Overdue");
}
}
v_XeroBalance = v_XeroPayables - v_XeroReceivables;
m_CrmAccount.put("Xero_Balance",v_XeroBalance);
//
// create CRM account for other contact records
if(b_CreateAccount)
{
r_CreateAccount = zoho.crm.createRecord("Accounts",m_CrmAccount);
if(b_Debug)
{
info "Creating CRM Account: " + r_CreateAccount;
}
if(!isNull(r_CreateAccount.getJSON("id")))
{
v_CrmAccountID = r_CreateAccount.get("id");
}
}
//
// create a contact
v_SearchContactsCriteria = "Email:equals:" + if(isBlank(m_XeroContact.get("EmailAddress")),"Unknown",m_XeroContact.get("EmailAddress"));
l_SearchContacts = zoho.crm.searchRecords("Contacts",v_SearchContactsCriteria);
for each m_SearchContact in l_SearchContacts
{
if(!isNull(m_SearchContact.getJSON("id")))
{
b_CreateContact = false;
v_CrmContactID = m_SearchContact.get("id");
if(b_Debug)
{
info "Found CRM Contact: " + v_CrmContactID;
}
}
}
//
// build upsert for CRM contact
m_CrmContact = Map();
m_CrmContact.put("First_Name",m_XeroContact.get("FirstName"));
// last name is mandatory for a CRM contact so we're going to put a placeholder one if this is not given
v_CrmContactLastName = ifnull(m_XeroContact.get("LastName"),"-Unknown-");
m_CrmContact.put("Last_Name",v_CrmContactLastName);
m_CrmContact.put("Email",m_XeroContact.get("EmailAddress"));
m_CrmContact.put("Phone",v_CrmPhone);
m_CrmContact.put("Mobile",v_CrmMobile);
m_CrmContact.put("Xero_Ref_ID",m_XeroContact.get("ContactID"));
m_CrmContact.put("Xero_Updated",zoho.currenttime.toString("yyyy-MM-dd'T'HH:mm:ss",v_ZohoTimeZone));
m_CrmContact.put("Mailing_Street",l_AddressStreet.toString(", "));
m_CrmContact.put("Mailing_City",v_XeroAddressCity);
m_CrmContact.put("Mailing_Zip",v_XeroAddressZip);
m_CrmContact.put("Account_Name",v_CrmAccountID);
// last name is mandatory, let's not bother if it wasn't provided
if(b_CreateContact && v_CrmContactLastName != "-Unknown-")
{
r_CreateContact = zoho.crm.createRecord("Contacts",m_CrmContact);
if(b_Debug)
{
info "Creating Primary Contact: " + r_CreateContact;
}
if(!isNull(r_CreateContact.get("id")))
{
v_CrmContactID = r_CreateContact.get("id");
}
//
// create other contacts (retain the map and only change first name, last name, and email)
for each m_OtherContact in m_XeroContact.get("ContactPersons")
{
m_CrmContact.put("First_Name",m_OtherContact.get("FirstName"));
m_CrmContact.put("Last_Name",m_OtherContact.get("LastName"));
m_CrmContact.put("Email",m_OtherContact.get("EmailAddress"));
r_CreateContact2 = zoho.crm.createRecord("Contacts",m_CrmContact);
if(b_Debug)
{
info "Creating Secondary Contact: " + r_CreateContact2;
}
}
}
}
}
//
// if Xero record is more recently updated than the CRM one, then update the account
if(d_XeroAccountLastUpdated >= d_CrmAccountLastUpdated)
{
r_UpdateCrmAccount = zoho.crm.updateRecord("Accounts",v_CrmAccountID,m_CrmAccount);
r_UpdateCrmContact = zoho.crm.updateRecord("Contacts",v_CrmContactID,m_CrmContact);
}
//
// add account/contact to the Quote
m_UpsertCrmQuote.put("Account_Name",v_CrmAccountID);
m_UpsertCrmQuote.put("Contact_Name",v_CrmContactID);
//
// -------------------------------- Quote Line Items --------------------------------
//
// initializing
l_CrmLineItems = List();
//
// loop through line items on the Xero Quote
for each m_XeroLineItem in m_ThisQuote.get("LineItems")
{
//
// initialize
v_CrmProductID = "";
l_CrmProductAvailableTaxes = List();
//
// checking this is a valid line item and not an error message by it having an ItemCode
//v_CrmProductName = ifnull(m_XeroLineItem.get("ItemCode"),m_XeroLineItem.get("Description"));
//v_CrmProductName = if(isBlank(v_CrmProductName),ifnull(m_XeroLineItem.get("Item"),{"Name":"Product"}).get("Name"),"Product").replaceAll("\n", " ");
//
// override for this client
v_CrmProductName = m_XeroLineItem.get("Description");
if(b_Debug)
{
info "This Line Item Product: " + v_CrmProductName;
}
if(!isBlank(v_CrmProductName))
{
//
// adapt this search for matching CRM product based on how the product is referred to in Xero (eg. if no code or name, then use description)
v_CrmProductName = if(v_CrmProductName.length() >= 200,v_CrmProductName.subString(0,199),v_CrmProductName);
v_CrmProductNameSafe = zoho.encryption.urlEncode(v_CrmProductName);
v_SearchCriteria = "Product_Name:equals:" + v_CrmProductNameSafe;
l_SearchProducts = zoho.crm.searchRecords("Products",v_SearchCriteria,1,2,{"approved":"both"});
if(b_Debug)
{
info "Searching CRM Products: " + v_SearchCriteria + " RESULT(S): " + l_SearchProducts;
}
for each m_SearchProduct in l_SearchProducts
{
if(!isNull(m_SearchProduct.get("id")))
{
v_CrmProductID = m_SearchProduct.get("id");
l_CrmProductAvailableTaxes = ifnull(m_SearchProduct.get("Tax"),List());
}
}
//
// build up creation or updation of product
m_CrmProduct = Map();
//
// some companies don't use the product lookup in Xero so you would need a placeholder product from CRM.
if(!isNull(m_XeroLineItem.get("Item")))
{
v_CrmProductName = m_XeroLineItem.get("Item").get("Name");
m_CrmProduct.put("Xero_Ref_ID",m_XeroLineItem.get("Item").get("ItemID"));
m_CrmProduct.put("Product_Code",m_XeroLineItem.get("Item").get("Code"));
}
m_CrmProduct.put("Product_Name",v_CrmProductName);
m_CrmProduct.put("Product_Active",true);
//
// eg. Delivery should just be the name of the product not both the title and description
if(!v_CrmProductName.equalsIgnoreCase(m_XeroLineItem.get("Description")))
{
m_CrmProduct.put("Description",m_XeroLineItem.get("Description"));
}
m_CrmProduct.put("Unit_Price",m_XeroLineItem.get("UnitAmount"));
//
// map over tax
v_XeroLineItemTaxAmount = ifnull(m_XeroLineItem.get("TaxAmount"),0).toDecimal();
m_CrmProduct.put("Taxable",true);
if(!l_CrmProductAvailableTaxes.contains(m_XeroLineItem.get("TaxType")))
{
l_CrmProductAvailableTaxes.add(m_XeroLineItem.get("TaxType"));
}
m_CrmProduct.put("Tax",l_CrmProductAvailableTaxes);
//
m_CrmProduct.put("Xero_Updated",zoho.currenttime.toString("yyyy-MM-dd'T'HH:mm:ss",v_ZohoTimeZone));
//
// couldn't find it so let's create it
if(v_CrmProductID == "")
{
r_CreateCrmProduct = zoho.crm.createRecord("Products",m_CrmProduct);
if(b_Debug)
{
info "Creating/Updated CRM Product: " + r_CreateCrmProduct;
}
if(!isNull(r_CreateCrmProduct.get("id")))
{
v_CrmProductID = r_CreateCrmProduct.get("id");
}
else if(r_CreateCrmProduct.get("code").equalsIgnoreCase("DUPLICATE_DATA"))
{
v_CrmProductID = r_CreateCrmProduct.get("details").get("id");
if(b_Debug)
{
info "Duplicate CRM Product: Re-using " + v_CrmProductID;
}
}
}
//
// update the product (mainly for new applicable taxes)
r_UpdateCrmProduct = zoho.crm.updateRecord("Products",v_CrmProductID,m_CrmProduct);
if(b_Debug)
{
info "Update CRM Product";
info m_CrmProduct;
info r_UpdateCrmProduct;
}
//
// let's do the rest of the line item (note that we are going to upsert using CRM API v8)
m_CrmLineItem = Map();
m_CrmLineItem.put("Product_Name",v_CrmProductID);
m_CrmLineItem.put("Description","");
if(!v_CrmProductName.equalsIgnoreCase(m_XeroLineItem.get("Description")))
{
m_CrmLineItem.put("Description",m_XeroLineItem.get("Description"));
}
m_CrmLineItem.put("List_Price",m_XeroLineItem.get("UnitAmount"));
m_CrmLineItem.put("Quantity",m_XeroLineItem.get("Quantity"));
l_CrmLineItemTax = List();
m_CrmLineItemTax = Map();
v_ZohoLineItemTaxPercent = ifnull(m_ZohoTaxRates.get(m_XeroLineItem.get("TaxType")),0.0).toDecimal();
m_CrmLineItemTax.put("percentage",v_ZohoLineItemTaxPercent);
m_CrmLineItemTax.put("name",ifnull(m_XeroLineItem.get("TaxType"),"NO VAT"));
m_CrmLineItemTax.put("value",m_XeroLineItem.get("TaxAmount"));
l_CrmLineItemTax.add(m_CrmLineItemTax);
m_CrmLineItem.put("Line_Tax",l_CrmLineItemTax);
v_DiscountPercent = ifnull(m_XeroLineItem.get("DiscountRate"),0.0);
v_DiscountAmount = ifnull(m_XeroLineItem.get("DiscountAmount"),0.0);
if(v_DiscountPercent != 0)
{
// just qty vs unit excluding discount and tax
v_LineItemTotal = m_XeroLineItem.get("Quantity") * m_XeroLineItem.get("UnitAmount");
v_DiscountFactor = v_DiscountPercent / 100;
v_DiscountAmount = v_LineItemTotal * v_DiscountFactor;
}
m_CrmLineItem.put("Discount",v_DiscountAmount);
m_CrmLineItem.put("Tax",m_XeroLineItem.get("TaxAmount"));
l_CrmLineItems.add(m_CrmLineItem);
}
}
//
// if the CRM Quote already exists, we are going to upsert so we need to remove the current line items in the CRM Quote
l_SearchQuotes = zoho.crm.searchRecords("Quotes","Xero_Ref_ID:equals:" + v_XeroQuoteID);
for each m_QuoteResult in l_SearchQuotes
{
if(!isNull(m_QuoteResult.get("id")))
{
for each m_ExistingLineItem in m_QuoteResult.get("Product_Details")
{
m_MiniDeleteMe = Map();
m_MiniDeleteMe.put("id",m_ExistingLineItem.get("id"));
m_MiniDeleteMe.put("_delete",null);
l_CrmLineItems.add(m_MiniDeleteMe);
}
}
}
//
// add line items to the Quote
m_UpsertCrmQuote.put("Quoted_Items",l_CrmLineItems);
//
// -------------------------------- Quote Address --------------------------------
//
//
// let's add the billing address retrieved earlier to the Quote
m_UpsertCrmQuote.put("Billing_Street",l_AddressStreet.toString(", "));
m_UpsertCrmQuote.put("Billing_City",v_XeroAddressCity);
m_UpsertCrmQuote.put("Billing_Code",v_XeroAddressZip);
//
// -------------------------------- Quote Upsert --------------------------------
//
// initializing
v_UpsertedCrmID = "";
//
// let's upsert
info "Sending to Zoho: ";
info m_UpsertCrmQuote;
m_Data = Map();
m_Data.put("data",List({m_UpsertCrmQuote}));
m_Data.put("trigger",{"workflow","approval","blueprint"});
r_UpsertQuote = invokeUrl
[
url :"https://www.zohoapis.eu/crm/v8/Quotes/upsert"
type :POST
parameters:m_Data.toString()
connection:"ab_crm"
];
if(b_Debug)
{
info r_UpsertQuote;
}
l_ResponseData = ifnull(r_UpsertQuote.get("data"),List());
for each m_ResponseData in l_ResponseData
{
if(!isNull(m_ResponseData.get("code")))
{
v_Action = m_ResponseData.get("action");
if(!isNull(m_ResponseData.get("details")))
{
v_UpsertedCrmID = m_ResponseData.get("details").get("id");
}
}
}
if(v_Action == "insert")
{
v_Count_Created = v_Count_Created + 1;
//
// override the auto-naming to take the Quote Subject from Xero
if(v_UpsertedCrmID != "")
{
m_OverrideCrmQuote = Map();
m_OverrideCrmQuote.put("Subject",m_ThisQuote.get("QuoteNumber"));
r_OverrideCrmQuote = zoho.crm.updateRecord("Quotes",v_UpsertedCrmID,m_OverrideCrmQuote);
if(b_Debug)
{
info "Overriding CRM Quote subject: " + r_OverrideCrmQuote;
}
}
}
else if(v_Action == "update")
{
v_Count_Updated = v_Count_Updated + 1;
}
}
}
}
}
v_OutputMessage = "Created " + v_Count_Created + " and Updated " + v_Count_Updated + " from " + v_Count_FoundInXero;
}
return v_OutputMessage;
}

Caveat(s):

Tax rates have to have the same name in both CRM and Xero
The code above is for EU / UK clients only (change the TLD as applicable)
The code above accounts for clients not using the Items module and only the description field per line item.
pageSize seems to be completely ignored by Xero in this function

Category: Zoho :: Article: 914