BTerrell Group Blog

Joe Zhou

Recent Posts

Revel and Intacct Integration

Posted by Joe Zhou on Mon, Apr 20, 2015

As a developer who builds high-quality integrations and customizations on the cloud-based Intacct platform, I constantly get requests to build an integration that pulls data from a system into Intacct. Often times the system I need to interface with is a legacy system.  A lot of these legacy systems share the same characteristics, such as on-premise, closed-loop, stand-alone, etc. When integrating with such systems, an intermediary file and a daily scheduler are often required to transfer the data from this system to Intacct.

 

Recently, I worked with the iPad based-Point of Sale (POS) system Revel. Our business partner’s client is in the restaurant business and has multiple Revel iPad based POS devices deployed in their different stores. The business partner booked the sales manually on a daily basis. Because of our expertise on the Intacct platform, they contracted us to automate the process for thier client.

Unlike legacy systems, Revel exposes an open and modern RESTful API that allows third-party developers to easily integrate with their API. So, I was able to  understand their API quickly and turn it around a working solution within a week. The integration is based on ASP.NET WebAPI and hosted in Microsoft Azure. Here's how it pulls the daily sales into Intacct:

  1. BTerrell API sends a request to Revel REST API via HTTPS for all stores to retrieve the sales reports daily.
  2. Revel REST API returns the sales reports in JSON format.
  3. BTerrell API parses the JSON data and then converts it into a XML file that conforms to the Intacct XML API format.
  4. BTerrell API invokes an Intacct API call by submitting the XML file prepared from the above step.
  5. If the above steps succeed:
    1. Daily sales are booked to a private entity as a single GL batch in the Sales Journal in Intacct.
    2. A success message is logged.
  6. If the above steps fail, a message indicating the nature of the failure and appropriate next steps is written to a failure log.

The diagram illustrates the workflow.

 4-20image1

Tags: erp integration, automation, Intacct Customization

Tips on Writing Queries for the Readbyquery() Method

Posted by Joe Zhou on Mon, Mar 09, 2015

The Readbyquery() method is one of the most used Intacct API calls, because it allows you to retrieve records from a certain object based on a query. The query syntax used in this method is actually a subset of standard SQL where clause. Therefore, it can be confusing as to which standard SQL syntax is applicable to Intacct and which is not. I listed some tips I gathered from my trial and errors.  I hope you find them helpful.

  1. It is a good habit to enclose your query in parenthesis.
  2. Supported SQL operators include, but are not limited to:

<, >, <=, >=, =, like, not like, in, not in, and, or, is null, is not null, not

  1. Because Intacct API calls are XML based, the operators ‘<’ and ‘<=’ are illegal as a result. Both operators should be replaced with ‘&lt;’ and ‘&lt;=’ instead.
  2. Use the escaped form on these characters.
ampersand & -> &amp;
apostrophe ' -> &apos;
quotation mark " -> &quot;
  1. Use IS NOT NULL if you want to exclude null values.
  2. Use IS NULL if you only want to return null values.
  3. If you want to express NOT EQUAL TO, use these expressions.
  • (RECORDID NOT IN (‘92’))
  • (NOT RECORDID = ‘92’)
  1. Use IN operator to query within an array. For example, (RECORDNO IN ('11','22','33','44','55','66','77','88','99'))
  2. Use ‘&lt;=’ and ‘&gt;=’ on a date range.

For example, (WHENDUE &lt;= '02/28/2015' AND WHENDUE &gt;= '02/01/2015')

10. Use the LIKE operator and a wildcard character such as % to substitute for zero or more characters to perform START WITH, END WITH or CONTAIN type of queries.

For example, (VENDORNAME LIKE 'Code%')

 

I hope you find these useful, please let me know if you have other tips for Readybyquery().

Tags: Intacct Customization

How to use jQuery to change labels on a standard Intacct page

Posted by Joe Zhou on Fri, Jan 16, 2015

Intacct Platform Services provides the ability to add client side jQuery code to both standard object pages and custom Platform pages. This allows you to extend Intacct’s world-class ERP functions by leveraging the popular jQuery library to its full extent. Here is a very simple explanation of how easy it is to overwrite a label from the Vendor page with a few lines of code.

Let’s say I want to store the second phone number for my vendors in Intacct and I want to use the Pager field to store it since few people carry a pager. 

 1-16image1

 

If you are a fan of Firefox like me, you probably already know the shortcut Ctrl+Shift+C to open Inspector. If you are not familiar with these shortcuts, you can click the hamburger icon on the toolbar and go to Developer->Inspector.

1-16image2

Inspect the label that says Pager and double-click on the label. In the underneath Inspector view, it will display the HTML DOM of the label. As you can see from the screenshot below, the identifier of the Pager label is “label_obj__DISPLAYCONTACT-_obj__PAGER” and the content of the label is enclosed in a child Span tag within a label tag. Make a note of the ID that will be referenced in the code for the next a few steps.

1-16image3

Click More Action button at the far top right corner. Then click Edit this page.

1-16image4

Drag and drop a new <Script Component> to the Page Editor.

 1-16image5

Edit the <Script Component> and add the following code snippet.

<script type='text/javascrpt'>
jq = jQuery.noConflict();
jq(document).ready(function () {
    jq("#label_obj__DISPLAYCONTACT-_obj__PAGER").find("span").html("Second phone");
});
</script>

As you see from the code snippet above, it basically uses a jQuery selector to locate the label in the DOM and replaces its content in one line of code.

Click Save and save the page editor again. Once the Vendor Information page gets reloaded, you will see that the Pager label now says Second phone.

1-16image6

Tags: Intacct Customization