BTerrell Group Blog

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

BTerrell saves client 40 hours each month by tailoring cost allocation in Intacct

Posted by Jennifer Chandler on Wed, Feb 18, 2015

At BTerrell, we increase the enterprise value of Intacct client companies by automating key business processes. In a recent opportunity, we reduced the amount of time to calculate a complicated cost allocation process by 40 hours per month. Even at nominal loaded labor rates of $24 per hour, this amounts to a savings of nearly $12,000 per year!

2-20imagea

How did we do it? Our application includes five components, which allow the end user to configure the component, calculate the allocation, and view historical results. Specifically, those five components include:

Driver - bases the allocation driver on account balance or percentage

Allocation steps - creates user defined steps and processing order

Allocation profile - defines which accounts to include in each step of the process as well as the G/L segments and accounts to use in the journal entry

Calculate allocation - calculates the allocation and creates the journal entry

View history calculation - provides insight into drivers, calculations, and allocation results for historical transactions

 

Thank you to Arxis Technology for allowing us to partner with them in the development and delivery of this valuable Intacct automation. Don't hesitate to ask us how we can help you increase your company's enterprise value! 

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

How to Customize Intacct Reports and Forms

Posted by Kevin Yu on Wed, Nov 20, 2013

Tired of dealing with complicated report writing software or needing assistance from your IT department when you want to create a simple report? Wait no longer! Intacct makes creating customized documents and the ability for you to write custom reports very easy.

Intacct provides editable templates, in MS Word format, for customizing documents you send to customers, such as invoices, sales orders, and quotes. These templates use mail merge fields that refer to Intacct fields. Intacct also provides a Custom Document Toolbar add-in for MS Word making it very simple to find and insert the correct merge field into your document. Since all of the editing is completed in MS Word, the look and feel of forms, such as invoices, sales orders, and quotes are limited by only your imagination, MS Word skills, and the formatting capabilities of MS Word.

 

Intacct Custom Document Toolbar

With Intacct’s custom report wizard and financial report writer, you can easily create reports that capture and track the metrics most important to you.  For example, the report writer below takes you step-by-step through the creation process, allowing you real-time visibility into your performance, ultimately helping you manage your organization’s growth. You can view the performance any way you prefer, as well as achieve higher quality information without the extra costs, reporting tools, and IT effort.

Intacct Custom Report Wizard & Document Report Writer

 

Interested in learning more? Please contact me for a free 30 minute consultation!

Tags: Intacct, Intacct Customization