Tag: NetSuite

Categories
Cloud Apps

How To Fix Oblique Advanced PDF/HTML Template Errors In Netsuite

When working with Advanced Templates, and the FreeMarker syntax in NetSuite there can be some oblique errors which make it difficult to diagnose.

To help diagnose any such errors, I highly recommend writing your templates in a code editor.

I personally prefer the editors from JetBrains as they package everything you need in one nice editor. To effectively write FreeMarker templates with syntax highlighting and auto-completion you will need the IntelliJ Editor.

Template cannot be saved error

When you get an error from NetSuite such as something of the form:

The template cannot be saved due to the following errors: Parse exception during template merging. Cause: Encountered “&” at line 128, column 89 in template. Was expecting one of: … … “false” … “true” … … … “.” … “+” … “-” … “!” … “[” … “(” … “)” … “{” … …

What I’ve found is when I return to my editor to hunt for the problem I don’t see anything on line 128??

So what is going on with this error?

To properly diagnose the error, copy the code as it is in the Advanced Template window. Then paste this code back into your text editor and then look at line 128.

You should notice with the newly pasted code that NetSuite may have parsed through some lines and edited it for you. Some lines may have been concatenated together and other lines may have page breaks.

With the newly inserted code you should be able to better diagnose the problem.

In the case of the error above I would navigate to line 128, and I’d see where NetSuite is struggling with my template code.

In the example above it appeared NetSuite doesn’t like using the   HTML entity directly in the code. So just changed this to a space character.

The error appeared to go away when I tried saving the template again. In fact if you apply any innocuous edit it should save just fine.

The important thing to remember though, especially if you’re scratching your head and looking at the error, and your code on side-by-side screens: copy the code from NetSuite and then you’ll see the error.

Categories
Cloud Apps Coding

How To Print More Than 1 Page In Advanced PDF/HTML Templates

I like NetSuite’s Advanced PDF/HTML templating system with the FreeMarker syntax. However, there are a couple of issues I’ve run into and one just recently:

How can you force printing on two or more pages?

One example I’ve been working on is the ability to print “how to pay” information on the back of our invoices that are issued to our customers.

As our invoices can span a few lines we have been squeezing that information down in the footer of the page. This makes for small font and errors from the customer when entering payment details. I mean, you’ve got a lot more space on the back than you do in the footer! It would be so much more convenient if this information were to be printed on the back of the invoice, but to do this it would mean getting the template to do a page break.

After asking for this feature request to NetSuite some time ago, I came across an interesting, and simple, CSS technique that actually works!

The solution?

  1. Just measure the dimensions of the paper you would like to print. Or obtain standard paper dimensions from the web.
  2. Enter this information into a CSS class in the <style> area within your PDF/HTML template.
  3. Reference this class whenever you want to do a page break.

Here’s a simplified template demonstrating this concept:

<?xml version="1.0"?>
<!DOCTYPE pdf PUBLIC "-//big.faceless.org//report" "report-1.1.dtd">
<pdf>
    <head>
        <macrolist>
            <macro id="nlfooter">
                <table>
                    <tr>
                        <td>This is the footer. I've chosen to span this over each page.</td>
                    </tr>
                </table>
            </macro>
        </macrolist>
        <style>
            /* Insert the physical dimensions of the size of the paper you print.*/
            /* Width = page width; Min-height = page height; */
            /* (You may/may not need to have `margin-right:1.5cm`. I found the contents spilled off the page without it.)*/
            .page { margin-right: 1.5cm; width: 21cm; min-height: 29.7cm; }
        </style>
    </head>
    <body footer="nlfooter" footer-height="15%">
    <div class="page">
        <h1>Welcome to the first page!</h1>
        <p>Place all your first page information here. For example, you may want to put your company logo, the invoice date, number, contact address of the customer, and then you'll want to loop through all the items in the invoice in a nice neat table.</p>
        <p><strong>Notice how the right-margin is a little funny?</strong></p>
        <p>You may need to increase the `margin-right` value on the `page` class attribute to make this margin look nicer on the page. Play with it.</p>           
    </div>
    <div class="page">
        <h1>Welcome to page 2!</h1>
        <p>You should now see this on the second page.</p>
        <p>This is where you can place additional information on whatever else is important, i.e. how to pay, etc.</p>
    </div>
    </body>
</pdf>

As you can see from the comments, all you need do is enter the dimensions of the size of the paper into the .page attribute and then reference that class whenever you want to apply a page break.

How did that demo turn out?

Have a look at the demo copy of the PDF and see for yourself.

Nice!

Categories
Cloud Apps

Netsuite Aggregate CASE Values

If you want to show in your Saved Search results an aggregation of your results from a CASE statement then you will want to apply the Sum aggregation type in the Group section of your saved search field.

Let’s illustrate this through the use of an example.

For example, let us assume I want to find out the BALANCE of an Account on what has been charged for our customers and what remains pending in scheduled Sales Orders that have a future invoice date.

To perform this type of task I would create a Transaction Saved Search.

In the criteria area I need to apply the following:

Account     25XXX; 25XXX (Whatever you GL Account Number is)
Date        This fiscal year (Or any other date range or condition you would like)

Then in the Results area create the following fields:

Name                Group
Formula (Number)    Sum     ** see code #1 below **
Formula (Number)    Sum     ** see code #2 below **

As you want to cluster the results based on the customer name, we aggregate the data by grouping the Name field. Therefore, our next two fields will be the aggregate SUM of each customer according to the criteria above.

Our first CASE statement tries to calculate a balance by adding all the invoices created and subtracting any credits applied:

// CODE #1:
CASE WHEN {type} = 'Cash Sale' or {type} = 'Invoice' THEN
    ROUND( ABS( {amount} ), 0 )
WHEN {type} = 'Credit Memo' or {type} = 'Cash Refund' THEN
    ROUND( -1 * {amount}, 0 )
END

Notice how the CASE statement just returns the {amount} of the invoice/credit. Therefore, if we have the following transactions in our results:

IDGL A/cAmount
INV001A/C 25XXX-$200
CM001A/C 25XXX$200

As these transactions progress through CODE #1 case statements the results will be:

ROUND( ABS( -$200 ), 0 )
+
ROUND( -1 * $200, 0 )

With the result being:

ROUND( 200, 0 ) + ROUND( -200, 0 ) = 0

The next formula CODE #2 is a little more complicated, but is a good example of demonstrating nested case statements:

CASE WHEN {customer.status} = 'Active' THEN 
    CASE WHEN {type} = 'Sales Order' and {status} = 'Pending Billing' THEN 
        ROUND( ABS( {amount} ), 0 ) 
    WHEN LENGTH( {createdfrom} ) > 0 and {createdfrom.status} = 'Pending Billing' THEN 
        CASE WHEN {type} = 'Cash Sale' or {type} = 'Invoice' THEN 
            ROUND( {amount}, 0 ) 
        WHEN {type} = 'Cash Refund' or {type} = 'Credit Memo' THEN 
            ROUND( -{amount}, 0 ) 
        END 
    END 
END

Let’s take this CASE statement apart:

  • CASE WHEN {customer.status} = 'Active' THEN

Our first main condition is to determine if the Customer’s status field of the customer is they are still 'Active'.

  • CASE WHEN {type} = 'Sales Order' and {status} = 'Pending Billing' THEN

Our first nested condition for Customer’s who are active are to determine if the type of the Transaction is a Sales Order, and the status of this Sales Order is Pending Billing.

If this is true then we want to round the absolute value of the transaction’s amount.

  • WHEN LENGTH( {createdfrom} ) > 0 and {createdfrom.status} = 'Pending Billing' THEN

The next condition applies to the remaining transactions which are not Sales Orders that are still pending billing. Here we apply further nested conditions:

  • CASE WHEN {type} = 'Cash Sale' or {type} = 'Invoice' THEN

If the transaction is a Cash Sale or Invoice then we round the amount field.

  • WHEN {type} = 'Cash Refund' or {type} = 'Credit Memo' THEN

Or if it is a Cash Refund or Credit Memo type of transaction then we want to apply the rounding of the negated {amount} field.

Then we make sure we’ve closed properly all our nested statements.

Conclusion

In this article you have learned how to aggregate saved search results by using the SUM group function and applying the Formula field with CASE statements to further refine your result set.

In our CASE statement formula you saw how you can apply nested CASE statements.

If you need something simpler you might want to read about the DECODE function.

Categories
Cloud Apps

Stuck Making Custom Reports in NetSuite?

Couple of quick notes in regards to NetSuite reports that I found today when playing around.

  1. Whenever you are importing GL Accounts into your report you can only have ONE import of that GL Account. This means that should you need to include multiple instances of a GL Account in a report that you would need to create a formula that references the GL Account.
  2. Another aspect is that there are different types of reports. One monitors movement (cash flow, income). Another monitors balance (balance sheet).
  3. When trying to create a report that contains balances, and you want to be able to set the report up to have multiple columns that span time periods, you will need to add Amount columns where they are related to a date and then you set a Custom Date to a specific period. This means if you’re looking to see how certain GL Accounts have spanned over the year, on a monthly basis, you would need to add multiple Amount columns and set the Custom Date for each month.
  4. The accounting period total in a report is perhaps the best way to try and see over a period of time what the amounts have been. You can have Amount and Budget Total side-by-side in the accounting periods that you want to view, you can also add a Budget Amount: which is found in the budget collapsed menu on the left-hand side.
  5. You can also reference other reports and their totals this might be handy if you want to have a consolidated report that references all the individual entities such that if you had a cash flow statement and you wanted to be able to show the movements on each level all the different subsidiaries and what their ending cash flow balances are.
  6. To be mindful that you can only reference other reports that are of the same type such that you can only reference movement type reports and balance type reports against each other.
  7. There are some filters that are sticky in that they cannot be removed, however what you can do is that you can add the same filter and restrict the report further. For example, if you don’t want the ability for users to select a department or subsidiary you can set that by adding a filter and selecting the department or subsidiary and then not showing that in the report.
  8. Be also mindful that whenever you are reversing the sign that it’s perhaps probably best to just reverse the sign on the account level rather than the total level or the summary level, but it obviously does depend on what you’re trying to show.
  9. You might also find that if you do have two reports that are of the same type that it might still not be able to be referenced because the columns are of a different type. For example, you might have Budget Amount whereas the other reports have Actual Amounts.

Anyway, just thoughts and limited experiences.

Categories
Cloud Apps

Where Is The “IF” Statement In A Saved Search Formula?

Saved searches are a powerful tool to fetch and display data in NetSuite. However, there may be times with the display of our saved search data where we want to transform the data into something else.

To display data in a format based on a condition we need to use the Formula fields in the Result tab.

Within the Formula field for us to apply a condition we can use either the following formulas:

  • DECODE
  • CASE

DECODE NetSuite Function

The DECODE function in NetSuite’s saved searches allows you to apply a simple equality comparison. The parameters of the function are as follows:

DECODE({field_name}, equality_result, true_response, false_response)

For example, if I have Formula (Text) result column a simple condition based on whether an employee is a Sales Rep, would be:

DECODE({issalesrep}, 'T', 'Sales Rep', 'Not Sales Rep')

Let’s explain this example briefly:

  1. The first parameter is the field we want to have our condition checked, in our case it’s the Sales Rep field on an Employee Record {issalesrep}.
  2. The second parameter is what you want the value of this field to be, if it’s checked we use T.
  3. The third parameter is what you want to show if the condition is TRUE, here you display the text 'Sales Rep'.
  4. The fourth parameter is what you want to show if the condition is FALSE, here you display the text 'Not Sales Rep'.

The DECODE function is the simplest conditional check you can perform on your result data set.

If you require more or even nested conditions then you’ll want to apply the other type of conditional form in a Formula field.

CASE NetSuite Statement

The CASE statement is the most common type of condition check in a NetSuite saved search Formula field. It provides greater flexibility in the conditional area of the CASE statement.

Here is the schema of a CASE statement in the Formula field:

CASE WHEN conditional_statement_1 THEN
  value_if_true
WHEN conditional_statement_2 THEN
  value_if_second_condition_true
ELSE
  default_result_if_no_conditions_met
END

Nested CASE Statements

If you’d like to nest conditional statements within a CASE statement you can easily do so by starting another CASE statement inside, like so:

CASE WHEN original_condition THEN 
    CASE WHEN nested_condition_1 THEN 
        nested_case_result_if_true
    ELSE
        nested_case_result_if_false
    END
ELSE
    original_condition_if_false
END

You can continue to nest CASE statements provided you open the CASE statement within the THEN clause of the parent CASE statement.

Conclusion

In this article you have learned how to apply conditions on your saved search results using the Formula fields. You have seen the two types of conditions you can use within your formula, namely:

  1. DECODE
  2. CASE

And how you can apply the right function or statement according to the complexity of the conditional check or checks you want to undertake.

You can continue to learn more about CASE statements by reading about the following similar post on nested CASE statement with aggregation.

Categories
Cloud Apps

How To Loop Through A List In Advanced PDF/HTML Template

When working with NetSuite’s Advanced PDF/HTML Templating system it’s difficult in being able to find documentation on the syntax structure for working with logic in the templates.

One of the requirements I had was being able to determine whether the quantity ordered on an item has the same quantity ordered as the previous item. This required the ability to determine that the item being iterated through with the #list function was not the first item (otherwise it wouldn’t have been able to compare to the previous item – as there would be none!) and if the item was NOT the second to be able to compare the previous item’s quantity to its current quantity (if it’s then different to do stuff).

Here’s how the template in this respective area then worked:

<#list record.item as i>
<#if i_index == 0>
<!-- it's the first item so just enter the quantity -->
${i.quantity}
<#elseif i.quantity == record.i[item_index-1].quantity>
<!-- if the item matches the same as the previous quantity -->
${i.quantity}
<#else>
<!-- if the quantity of this item differs with the previous -->
<b>${i.quantity}</b>
</#if>
</#list>
view raw ns-looping-list.html hosted with ❤ by GitHub

The important elements to be mindful of when writing this code is:

  • Don’t use ${ and/or } within the if statements – these are for placing those data elements directly into the HTML.
  • Be wary on using just < or > or <= or >= in your logical expressions, otherwise you’ll receive an error – something to the effect of:

The template cannot be saved due to the following errors: Error on line 133, column 34 in template. Expecting a boolean (true/false) expression here. Expression i.quantity does not evaluate to true/false . it is an instance of com.netledger.templates.model.NumberModel

To get around these problems do any one of the following:

  • Wrap your logical expression containing your greater-than or less-than sign in brackets, such as <#if ( i.quantity > record.item[item_index-1].quantity )>...</#if>
  • Use the shortcode expressions (gt is >, lt is <, gte is >=, lte is <=), such as <#if i.quantity gt record.item[item_index-1].quantity>...</#if>

In fact if you use any sort of logical operator in your expression it’s probably best practice wrapping it in brackets:

<#if ( ( i.quantity > record.item[item_index-1].quantity ) && ( i.quantity > 1 ) ) >...</#if>

More information on the syntax used in NetSuite’s Advanced PDF/HTML Templating system can be found at the FreeMarker website.

Categories
Cloud Apps

Problem Saving Temple: Index 2, Size 2 In NetSuite

If you’ve been tinkering around with the wonderful Advanced PDF/HTML Template within NetSuite there are times when you’ll receive some ambiguous errors.

I received one today which had me scratching my head for quite some time, it was:

Problem saving template: Index: 2, Size: 2

Whenever you encounter this error it’s trying to tell you there’s an error in your table structure with the number of rows and cells – this will need fixing otherwise it will not save your template.

To try to debug where the error lay go back to your template and click on the Save button again to see if a different prompt occurs – if you’re lucky you’ll be given another ambiguous error, but this time you’re going to try to read this new error to determine where the FreeMarker parser is actually encountering problems.

With this line number go back and copy your parsed code and paste it into your favourite text editor to try to locate the line.

Even though this line will not likely be where the error is it will likely put you in the right area. When you are here you need to begin checking your tr and td tags and any rowspan or colspan calls within those tags.

In my case the second error was pointing me to the footer macro area of which I had the following code which produced the original ambiguous error:

<table>
    <tr>
        <td rowspan="2"></td>
    </tr>
    <tr>
        <td></td>
    </tr>
</table>

From the above you can see with my first td tag I made it span over 2 rows, however, with the second td tag it didn’t know where to go as I hadn’t created an additional column for it. In the end I amended my code to the following which made it no longer throw an error:

<table>
    <tr>
        <td></td>
        <td></td>
    </tr>
</table>

I just removed the rowspan parameter as I found I didn’t really need it and ended up combining it into one row.

Categories
Cloud Apps

Advanced PDF/HTML Template A4 Pixel Dimension With NetSuite

If in NetSuite you’re designing a template and need to know how wide you can go with a table or other HTML element such that it fits snugly on to the page (in pixels), then the maximum width I found with NetSuite’s default page margins is 675px.

Knowing this can help you set the width size of two (or more) adjoining tables that need to be squeezed in together on the same horizontal as shown in the diagram below:

A4 Template Width boxes
A4 width in Advanced Templates is 675 pixels

Unfortunately the default template view will look as though you can squeeze two tables with widths that total 816px, but when it comes to print it’s a different story.

Hopefully this helps as you develop your Advanced PDF/HTML Templates – it took a few trial and errors before I hit it.

Categories
Cloud Apps

Import Chart of Accounts to NetSuite – ParentRef

One of the challenges when importing the chart of accounts into NetSuite is trying to understand how a parent reference is to be configured.

Unfortunately an example template I’ve come across doesn’t differentiate what is what as per the following:

accountNumberaccountTypeRefnameparentRef
6000ExpenseExpense
6010ExpenseTravel & Entertainment6000 Expenses
6020ExpenseMeals6010 Expense : Travel & Entertainment

What is most confusing with this example is the final row where you don’t really know which reference Expense is referring too? Is it the accountTypeRef? Is it the name of the uppermost parent?

What is to happen when you need to go down another sub-level in your accounting structure – what exactly is the pattern, is it:

Uppermost level's account name : next sub-level's account name : next sub-level's account name...

Unfortunately after testing a few different varieties which weren’t working for our nested account structure I eventually found the successful import, in a pattern it would look like this:

AAAA HLName : SLName : ... : LSLName

Where:

  • AAAA is the nearest sub-level’s account number
  • HLName is the name of the account starting at the highest level
  • SLName is the name of the account at the next sub-level
  • ... is the name of additional sub-level account names
  • LSLName is the name of the account that represents the nearest sub-level’s name (if this account has a number it is that which is written in at AAAA above).

As an example, if I have the following structure:

+ 6000 - Expenses
    + 6100 - Operational Expenses
        + 6110 - Local Expenses
            + 6111 - Printing

Where 6000 is a main header account, 6100 the next sub-level account header, 6110 an additional sub-level account header and 6111 being a posting account. Our import structure for just this data using the above table as our basic template would look as follows:

accountNumberaccountTypeRefnameparentRef
6000ExpenseExpenses
6100ExpenseOperational Expenses6000 Expenses
6110ExpenseLocal Expenses6100 Expenses : Operational Expenses
6111ExpensePrinting6110 Expenses : Operational Expenses : Local Expenses

This portion of the import will work successfully and when done you should receive 100% success status.