Thought I already had an article on this and I know my article
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
the Code
copyraw
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-09 (Joel Lipman)
- Initial release
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;
//
// 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");
//
// get token details
v_ModuleName = "Integrations";
//
// enter the CRM record ID of your integrations record (Xero Integration API)
v_TokenCrmID = 878991000000960074;
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","Date 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
];
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");
info v_XeroInvoiceID;
//
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.subString(v_XeroInvoiceDate.indexOf("(") + 1,v_XeroInvoiceDate.indexOf("+")).toLong().toTime();
m_UpsertCrmInvoice.put("Invoice_Date",d_XeroInvoiceDate.toString("yyyy-MM-dd"));
v_XeroInvoiceDueDate = m_ThisInvoice.get("DueDate");
d_XeroInvoiceDueDate = v_XeroInvoiceDueDate.subString(v_XeroInvoiceDueDate.indexOf("(") + 1,v_XeroInvoiceDueDate.indexOf("+")).toLong().toTime();
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","Europe/London"));
m_UpsertCrmInvoice.put("Amount_Paid",m_ThisInvoice.get("AmountPaid"));
m_UpsertCrmInvoice.put("Amount_Credited",m_ThisInvoice.get("AmountCredited"));
v_XeroFullyPaidDate = m_ThisInvoice.get("FullyPaidOnDate");
d_XeroFullyPaidDate = v_XeroFullyPaidDate.subString(v_XeroFullyPaidDate.indexOf("(") + 1,v_XeroFullyPaidDate.indexOf("+")).toLong().toTime();
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","Europe/London").toTime();
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.subString(v_XeroTime.indexOf("(") + 1,v_XeroTime.indexOf("+")).toLong().toTime();
//
// 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","Europe/London"));
//
// 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);
info "Creating CRM Account: " + r_CreateAccount;
if(!isNull(r_CreateAccount.get("id")))
{
v_CrmAccountID = r_CreateAccount.get("id");
}
}
//
// create a contact
l_SearchContacts = zoho.crm.searchRecords("Contacts","Email:equals:" + m_XeroContact.get("EmailAddress"),1,2,{"approved":"both","converted":"both"});
info "Searching Contacts: " + l_SearchContacts;
for each m_SearchContact in l_SearchContacts
{
if(!isNull(m_SearchContact.get("id")))
{
b_CreateContact = false;
v_CrmContactID = m_SearchContact.get("id");
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","Europe/London"));
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);
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);
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 = "";
//
// 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"));
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"});
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");
}
}
//
// couldn't find it so let's create it
if(v_CrmProductID == "")
{
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"));
m_CrmProduct.put("Xero_Updated",zoho.currenttime.toString("yyyy-MM-dd'T'HH:mm:ss","Europe/London"));
r_CreateCrmProduct = zoho.crm.createRecord("Products",m_CrmProduct);
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");
}
}
//
// 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;
}
m_CrmLineItem.put("Discount",v_DiscountAmount);
m_CrmLineItem.put("Tax",m_XeroLineItem.get("TaxAmount"));
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
info m_UpsertCrmInvoice;
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"
];
info "Upserting Invoice: " + m_ThisInvoice.get("InvoiceNumber");
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");
}
}
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-09 (Joel Lipman)
- - Initial release
- 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;
- //
- // 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");
- //
- // get token details
- v_ModuleName = "Integrations";
- //
- // enter the CRM record ID of your integrations record (Xero Integration API)
- v_TokenCrmID = 878991000000960074;
- 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","Date 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
- ];
- 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");
- info v_XeroInvoiceID;
- //
- 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.subString(v_XeroInvoiceDate.indexOf("(") + 1,v_XeroInvoiceDate.indexOf("+")).toLong().toTime();
- m_UpsertCrmInvoice.put("Invoice_Date",d_XeroInvoiceDate.toString("yyyy-MM-dd"));
- v_XeroInvoiceDueDate = m_ThisInvoice.get("DueDate");
- d_XeroInvoiceDueDate = v_XeroInvoiceDueDate.subString(v_XeroInvoiceDueDate.indexOf("(") + 1,v_XeroInvoiceDueDate.indexOf("+")).toLong().toTime();
- 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","Europe/London"));
- m_UpsertCrmInvoice.put("Amount_Paid",m_ThisInvoice.get("AmountPaid"));
- m_UpsertCrmInvoice.put("Amount_Credited",m_ThisInvoice.get("AmountCredited"));
- v_XeroFullyPaidDate = m_ThisInvoice.get("FullyPaidOnDate");
- d_XeroFullyPaidDate = v_XeroFullyPaidDate.subString(v_XeroFullyPaidDate.indexOf("(") + 1,v_XeroFullyPaidDate.indexOf("+")).toLong().toTime();
- 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","Europe/London").toTime();
- 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.subString(v_XeroTime.indexOf("(") + 1,v_XeroTime.indexOf("+")).toLong().toTime();
- //
- // 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","Europe/London"));
- //
- // 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);
- info "Creating CRM Account: " + r_CreateAccount;
- if(!isNull(r_CreateAccount.get("id")))
- {
- v_CrmAccountID = r_CreateAccount.get("id");
- }
- }
- //
- // create a contact
- l_SearchContacts = zoho.crm.searchRecords("Contacts","Email:equals:" + m_XeroContact.get("EmailAddress"),1,2,{"approved":"both","converted":"both"});
- info "Searching Contacts: " + l_SearchContacts;
- for each m_SearchContact in l_SearchContacts
- {
- if(!isNull(m_SearchContact.get("id")))
- {
- b_CreateContact = false;
- v_CrmContactID = m_SearchContact.get("id");
- 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","Europe/London"));
- 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);
- 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);
- 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 = "";
- //
- // 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"));
- 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"});
- 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");
- }
- }
- //
- // couldn't find it so let's create it
- if(v_CrmProductID == "")
- {
- 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"));
- m_CrmProduct.put("Xero_Updated",zoho.currenttime.toString("yyyy-MM-dd'T'HH:mm:ss","Europe/London"));
- r_CreateCrmProduct = zoho.crm.createRecord("Products",m_CrmProduct);
- 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");
- }
- }
- //
- // 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;
- }
- m_CrmLineItem.put("Discount",v_DiscountAmount);
- m_CrmLineItem.put("Tax",m_XeroLineItem.get("TaxAmount"));
- 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
- info m_UpsertCrmInvoice;
- 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"
- ];
- info "Upserting Invoice: " + m_ThisInvoice.get("InvoiceNumber");
- 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");
- }
- }
- 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;
- }
Category: Zoho :: Article: 913
Add comment