BTerrell Group Blog

Joe Zhou

Recent Posts

Intacct Customization Showcase

Posted by Joe Zhou on Wed, Oct 09, 2019

Our development team specializes in Intacct customizations and integrations since March 2013. During this time, we've implemented many Intacct integration projects for various clients. When I look back on these projects, I can group them into the following categories.

 

1.       Standalone Web Applications

A good example is our CodePartners’ Mass Update Tool and GL Upload Tool. Both tools are standalone ASP.NET MVC websites and hosted in Windows Azure. If the customer requires tighter integration, we can embed such web applications in a Dashboard so that they look like a part of Intacct.

2.       Standalone Windows Applications

These applications deploy on a Windows server that the customer owns. They can run on demand or on a regular basis if set up using Windows Scheduler.

3.       Intacct Platform Services with no External Resources

These applications leverage Intacct Platform Services including custom objects, smart events, triggers, AJAX Toolkit, etc., exclusively.  In other words, every piece of the integration runs within Intacct and doesn’t require anything external to run. Read more about  Simplifying Billable Expenses with Sage Intacct.

4.       Intacct Platform Services with an External Restful API

BTerrell's Workforce Go! is a perfect example for this category. It leverages all the power Intacct Platform Services provides and takes advantage of the benefit of Restful APIs for the pieces that are better handled outside of Intacct.

5.      Combining a Windows application, an external Restful API and Intacct Platform Services

Many folks didn’t think Intacct, a cloud based ERP system, could work with on-premise legacy systems.  With the help of a Restful API that bridges Intacct and the on-premise system, it is possible. Check out the blog post I wrote a couple of months ago on Intacct customizations for details about this type of integration.

    

    

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 Fiddler Web Debugger to Capture Intacct API Calls

Posted by Joe Zhou on Wed, Feb 11, 2015

Building Intacct customization programs is not an easy task. When we develop a custom Intacct Platform application for a client, we must construct the correct API request to get the desired response from Intacct. A great tool I use on a regular basis, called Fiddler, does exactly that to help us record all the HTTP and HTTPS traffic that passes between the computer and the Internet so that the user sees what is being sent to Intacct and what is being returned from Intacct.

These steps describe how we utilize Fiddler Web Debugger to help capture Intacct XML calls for the debugging purposes.

  1. Go to http://www.telerik.com/download/fiddler and download and install Fiddler 4 if you are a Windows 8.1 user like me.
  2. Run Fiddler 4.
  3. Create a filter so that Fiddler only captures traffic to Intacct. Without the filter, all web traffic initiated from the computer will be captured and it will be hard to find the ones that we want to monitor from the process list.
3.1   Go to the Filters tab and enable Use Filters.
3.2   Check Show only Internet Hosts and Show only the following Hosts.
3.3   Type in www.intacct.com as the only host to show.
3.4   Click the Actions button on the right and Save the Filterset.
3.5   Run the Filterset saved above.
2-11image1
  1. Enable HTTPS traffic decryption:
4.1   Click Tools -> Fiddler Options -> HTTPS.
4.2   Click the Decrypt HTTPS Traffic box.
2-11image2
  1. Now we completed all the necessary Fiddler configurations. We are ready to execute the application we want to debug.
  2. In the left window where all processes are listed, there are a few requests to a URL that says /ia/xml/xmlgw.phtml. These requests are basically XML API calls to Intacct when our application runs.
2-11image3
7. Double click on a request. Go to the window on the right. Click the Inspectors tab and use Textview to view the request (upper box) and the response (lower box) in XML.
2-11image4
8. In the previous screenshot, the request looks OK in terms of syntax. Intacct however returns an error that says “Incorrect password or invalid IP address” in the response that indicates what the exact root cause was and the action needed to be taken.  

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