Intacct: Platform Services Formula Fields


Did you know you can write formula fields in Intacct Platform Services?

I was struggling with getting a date to populate on a sales order being created from a platform object because the date created field on the Sales Order had to be in <month></month> <day> </day> <year> </year> format. I couldn’t figure out how to write it into the API code which was creating the Sales Order so I decided to go the custom formula field route!

The Solution:

Step 1: The user enters the Sales Order Date of: 03/27/2016

Step 2: The hidden custom fields (month, day, year) populate as follows:

Month: 03

Day: 27

Year: 2016

Step 3: These fields are then popped right into the API that creates the Sales Order and bam! Date field filled out!

Here is the formulas I used in creating the formula fields in Intacct (I couldn’t find it anywhere until I realized it’s just JavaScript). Just in case you don’t know these functions (or couldn’t remember from the last time you took a class in college- like me):

Year: “{!CLA_CM_patient_claim.CLA_CM_ar_inv_date#userformat!}”.substr(-4);

This formula takes the date “03/27/2016” and gives me just the last 4 digits.

Day: “{!CLA_CM_patient_claim.CLA_CM_ar_inv_date#userformat!}”.slice(3,5);

Month: “{!CLA_CM_patient_claim.CLA_CM_ar_inv_date#userformat!}”.slice(0,2);


Once I found these formulas it all came back to me! This is the basic premise of the slice formula:

  • You instruct the formula what you’ll be slicing (Intacct field) “03/27/2016” (that’s what’s in quotations)
  • Then the slice has two numbers in parenthesis:
    • The first number tells me where to start the slicing. This is ALWAYS the slot right before the first digit you want. So for Day, I want it to start in the 3rd slot “03/”. For the Month I want it to start before the first digit so I put 0.
    • The second number in the formula tells me what is the position of the last digit I want? For Day, this was the 5th digit “03/27” (7 is the 5th digit – technically including the “/”. For the month, it’s just the second digit “03” (3 is technically the second digit).

Easy right? The only thing to remember is the first number (starting position) is always the digit before the first digit you want to return (starting with 0) and the second digit is the position of the last digit to return.

I hope this helps someone! Have fun playing with some formula fields – and if anyone else if working on cool platform applications reach out to me! I love hearing the cool applications people come up with and we can geek out together on it.

P.S Anyone else agree it would be awesome if formula fields were not just on custom platform objects but throughout the system?! I could think of a lot of uses for them!




2015 was a great year! I learned more than I think I was expecting to. If you’ve been wondering where I disappeared to – unfortunately I have not gone on to brainwashing the world quite yet (2017 roadmap).  This year, I have some exciting news to share… In November I started writing for CLA: CliftonLarsonAllen’s Intacct blog. (Check it out here!).

Screen Shot 2016-03-26 at 3.10.18 PM.png

I’m pretty excited about the opportunity (a little nervous and self conscious that more people will be reading it but mostly excited). The goal of the Intacct blog was to help people who were like me (new to Intacct and not really having any resources to help them as they learn) make their jobs easier, learn how cool technology can be, and have a little fun along the way. I wanted to share what I think are really cool features of this software as I learned it. So the fact that I get to do that for a larger audience I think is a good thing. I really am excited to share my knowledge.

A lot has happened since my last post on excelwizards. More notably, I’ve had some fun diving into Platform Services and building custom applications. I think my favorite part of Intacct is the ability to customize the software to meet your business requirements.

Here are some of the platform applications I’ve built so far:

Collections Application: read it here!

Claims Management: (this one is really neat and was one of the more fun ones) read it here!

So it’s not that I’m leaving excelwizards, it’s more so I’m moving my knowledge base (and brain) to CLA’s website. You are welcome to follow me there (we have new improvements coming to the blog this summer too!). Added benefit: we have guest blog posts from other brilliant members of our CLA Intacct team too!?

Intacct: Finding Field ID’s for Smart Rules, Smart Events, API, and imports!

Today’s post will be a short one: A cool trick to help you in writing Smart Rules, Smart Events, API, and even imports!

You’re probably already familiar with the Intacct Catalog (found under Platform or Customization Services). It’s where you usually go to find your merge fields when writing Smart Rules, Smart Events, and anything in this format: {!PROJECT.NAME!}. (The object is the part to the left of the period, while the field id is anything to the right of the period. We’ll give you a tip today on finding the ID to the right of the period).


Now that you know where that is, I thought I’d share an easier tip (which changed my life) to help you find these field ID’s (since the catalog, while useful, is not too fun to play around in).

Whenever you are on a page in Intacct and you would like to know what the field’s ID is, you can right click and select “Inspect Element”.

Inspect Element

A small window will appear either in the bottom or right side of your screen and highlight the field for you. In that line you’ll see a part that says “id=_obj__PROJECTTYPE “. Anything to the right of the obj__ is your field id. Easy as that!

Inspect Element Open-1

Intacct Dimensions – Items

Screen Shot 2015-10-19 at 8.23.27 PM

A while back I promised a series on Intacct dimensions and different ways to use them. We covered the Customer Dimension in May, now let’s venture through the world of Items.

Items is one of the tougher dimensions that is often mis-used. So let’s approach the explanation of the Item dimension through a real-world example. Imagine we work at Apple in their accounting department and our team has decided to move to Intacct. We’re working on restructuring our chart of accounts and have just been asked what we would like our items to be. (You probably think – why do we need items? What exactly are items? Especially if you’ve never thought of your company as selling “items”). Let’s say your revenue accounts look something like this:

Screen Shot 2015-10-19 at 7.38.51 PM

You’ll notice right off the bat – each account represents a different revenue item. What if you could simplify your chart of accounts? What if every time you add a new product, you wouldn’t have to change your chart of accounts by adding a new account? Those Apple press conferences would be a lot less painful for you as the accountant and you could actually enjoy the cool new products those engineers are coming up with! Intacct helps with this with it’s Item dimension.

I define the Item dimension’s mission as:

The Item dimension allows you to simplify your chart of accounts, while allowing you to still track item differentiations. We leave your chart of accounts with the responsibility of managing the high level umbrella of item categories (revenue streams) and allow your items to specify actual variances of these revenue streams.

In our Apple example, your new Chart of Accounts would look like this:

Screen Shot 2015-10-19 at 7.53.09 PM

Your Items would be:

Screen Shot 2015-10-19 at 8.05.04 PM

Each Item is tagged with a GL group which is then tied to your GL account in the Order Entry Posting Configuration. (If using standard process workflows, it’s directly tied on the item – even easier!).

Then, in reporting you can report on these individual Revenue streams summarized, or you can expand by items showing details. Below, is a sample report which shows sales by customer expanded by items.

Screen Shot 2015-10-19 at 8.19.24 PM

Also, just like the customer dimension you can add custom fields, group items, and create dimension structures with your items.

Intacct: Customization Documents & Custom Invoices

Invoice requirements vary from one company to another. While a standard invoice may work well for most companies, often times your customer’s invoice requirements dictate your invoice format (especially if your customer is a government body or simply your largest customer). And, well, if you like having a positive number in your bank account, it’s highly unlikely you’re going to argue with your clients. So you are faced with two options: spend your time editing a document outside of your system (and let your coffee get cold in the process) or let your accounting software customize invoices for you! That becomes possible in Intacct with Custom Documents.

This post will be an introduction to customizing your Invoices citing one of the more popular requests I have come across: displaying a “Paid” stamp on a paid invoice.

The Problem: When printing an invoice, no payment stamp displays at the top of a paid invoice. Four simple letters you would think would be so simple to add! Yet, they are not there.

The Solution: Custom Documents using Merge Fields and IF statements.

First, where are custom documents managed?

Customization (or Platform) Services > Custom Documents

Custom Documents

That brings you to the following list screen – so now what?

Custom Documents List Screen

Continue reading

Filtering Reports & Dashboards for Current User

Sorry for disappearing for a little while – I haven’t abandoned the blog quite yet! So now back to your regularly scheduled Intacct program …


It’s 10pm, you close your laptop, close your eyes and then suddenly you remember the project managers are expecting to receive a report on their projects P&L. Your eyes scrunch closed trying to erase what you just remembered from your memory. That didn’t work. So you open your eyes and open up your laptop to Intacct (at least you switched to the cloud so you can do this from home instead of waiting until the morning). Alright, here we go let’s create 10 reports. But wait! Remember Intacct’s spring 2015 release? Didn’t they say something about filtering reports and dashboards for current users? That caught your attention. So you think to yourself: “I can create 1 report and just filter for the project manager!” Done. So let’s do this!

Continue reading

Creating Value That Doesn’t Exist … Yet

Every business ever started, every invention ever created, began with one question:

“What do I know; what valuable information do I possess somewhere in my brain that would be valuable to someone else that they do not have?” 

I learned that lesson in this podcast on Pat Flynn’s website called Smart Passive Income (which if you’ve never heard of – he’s awesome! not just because he’s from my hometown of San Diego. Go listen to it – you’ll be inspired!).

I was listening to this on New Years Day and still remember this part:

Pat was interviewing Joseph Michael (the Scrivener Coach). Joseph Michael (not his real name) was talking about how he had gotten started. He had first created an eBook on topics he knew, SEO and that type of thing – he described it as the most beautiful eBook that no one ever saw. He spent months on it, thinking that’s what people wanted, people were going to love it! Turned out, nobody wanted it. That’s when he learned his first lesson:

You have to survey your audience. You have to build what they want, not what you think they want.”

So one day, he was struggling with teaching his daughter how to tie her shoes. (Something so instinctive that most people forget how they even learned.) So he did some research. He then did a post on teaching his daughter to tie her shoes (of all things). That post went viral! This beautiful eBook he had spent months on creating, no one read, then this post on tying shoes goes viral! That’s an important lesson: It solves a very specific problem that people really need. He went on to create the Scrivener eBook (during his lunch breaks in his car and after work), and was able to quit his job.

Continue reading