Creating an HTML table using jQuery

Last month I battled how to create an HTML table using jQuery. It sounded simple and definitely possible to me but it took some time to get it to work. Since it took some time I wanted to write it out to keep notes for myself in the future and help others who might also be trying to do the same thing.

I wanted to use jQuery so that I could create a nice clean table with 50 rows without having to actually write out an HTML table with 50 rows in it (that would be painful and I would probably hate for anyone to actually look at it and see that’s how I got the 50 row table built in the first place). So jQuery was a logical solution. I knew I could use an array to loop through and create 50 rows but I had never done it before.

My table at first without jQuery:


And yep that’s just 3 rows … yikes!

My table using jQuery:


My 50 row table is shorter than the original 3 row table. So cool!

And here’s what the table looks like:




jQuery to Autofill Dates and Increment Annually

My idea of Friday afternoon fun was learning some date functions! In my last post I went over how to take the value of the total amount entered in a field and split it into even installments of related fields based on the number of years in another field and a button push. (Ex: Enter $300,000 in field A, enter 3 years into field B, then push a button, update field C, D, and E with $100,000 each). That was pretty neat! I learned .val and how to use arrays.

Now, Friday my goal was to figure out how to auto-fill 3 more fields with dates based on the start date and number of years fields. For example, a user enters a start date of 1/1/2017 and 3 years… I then wanted the three date fields next to the amounts to auto-fill with 1/1/2017, 1/1/2018, and 1/1/2019. Easy, right? Not quite. When I first tried I didn’t realize that simply adding “1” would basically result in 1/1/20171, 1/1/20182, 1/1/20193. Not exactly what I want. Because I wasn’t treating my field as a date, I was just adding 1 as a string to the end of my value.

Here’s what I ended up with:

Screen Shot 2017-05-08 at 7.14.16 PM.png

A couple things I had to learn:

1.Declaring my date field variable as a Date: var myDate = new Date(input);

I had to set my date field as a value that I could do a calculation on in ms.

2. Doing a calculation on dates: myDate.setFullYear(myDate.getFullYear() + 1);

myDate.setFullYear() sets the year of my date. So I’m saying, take my field “myDate”, then set the Year. What should the Year be? That which is in the parenthesis! Another Date function. Take myDate and get the full year then add 1. So, in essence I am saying I want to set the year of myDate… what do I set it to? myDate’s year +1. Awesome! Now how to throw that into my fields within my array?

3. Well, I actually had my first date be auto-filled to equal the start date. So I can skip field i=0. Then, I only needed the array to auto-fill the other date fields.

So I started my array at 1 rather than 0 like in my last post. So the jQuery will only fill my fields starting with gm_grantapp_ar_invoice_due_date1 … through the number of years value.

4. Learn some jQuery: ($.datepicker.formatDate(‘dd/mm/yy’, myDate));

This statement says the value of the gm_grantapp_ar_invoice_due_date[i] field should be the value of myDate that I calculated in #3 above, then format it.

A final note, if you’re learning these as you read along: here’s a helpful note on syntax of jQuery. It follows the format: $(field).action(value)

  • Meaning … what field (HTML) am I editing is declared first. (after $ which calls the jQuery library).
  • Then, a period.
  • Then, what function/action am I using (getYear, setYear, val()).
  • Finally, what the value of the field should be is in parenthesis.

Easy, right?! Keep tuning in for more fun javascript/jQuery!

On a separate note, I learned how to use Process Builder in SalesForce today and have a practical use-case for it so if you like my mixed bag of topics keep your eyes open for an upcoming post on that!


jQuery to Auto-Fill Fields with HTML Buttons

Have you ever noticed when placing an order on certain sites you can enter your shipping address and then have the option to check a box that says “my billing address is the same as shipping”? Then, like magic, the shipping address fields auto-fill? It’s a cool little efficiency isn’t it? Why type something twice when it’s identical? (Taken for granted a lot but cool non-the-less). Have you ever wondered how to do that?

Today’s post will be a slight detour back to Intacct but in a broader sense. While the example is Intacct-specific to something I needed (mostly wanted because it’s cool) for an app I’m building, the use-case is not and applies to any site where you can add html and jQuery.

So how do you do it?

Continue reading

SalesForce: Formulas

SalesForce formulas have many potential uses. Today’s post will cover one of my favorites:

Deploying SalesForce formulas as a simple mechanism to overcome potential system integration point hiccups without adding any additional data entry for users.

Imagine you would like to integrate SalesForce Opportunities with Intacct Sales Transactions. On the Intacct Sales Transaction, you would like to send from SalesForce to Intacct the Opportunity Owner field. Great! We know exactly what we need to sync.

SalesForce Opportunity Owner Field:


Intacct Employee Field:


Why might we want to do that?

  1. Visibility/Ease of Use: The finance team can simply look at a sales transaction from within Intacct to see who the owner of the opportunity is and reach out to them with questions (or even send a collaborate/chatter message).
  2. Reporting: There is added benefit of reporting. Being that Employee is a dimension in Intacct, you’ll have all the added financial reporting capabilities.

Herein lies the Problem:

The Employee field is on the Intacct Sales Transaction line item, while the Opportunity Owner field in SalesForce is on the SalesForce Opportunity (instead of associated Products). Why is that a problem for us? Well, the standard integration doesn’t allow for syncing fields located on the SalesForce Opportunity to Intacct Sales Transaction line items. In addition, the Employee ID that is passed through needs to match what is set up in Intacct – which is likely not what is set up in SalesForce.

Don’t give up yet! How do you solve these two issues? You probably guessed from the title of this post: Formulas!

Continue reading

excelwizards: SalesForce

It has been a few months almost a year(!) since my last post on excelwizards so I thought a revival post was due. This blog began as a way to share Excel tips with no expectations whatsoever (being there are so many other Excel sites at least this one hopefully kept you awake while teaching you something new). It then evolved into Intacct tips, tricks, neat things, and how-to’s as I went through an implementation first hand and eventually learned Intacct and made my way into consulting.

Continue reading

Smart Event API Calls – Updating Line Items on Sales Transactions

Have you ever wanted Intacct to update a corresponding custom field on a transaction line item based on another field? Have you ever wanted to update a field in a transaction’s line items based on a header level field? Those are two things I learned this weekend.

Problem 1: If you use Intacct’s merging transactions option in Order Entry to merge multiple Sales Orders to one Sales Invoice you might have noticed any custom fields on the Sales Order don’t propagate through to the merged Sales Invoice. This was especially bad news in this case because we had a custom invoice template set up which was grouping items and subtotaling based on that field. It didn’t make sense for the person entering sales orders to then also have to edit the merged sales invoice because Intacct clears those custom fields upon merging.

Solution: A Smart Event API call which looks at the Item’s name to fill out the custom field. The solution took some creative thinking. I noticed the list of items that require this custom field to be filled out either have a name containing: MAINTENANCE – S… or MAINTENANCE – U… So I thought I could write an API call with a condition so that if the Item’s Name starts with “Maintenance – U” the custom field is filled out with Unscheduled and if it starts with “Maintenance – S” the custom field is filled out with Scheduled. Neat, right?

Continue reading

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!