Saved Search – Ageing and Linking

by Posted on

Continuing on from other posts, some more saved search tricks and tips that I have picked up recently!

URL & Record Linking

Occasionally, you will need to link to a record or page in Netsuite in a saved search and find that Netsuite (rather unhelpfully) cannot do it. The reason for this is usually due to the type of search you are using;
A transaction search will natively link quite happily to an Employee record from an Employee field – whereas a Custom Record search, with a transaction field, often wont provide a link to the transaction automatically

You can get around this by using “CONCAT” inside a Formula (text) field to link to the transaction. Adding HTML in single quotes inside this field enables the use of a hyperlink that will render for every row
This method does use the Netsuite URL for linking, so should be checked upon every new release to ensure it still works

CONCAT(CONCAT(CONCAT(CONCAT('<a href="../../accounting/transactions/custinvc.nl?id=',{custrecord_xxx.internalid}),'">'),{custrecord_xxx.number}),'</a>')

Several CONCAT functions are used inside each other to create a continuous statement when run

  1. This is the relative URL for the Customer Invoice page. Adding “?id=” in the URL means we will be passing the page an ID to load
  2. Is the ID from the search we want to load into the Invoice page
  3. Once we have the URL, we can also define a display name for the link – in this example I am using the Invoice Number

This link will appear for each row in the search that has a linked invoice.

Ageing Formula

Another trick of using HTML inside a saved search formula field is to have Netsuite calculate “Ageing” for an invoice and display that inside a Formula (text) field.

The below code on a transaction search will display the amount of days an invoice is due – positive for overdue, negative for not yet due. The code measures the different between the due date and the current date; this is all done inside a case statement

I also coded in three different colours for this field

  • F7DBD7 – Red – 10 days overdue
  • FAEACB – Yellow – Less than 10, greater than 5 days overdue
  • CDE7CD – Green – anything that is not due yet
case when round(({duedate}-{today})*-1) > 10 then CONCAT(CONCAT('<div style="background-color:#F7DBD7; text-align:center; padding:10px;">',round(({duedate}-{today})*-1)),' days</div>') when round(({duedate}-{today})*-1) < 10 and round(({duedate}-{today})*-1) > 5 then CONCAT(CONCAT('<div style="background-color:#FAEACB; text-align:center; padding:10px;">',round(({duedate}-{today})*-1)),' days</div>') else CONCAT(CONCAT('<div style="background-color:#CDE7CD; text-align:center; padding:10px;">',round(({duedate}-{today})*-1)),' days</div>') end

When run, invoice ageing is shown – with an easy “RAG” (red amber green) status

Hopefully you find this useful!

Published by samlystor