ZohoCRM & Xero: Function to pull most recent invoices

: Category: Zoho; 09 October 2025; Hits: 5836

What?
Thought I already had an article on this and I know my article Zoho Deluge - Connect to Xero API covered a quick query to pull some invoices but this one documents a pull and mapping into CRM invoices.

Why?
This took me a whole afternoon so I wanted a template function I could use in future when I get this request again. The additional benefit of having this template is that it includes creating contacts, accounts, and products on-the-fly as well as recording payments and checks as to which record is more up to date between ZohoCRM and Xero.

How?
The access token is generated by a function documented in my previously mentioned article Zoho Deluge - Connect to Xero API so here's the pull of the first page of invoices by most recent first (invoice date).
There is also a table that returns the name of tax rates and then the percent that these equate to which I've copied below this invoices pull function.

the Code

copy raw

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

	Date Created:   2025-10-08 (Joel Lipman)
					- Initial release
	Date Modified:	2025-10-13 ( Joel Lipman)
					- Parsing Xero Dates and Times to include timezone of Xero instance for accurate time comparisons
					- Map Xero tax rates to Zoho tax rates (same names in Zoho)
					- output to console if debug mode enabled

	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 = 20;
	b_Debug = false;
	//
	// Xero Invoice Statuses vs your CRM Invoice Statuses
	m_TranslateStatuses = Map();
	m_TranslateStatuses.put("DRAFT","Draft");
	m_TranslateStatuses.put("SUBMITTED","Pending Approval");
	m_TranslateStatuses.put("AUTHORISED","Sent to Customer");
	m_TranslateStatuses.put("PAID","Paid in Full");
	m_TranslateStatuses.put("DELETED","Cancelled");
	m_TranslateStatuses.put("VOIDED","Cancelled");
	//
	// 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;
	}
	//
	// server timezones
	v_XeroTimeZone = "Europe/London";
	v_ZohoTimeZone = "Europe/London";
	//
	// 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 invoices (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 invoices
			v_FilterReceivables = "?where=" + zoho.encryption.urlEncode("Type=\"ACCREC\"");
			r_AllXeroInvoices = invokeurl
			[
				url :v_DataEndpoint + "/Invoices" + v_FilterReceivables
				type :GET
				parameters:m_Params
				headers:m_Header
			];
			if(b_Debug)
			{
				info r_AllXeroInvoices;
			}
			if(!isnull(r_AllXeroInvoices.get("Invoices")))
			{
				for each  m_ThisInvoice in r_AllXeroInvoices.get("Invoices")
				{
					if(!isnull(m_ThisInvoice.get("InvoiceID")))
					{
						//
						// counter
						v_Count_FoundInXero = v_Count_FoundInXero + 1;
						//
						// Xero Invoice identifier
						v_XeroInvoiceID = m_ThisInvoice.get("InvoiceID");
						//
						m_UpsertCrmInvoice = Map();
						m_UpsertCrmInvoice.put("Subject",m_ThisInvoice.get("InvoiceNumber"));
						//
						// some standard CRM invoice fields we can populate
						v_CrmInvoiceStatus = m_TranslateStatuses.get(m_ThisInvoice.get("Status"));
						if(m_ThisInvoice.get("Status") == "PAID")
						{
							v_InvoiceTotal = m_ThisInvoice.get("Total");
							v_PaidTotal = 0.0;
							// we have a partially paid status in crm so let's check those payments
							for each  m_XeroPayment in m_ThisInvoice.get("Payments")
							{
								if(!isNull(m_XeroPayment.get("PaymentID")))
								{
									v_PaidTotal = v_PaidTotal + m_XeroPayment.get("Amount");
								}
							}
							v_CrmInvoiceStatus = if(v_PaidTotal == v_InvoiceTotal,"Paid in Full","Partially Paid");
						}
						m_UpsertCrmInvoice.put("Status",v_CrmInvoiceStatus);
						v_XeroInvoiceDate = m_ThisInvoice.get("Date");
						d_XeroInvoiceDate = v_XeroInvoiceDate.replaceAll("/Date\((\d+)([+-]\d{4})?\)/","$1",false).toLong().toTime("yyyy-MM-dd HH:mm:ss",v_XeroTimeZone);
						m_UpsertCrmInvoice.put("Invoice_Date",d_XeroInvoiceDate.toString("yyyy-MM-dd"));
						v_XeroInvoiceDueDate = m_ThisInvoice.get("DueDate");
						d_XeroInvoiceDueDate = v_XeroInvoiceDueDate.replaceAll("/Date\((\d+)([+-]\d{4})?\)/","$1",false).toLong().toTime("yyyy-MM-dd HH:mm:ss",v_XeroTimeZone);
						m_UpsertCrmInvoice.put("Due_Date",d_XeroInvoiceDueDate.toString("yyyy-MM-dd"));
						m_UpsertCrmInvoice.put("Currency",m_ThisInvoice.get("CurrencyCode"));
						//
						// some custom fields I created in CRM to store the data
						m_UpsertCrmInvoice.put("Xero_Ref_ID",m_ThisInvoice.get("InvoiceID"));
						m_UpsertCrmInvoice.put("Xero_Updated",zoho.currenttime.toString("yyyy-MM-dd'T'HH:mm:ss",v_ZohoTimeZone));
						m_UpsertCrmInvoice.put("Amount_Paid",m_ThisInvoice.get("AmountPaid"));
						m_UpsertCrmInvoice.put("Amount_Credited",m_ThisInvoice.get("AmountCredited"));
						if(!isNull(m_ThisInvoice.get("FullyPaidOnDate")))
						{
							v_XeroFullyPaidDate = m_ThisInvoice.get("FullyPaidOnDate");
							d_XeroFullyPaidDate = v_XeroFullyPaidDate.replaceAll("/Date\((\d+)([+-]\d{4})?\)/","$1",false).toLong().toTime("yyyy-MM-dd HH:mm:ss",v_XeroTimeZone);
							m_UpsertCrmInvoice.put("Date_Fully_Paid",d_XeroFullyPaidDate.toString("yyyy-MM-dd"));
						}
						m_UpsertCrmInvoice.put("Reference",m_ThisInvoice.get("Reference"));
						//
						// -------------------------------- Invoice 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_ThisInvoice.get("Contact").get("ContactID");
						v_XeroContactName = m_ThisInvoice.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",v_ZohoTimeZone).toTime();
								if(b_Debug)
								{
									info "Found CRM Account: " + v_CrmAccountID;
								}
							}
						}
						//
						// get account/contact details from Xero (invoice 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_ThisInvoice.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);
								if(b_Debug)
								{
									info "Searching Contacts (" + v_SearchContactsCriteria + "): " + l_SearchContacts;
								}
								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 invoice
						m_UpsertCrmInvoice.put("Account_Name",v_CrmAccountID);
						m_UpsertCrmInvoice.put("Contact_Name",v_CrmContactID);
						//
						// -------------------------------- Invoice Line Items --------------------------------
						//
						// initializing
						l_CrmLineItems = List();
						//
						// loop through line items on the Xero invoice
						for each  m_XeroLineItem in m_ThisInvoice.get("LineItems")
						{
							//
							// initialize
							v_CrmProductID = "";
							l_CrmProductAvailableTaxes = List();
							l_CrmProductAvailableTaxes.add("NONE");
							//
							// 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");
							if(!isBlank(v_CrmProductName))
							{
								v_CrmProductCode = ifnull(m_XeroLineItem.get("ItemCode"),"-Unknown-");
								v_CrmProductCodeSafe = zoho.encryption.urlEncode(v_CrmProductCode);
								v_CrmProductName = if(v_CrmProductName.length() >= 200,v_CrmProductName.subString(0,199),v_CrmProductName);
								v_CrmProductNameSafe = zoho.encryption.urlEncode(v_CrmProductName);
								v_SearchCriteria = "((Product_Code:equals:" + v_CrmProductCodeSafe + ")or(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;
								}
								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());
										if(b_Debug)
										{
											info "Available Taxes: " + l_CrmProductAvailableTaxes;
										}
									}
								}
								//
								// couldn't find it so let's create it
								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);
								m_CrmProduct.put("Description",m_XeroLineItem.get("Description"));
								m_CrmProduct.put("Unit_Price",m_XeroLineItem.get("UnitAmount"));
								//
								// map over tax (even if it's zero)
								v_XeroLineItemTaxAmount = ifnull(m_XeroLineItem.get("TaxAmount"),0).toDecimal();
								m_CrmProduct.put("Taxable",true);
								v_CrmTaxRateString = m_XeroLineItem.get("TaxType") + " - " + ifnull(m_ZohoTaxRates.get(m_XeroLineItem.get("TaxType")),"0.0") + " %";
								if(!l_CrmProductAvailableTaxes.contains(v_CrmTaxRateString))
								{
									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));
								//
								if(v_CrmProductID == "")
								{
									r_CreateCrmProduct = zoho.crm.createRecord("Products",m_CrmProduct);
									if(b_Debug)
									{
										info "Creating 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;
										}
									}
								}
								else
								{
									//
									// 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",m_XeroLineItem.get("Description"));
								m_CrmLineItem.put("List_Price",m_XeroLineItem.get("UnitAmount"));
								m_CrmLineItem.put("Quantity",m_XeroLineItem.get("Quantity"));
								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;
								}
								// tax even if it's zero
								l_CrmLineItemTax = List();
								m_CrmLineItemTax = Map();
								v_ZohoLineItemTaxPercent = ifnull(m_ZohoTaxRates.get(m_XeroLineItem.get("TaxType")),0.0).toDecimal();
								v_ZohoLineItemTaxPercent = v_ZohoLineItemTaxPercent * 1;
								m_CrmLineItemTax.put("percentage",v_ZohoLineItemTaxPercent);
								m_CrmLineItemTax.put("name",ifnull(m_XeroLineItem.get("TaxType"),"NONE"));
								m_CrmLineItemTax.put("value",m_XeroLineItem.get("TaxAmount"));
								l_CrmLineItemTax.add(m_CrmLineItemTax);
								m_CrmLineItem.put("Line_Tax",l_CrmLineItemTax);
								//
								m_CrmLineItem.put("Discount",v_DiscountAmount);
								l_CrmLineItems.add(m_CrmLineItem);
							}
						}
						//
						// if the CRM invoice already exists, we are going to upsert so we need to remove the current line items in the CRM invoice
						l_SearchInvoices = zoho.crm.searchRecords("Invoices","Xero_Ref_ID:equals:" + v_XeroInvoiceID);
						for each  m_InvoiceResult in l_SearchInvoices
						{
							if(!isNull(m_InvoiceResult.get("id")))
							{
								for each  m_ExistingLineItem in m_InvoiceResult.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 invoice
						m_UpsertCrmInvoice.put("Invoiced_Items",l_CrmLineItems);
						//
						// let's add the billing address retrieved earlier to the invoice
						m_UpsertCrmInvoice.put("Billing_Street",l_AddressStreet.toString(", "));
						m_UpsertCrmInvoice.put("Billing_City",v_XeroAddressCity);
						m_UpsertCrmInvoice.put("Billing_Code",v_XeroAddressZip);
						//
						// let's upsert
						m_Data = Map();
						m_Data.put("data",List({m_UpsertCrmInvoice}));
						m_Data.put("trigger",{"workflow","approval","blueprint"});
						r_UpsertInvoice = invokeurl
						[
							url :"https://www.zohoapis.eu/crm/v8/Invoices/upsert"
							type :POST
							parameters:m_Data.toString()
							connection:"ab_crm"
						];
						if(b_Debug)
						{
							info "Upserting Invoice: " + m_ThisInvoice.get("InvoiceNumber");
							info m_UpsertCrmInvoice;
							info r_UpsertInvoice;
						}
						l_ResponseData = ifnull(r_UpsertInvoice.get("data"),List());
						for each  m_ResponseData in l_ResponseData
						{
							if(!isNull(m_ResponseData.get("code")))
							{
								v_Action = m_ResponseData.get("action");
							}
							info m_ResponseData.get("code");
							//
							// possible errors, output anyway
							b_Error = if(m_ResponseData.get("code").equalsIgnoreCase("SUCCESS"),false,true);
							if(b_Error)
							{
								info m_ThisInvoice.get("InvoiceNumber") + ": FAILED: " + m_ResponseData;
								info m_UpsertCrmInvoice;
							}
						}
						if(v_Action == "insert")
						{
							v_Count_Created = v_Count_Created + 1;
						}
						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_GetInvoices()
{
/* *******************************************************************************
Function: string standalone.fn_Xero_GetInvoices()
Label: Fn - Xero - Get Invoices
Trigger: Standalone / On-Demand / Callable
Purpose: Function to get the first page of most recent invoices from Xero and pull them into ZohoCRM
Inputs: -
Outputs: -
Date Created: 2025-10-08 (Joel Lipman)
- Initial release
Date Modified: 2025-10-13 ( Joel Lipman)
- Parsing Xero Dates and Times to include timezone of Xero instance for accurate time comparisons
- Map Xero tax rates to Zoho tax rates (same names in Zoho)
- output to console if debug mode enabled
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 = 20;
b_Debug = false;
//
// Xero Invoice Statuses vs your CRM Invoice Statuses
m_TranslateStatuses = Map();
m_TranslateStatuses.put("DRAFT","Draft");
m_TranslateStatuses.put("SUBMITTED","Pending Approval");
m_TranslateStatuses.put("AUTHORISED","Sent to Customer");
m_TranslateStatuses.put("PAID","Paid in Full");
m_TranslateStatuses.put("DELETED","Cancelled");
m_TranslateStatuses.put("VOIDED","Cancelled");
//
// 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;
}
//
// server timezones
v_XeroTimeZone = "Europe/London";
v_ZohoTimeZone = "Europe/London";
//
// 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 invoices (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 invoices
v_FilterReceivables = "?where=" + zoho.encryption.urlEncode("Type=\"ACCREC\"");
r_AllXeroInvoices = invokeUrl
[
url :v_DataEndpoint + "/Invoices" + v_FilterReceivables
type :GET
parameters:m_Params
headers:m_Header
];
if(b_Debug)
{
info r_AllXeroInvoices;
}
if(!isnull(r_AllXeroInvoices.get("Invoices")))
{
for each m_ThisInvoice in r_AllXeroInvoices.get("Invoices")
{
if(!isnull(m_ThisInvoice.get("InvoiceID")))
{
//
// counter
v_Count_FoundInXero = v_Count_FoundInXero + 1;
//
// Xero Invoice identifier
v_XeroInvoiceID = m_ThisInvoice.get("InvoiceID");
//
m_UpsertCrmInvoice = Map();
m_UpsertCrmInvoice.put("Subject",m_ThisInvoice.get("InvoiceNumber"));
//
// some standard CRM invoice fields we can populate
v_CrmInvoiceStatus = m_TranslateStatuses.get(m_ThisInvoice.get("Status"));
if(m_ThisInvoice.get("Status") == "PAID")
{
v_InvoiceTotal = m_ThisInvoice.get("Total");
v_PaidTotal = 0.0;
// we have a partially paid status in crm so let's check those payments
for each m_XeroPayment in m_ThisInvoice.get("Payments")
{
if(!isNull(m_XeroPayment.get("PaymentID")))
{
v_PaidTotal = v_PaidTotal + m_XeroPayment.get("Amount");
}
}
v_CrmInvoiceStatus = if(v_PaidTotal == v_InvoiceTotal,"Paid in Full","Partially Paid");
}
m_UpsertCrmInvoice.put("Status",v_CrmInvoiceStatus);
v_XeroInvoiceDate = m_ThisInvoice.get("Date");
d_XeroInvoiceDate = v_XeroInvoiceDate.replaceAll("/Date$(\d+)([+-]\d{4})?$/","$1",false).toLong().toTime("yyyy-MM-dd HH:mm:ss",v_XeroTimeZone);
m_UpsertCrmInvoice.put("Invoice_Date",d_XeroInvoiceDate.toString("yyyy-MM-dd"));
v_XeroInvoiceDueDate = m_ThisInvoice.get("DueDate");
d_XeroInvoiceDueDate = v_XeroInvoiceDueDate.replaceAll("/Date$(\d+)([+-]\d{4})?$/","$1",false).toLong().toTime("yyyy-MM-dd HH:mm:ss",v_XeroTimeZone);
m_UpsertCrmInvoice.put("Due_Date",d_XeroInvoiceDueDate.toString("yyyy-MM-dd"));
m_UpsertCrmInvoice.put("Currency",m_ThisInvoice.get("CurrencyCode"));
//
// some custom fields I created in CRM to store the data
m_UpsertCrmInvoice.put("Xero_Ref_ID",m_ThisInvoice.get("InvoiceID"));
m_UpsertCrmInvoice.put("Xero_Updated",zoho.currenttime.toString("yyyy-MM-dd'T'HH:mm:ss",v_ZohoTimeZone));
m_UpsertCrmInvoice.put("Amount_Paid",m_ThisInvoice.get("AmountPaid"));
m_UpsertCrmInvoice.put("Amount_Credited",m_ThisInvoice.get("AmountCredited"));
if(!isNull(m_ThisInvoice.get("FullyPaidOnDate")))
{
v_XeroFullyPaidDate = m_ThisInvoice.get("FullyPaidOnDate");
d_XeroFullyPaidDate = v_XeroFullyPaidDate.replaceAll("/Date$(\d+)([+-]\d{4})?$/","$1",false).toLong().toTime("yyyy-MM-dd HH:mm:ss",v_XeroTimeZone);
m_UpsertCrmInvoice.put("Date_Fully_Paid",d_XeroFullyPaidDate.toString("yyyy-MM-dd"));
}
m_UpsertCrmInvoice.put("Reference",m_ThisInvoice.get("Reference"));
//
// -------------------------------- Invoice 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_ThisInvoice.get("Contact").get("ContactID");
v_XeroContactName = m_ThisInvoice.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",v_ZohoTimeZone).toTime();
if(b_Debug)
{
info "Found CRM Account: " + v_CrmAccountID;
}
}
}
//
// get account/contact details from Xero (invoice 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_ThisInvoice.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);
if(b_Debug)
{
info "Searching Contacts (" + v_SearchContactsCriteria + "): " + l_SearchContacts;
}
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 invoice
m_UpsertCrmInvoice.put("Account_Name",v_CrmAccountID);
m_UpsertCrmInvoice.put("Contact_Name",v_CrmContactID);
//
// -------------------------------- Invoice Line Items --------------------------------
//
// initializing
l_CrmLineItems = List();
//
// loop through line items on the Xero invoice
for each m_XeroLineItem in m_ThisInvoice.get("LineItems")
{
//
// initialize
v_CrmProductID = "";
l_CrmProductAvailableTaxes = List();
l_CrmProductAvailableTaxes.add("NONE");
//
// 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");
if(!isBlank(v_CrmProductName))
{
v_CrmProductCode = ifnull(m_XeroLineItem.get("ItemCode"),"-Unknown-");
v_CrmProductCodeSafe = zoho.encryption.urlEncode(v_CrmProductCode);
v_CrmProductName = if(v_CrmProductName.length() >= 200,v_CrmProductName.subString(0,199),v_CrmProductName);
v_CrmProductNameSafe = zoho.encryption.urlEncode(v_CrmProductName);
v_SearchCriteria = "((Product_Code:equals:" + v_CrmProductCodeSafe + ")or(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;
}
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());
if(b_Debug)
{
info "Available Taxes: " + l_CrmProductAvailableTaxes;
}
}
}
//
// couldn't find it so let's create it
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);
m_CrmProduct.put("Description",m_XeroLineItem.get("Description"));
m_CrmProduct.put("Unit_Price",m_XeroLineItem.get("UnitAmount"));
//
// map over tax (even if it's zero)
v_XeroLineItemTaxAmount = ifnull(m_XeroLineItem.get("TaxAmount"),0).toDecimal();
m_CrmProduct.put("Taxable",true);
v_CrmTaxRateString = m_XeroLineItem.get("TaxType") + " - " + ifnull(m_ZohoTaxRates.get(m_XeroLineItem.get("TaxType")),"0.0") + " %";
if(!l_CrmProductAvailableTaxes.contains(v_CrmTaxRateString))
{
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));
//
if(v_CrmProductID == "")
{
r_CreateCrmProduct = zoho.crm.createRecord("Products",m_CrmProduct);
if(b_Debug)
{
info "Creating 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;
}
}
}
else
{
//
// 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",m_XeroLineItem.get("Description"));
m_CrmLineItem.put("List_Price",m_XeroLineItem.get("UnitAmount"));
m_CrmLineItem.put("Quantity",m_XeroLineItem.get("Quantity"));
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;
}
// tax even if it's zero
l_CrmLineItemTax = List();
m_CrmLineItemTax = Map();
v_ZohoLineItemTaxPercent = ifnull(m_ZohoTaxRates.get(m_XeroLineItem.get("TaxType")),0.0).toDecimal();
v_ZohoLineItemTaxPercent = v_ZohoLineItemTaxPercent * 1;
m_CrmLineItemTax.put("percentage",v_ZohoLineItemTaxPercent);
m_CrmLineItemTax.put("name",ifnull(m_XeroLineItem.get("TaxType"),"NONE"));
m_CrmLineItemTax.put("value",m_XeroLineItem.get("TaxAmount"));
l_CrmLineItemTax.add(m_CrmLineItemTax);
m_CrmLineItem.put("Line_Tax",l_CrmLineItemTax);
//
m_CrmLineItem.put("Discount",v_DiscountAmount);
l_CrmLineItems.add(m_CrmLineItem);
}
}
//
// if the CRM invoice already exists, we are going to upsert so we need to remove the current line items in the CRM invoice
l_SearchInvoices = zoho.crm.searchRecords("Invoices","Xero_Ref_ID:equals:" + v_XeroInvoiceID);
for each m_InvoiceResult in l_SearchInvoices
{
if(!isNull(m_InvoiceResult.get("id")))
{
for each m_ExistingLineItem in m_InvoiceResult.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 invoice
m_UpsertCrmInvoice.put("Invoiced_Items",l_CrmLineItems);
//
// let's add the billing address retrieved earlier to the invoice
m_UpsertCrmInvoice.put("Billing_Street",l_AddressStreet.toString(", "));
m_UpsertCrmInvoice.put("Billing_City",v_XeroAddressCity);
m_UpsertCrmInvoice.put("Billing_Code",v_XeroAddressZip);
//
// let's upsert
m_Data = Map();
m_Data.put("data",List({m_UpsertCrmInvoice}));
m_Data.put("trigger",{"workflow","approval","blueprint"});
r_UpsertInvoice = invokeUrl
[
url :"https://www.zohoapis.eu/crm/v8/Invoices/upsert"
type :POST
parameters:m_Data.toString()
connection:"ab_crm"
];
if(b_Debug)
{
info "Upserting Invoice: " + m_ThisInvoice.get("InvoiceNumber");
info m_UpsertCrmInvoice;
info r_UpsertInvoice;
}
l_ResponseData = ifnull(r_UpsertInvoice.get("data"),List());
for each m_ResponseData in l_ResponseData
{
if(!isNull(m_ResponseData.get("code")))
{
v_Action = m_ResponseData.get("action");
}
info m_ResponseData.get("code");
//
// possible errors, output anyway
b_Error = if(m_ResponseData.get("code").equalsIgnoreCase("SUCCESS"),false,true);
if(b_Error)
{
info m_ThisInvoice.get("InvoiceNumber") + ": FAILED: " + m_ResponseData;
info m_UpsertCrmInvoice;
}
}
if(v_Action == "insert")
{
v_Count_Created = v_Count_Created + 1;
}
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;
}

The Tax Rates function

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 (Ascent Business - Joel Lipman)
					- Initial release
	Date Modified:	???
					- ???

	More Information:
					Ensure that all possible tax rates in Xero match those in Zoho (eg. 20.000, 17.500, 15.000, 10.000, 5.000, ...)

	******************************************************************************* */
	// 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 (Ascent Business - Joel Lipman)
- Initial release
Date Modified: ???
- ???
More Information:
Ensure that all possible tax rates in Xero match those in Zoho (eg. 20.000, 17.500, 15.000, 10.000, 5.000, ...)
******************************************************************************* */
// 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;
}