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!
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:
Step 3: These fields are then popped right into the API that creates the Sales Order and bam! Date field filled out!
This formula takes the date “03/27/2016” and gives me just the last 4 digits.
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!