In this blog, we’ll share a helpful technique for using a CASE formula in a custom field to replicate the native tracking link functionality and expand it to include dynamic links for any carrier that offers online tracking capability. This simple customization will give your customer service team one-click access up-to-date tracking information on any shipment, regardless of carrier. Because it leverages the formula interface within the custom field, it requires no scripting and is a great basic customization project for NetSuite administrators looking to expand their skills.
CASE Statements in Formulas
CASE statements allow for the inclusion of if/then logic in your formula. For example, consider the below statement:
CASE WHEN {leadsource} = ‘Web’ THEN ‘Online Shopper’ ELSE null END
This statement looks to the Lead Source field on a customer record and returns the value “Online Shopper” if the Lead Source is “Web.” If the Lead Source is any other value, then nothing is returned (indicated by “null” in the CASE statement). CASE statements can be layered to include multiple conditions to return an array of results depending on which conditions are met. This structure will be the basis of our tracking link formula.
The Solution
Start by creating a custom transaction body field by navigating to Customization > Lists, Records, & Fields > Transaction Body Fields > New. Set the Label, ID, and Description fields as desired, leaving Type set to Free-Form Text and Store Value checked.
On the Applies To subtab, check the Sale box to allow this field to be displayed on sales order records.
Use the Display subtab to define where the field should be displayed and add help text to let users know what this field does. Additionally, set the Display Type as Inline to prevent users from accidentally editing or deleting the tracking link.
This next step is where the magic happens. You’ll define your formula on the Validation & Defaulting subtab, by placing it in the Default Value section. Make sure that the Formula box is checked to allow the system to display the information correctly in the custom field.
The formula that you will enter will vary based on the carriers that you need to include, but this same format can be applied to practically any carrier. This formula may look a bit complicated, but it’s actually quite straight-forward once you understand the structure. Here’s a sample:
CASE WHEN {fulfillingtransaction.shipmethod} LIKE 'FedEx%' THEN '<a href="https://www.fedex.com/apps/fedextrack/?tracknumbers='||{linkedtrackingnumbers}||'&language=en" target="_blank">'||{linkedtrackingnumbers}||'</a>' WHEN {fulfillingtransaction.shipmethod} LIKE 'UPS%' THEN '<a href="http://wwwapps.ups.com/WebTracking/track?HTMLVersion=5.0&loc=en_US&Requester=UPSHome&WBPM_lid=homepage%2Fct1.html_pnl_trk&trackNums='||{linkedtrackingnumbers}||'" target="_blank">'||{linkedtrackingnumbers}||'</a>'
WHEN {fulfillingtransaction.shipmethod} LIKE 'Canada Post%' THEN '<a href=" https://www.canadapost.ca/trackweb/en#/details/'||{linkedtrackingnumbers}||'" target="_blank">'||{linkedtrackingnumbers}||'</a> WHEN {fulfillingtransaction.shipmethod} LIKE 'DHL%' THEN '<a href="http://www.dhl.com/en/express/tracking.html?AWB='||{linkedtrackingnumbers}||'&brand=DHL" target="_blank">'||{linkedtrackingnumbers}||'</a>' WHEN {fulfillingtransaction.shipmethod} LIKE 'USPS%' THEN '<a href="https://tools.usps.com/go/TrackConfirmAction_input?strOrigTrackNum='||{linkedtrackingnumbers}||'" target="_blank">'||{linkedtrackingnumbers}||'</a>' ELSE null END
If you have experience with HTML, you might notice a familiar string in this formula. To achieve the functionality of having a hyperlink to the tracking URL, I’ve included the standard HTML format for a text link. This is structured as follows:
<a href=”http://www.websiteaddress.com” target=”_blank”>Link Text</a>
This string of code would be output as the words “Link Text” with a hyperlink to www.websiteaddress.com. The portion that reads target=”_blank” causes the browser to load the URL in a new window instead of loading it in the existing page.
The current FedEx tracking URL is formatted as such, with the actual tracking number in place of TRACKINGNUMBER:
https://www.fedex.com/apps/fedextrack/?tracknumbers=TRACKINGNUMBER&language=en
What we need to do is to pull the tracking number that is already stored in NetSuite’s standard Linked Tracking Numbers field into the correct location within the URL. To do this, we break the string into multiple sections and concatenate the tracking number in the specified spot by using the NetSuite field ID. To concatenate values together, we use double pipes (“||”). The full string looks like this:
'<a href="https://www.fedex.com/apps/fedextrack/?tracknumbers='||{linkedtrackingnumbers}||'&language=en" target="_blank">'||{linkedtrackingnumbers}||'</a>'
Single quotes are used to frame each piece of the HTML string, with the double pipes added before and after the NetSuite field ID for the Linked Tracking Numbers ({linkedtrackingnumbers}). When this is displayed, the tracking number will be shown as text linking directly to the tracking URL. This same approach can be used for any tracking URL. Simply navigate to the carrier’s website and track a package, copy the complete URL (taking note of where the tracking number falls in the string), and break the URL into segments as shown in the examples, replacing the tracking number with {linkedtrackingnumbers}.
Now that you understand the structure of the link text, we need to address the logic that will ensure that the correct URL is displayed based on the shipping method that is used. The tag {fulfillingtransaction.shipmethod} is a join that refers to the Ship Method field on the fulfilling transaction for that sales order. Since each carrier will likely have multiple shipping methods available, I’ve used the format LIKE ‘Carrier%’ to identify shipping methods that include the carrier’s name instead of trying to find an exact match. The percent sign (“%”) is a wildcard, which tells the system to look for shipping methods that start with the carrier’s name but may include additional text afterwards. The string LIKE 'UPS%' would identify both UPS Ground and UPS Standard as matches but would not identify U.P.S. Ground as a match.
Once you’ve added all of your carrier URLs to the formula, make sure the end it with ELSE null END. This ensures that if no match is found, the field is left blank.
After configuring your custom tracking link, you can use this same logic to add variable data points to records of all types using custom fields. This simple customization is infinitely powerful – with a bit of creativity and patience, the possibilities are truly endless!
If you have any additional questions, don't worry - the consultants at Concentrus. Visit our support page to talk to one of our NetSuite experts today!