Intacct: Platform Services Formula Fields

2016-03-27_12-49-34

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);

2016-03-27_12-48-38

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!

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s