Published on July 30, 2020.

A few weeks ago I wrote about NetSuite's SuiteQL Built-In Functions and showed how you can use the "DF" function to easily map a transaction's status ID to something more helpful. For example, a sales order transaction with a status of "G" is actually "Sales Order : Billed." Using the DF function saves you from having to join to another table to get the statuses.

I wanted to follow-up and show how the DF function can be used to map a number of other fields as well. But first, here's a little more info about the function.

In the NetSuite Help Center (topic "Using SuiteQL with the N/query Module in SuiteScript"), the DF function is described as returning "the display value of a field from the target record type without having to join the target record type explicitly." To use the function, you simply pass a field name that "represents the relationship between the field and the source record type."

An Example

Let's take another look at how you can use the DF function in queries against the Transaction table. We'll start with this simple query:

SELECT
	*
FROM
	Transaction
WHERE
	ID = 271017

Here's the response:

{
    "links": [
        {
            "rel": "self",
            "href": "https:\/\/ns-attachments.r2021.a0729.suitetalk.api.netsuite.com\/services\/rest\/query\/v1\/suiteql?limit=1000&offset=0"
        }
    ],
    "count": 1,
    "hasMore": false,
    "items": [
        {
            "links": [],
            "abbrevtype": "SALESORD",
            "actualshipdate": "7\/6\/2020",
            "billingaddress": "2712",
            "billingstatus": "F",
            "closedate": "7\/6\/2020",
            "createdby": "4",
            "createddate": "7\/2\/2020",
            "custbody_chk_trans_id": "rxjm4xa495kc5fu0ka",
            "daysopen": "4",
            "email": "timdietrich@me.com",
            "employee": "4",
            "entity": "11",
            "estgrossprofit": "400",
            "estgrossprofitpercent": "0.6666666666666666",
            "foreigntotal": "600",
            "id": "271017",
            "isfinchrg": "F",
            "isreversal": "F",
            "lastmodifiedby": "4",
            "lastmodifieddate": "7\/30\/2020",
            "memo": "Test for Checkout",
            "nexus": "1",
            "number": "5",
            "ordpicked": "T",
            "otherrefnum": "TD2020-0702-001",
            "paymenthold": "F",
            "posting": "F",
            "postingperiod": "11",
            "printedpickingticket": "F",
            "recordtype": "salesorder",
            "shipcomplete": "F",
            "shipdate": "7\/2\/2020",
            "shippingaddress": "2713",
            "status": "G",
            "terms": "4",
            "totalcostestimate": "200",
            "trandate": "7\/2\/2020",
            "trandisplayname": "Sales Order #SO05",
            "tranid": "SO05",
            "transactionnumber": "SALESORD5",
            "type": "SalesOrd",
            "typebaseddocumentnumber": "SO05",
            "userevenuearrangement": "F",
            "visibletocustomer": "T",
            "void": "F",
            "voided": "F"
        }
    ],
    "offset": 0,
    "totalResults": 1
}

That's pretty straightforward. I'm simply requesting a specific transaction based on its internal ID.

Let's adjust the query and focus on a few specific columns.

SELECT
	Transaction.CreatedBy,
	Transaction.Entity,
	Transaction.Employee,
	Transaction.BillingAddress,
	Transaction.LastModifiedBy,
	Transaction.Nexus,
	Transaction.PostingPeriod,
	Transaction.ShippingAddress,
	Transaction.Status
FROM
	Transaction
WHERE
	ID = 271017

Here's the updated response.

{
    "links": [
        {
            "rel": "self",
            "href": "https:\/\/ns-attachments.r2021.a0729.suitetalk.api.netsuite.com\/services\/rest\/query\/v1\/suiteql?limit=1000&offset=0"
        }
    ],
    "count": 1,
    "hasMore": false,
    "items": [
        {
            "links": [],
            "billingaddress": "2712",
            "createdby": "4",
            "employee": "4",
            "entity": "11",
            "lastmodifiedby": "4",
            "nexus": "1",
            "postingperiod": "11",
            "shippingaddress": "2713",
            "status": "G"
        }
    ],
    "offset": 0,
    "totalResults": 1
}

As you can see, for those columns the response was a combination of internal IDs and codes. For example, the "createdby" value of 4 represents the ID of the employee that created the transaction. To get the name of the employee, we'd need to join to the employee record.

But as I showed in my previous post, the DF function gives us a convenient way to get that value without the need for the additional join. For example, look at this query.

SELECT
	Transaction.CreatedBy,
	BUILTIN.DF( Transaction.CreatedBy ) AS CreatedBy_Name
FROM
	Transaction
WHERE
	ID = 271017

Here's the response.

{
    "links": [
        {
            "rel": "self",
            "href": "https:\/\/ns-attachments.r2021.a0729.suitetalk.api.netsuite.com\/services\/rest\/query\/v1\/suiteql?limit=1000&offset=0"
        }
    ],
    "count": 1,
    "hasMore": false,
    "items": [
        {
            "links": [],
            "createdby": "4",
            "createdby_name": "Timothy Dietrich"
        }
    ],
    "offset": 0,
    "totalResults": 1
}

So I'm now requesting the CreatedBy column, as well as the name of the employee that created the transaction - and doing so by calling the DF function and passing the CreatedBy value. That's very convenient.

Here's one more query. This time I'm requesting a number of columns and their DF values.

SELECT
	Transaction.CreatedBy,
	BUILTIN.DF( Transaction.CreatedBy ) AS CreatedBy_Name,
	Transaction.Entity,
	BUILTIN.DF( Transaction.Entity ) AS Entity_Name,
	Transaction.Employee,
	BUILTIN.DF( Transaction.Employee ) AS Employee_Name,
	Transaction.BillingAddress,
	BUILTIN.DF( Transaction.BillingAddress ) AS BillingAddress_Name,
	Transaction.LastModifiedBy,
	BUILTIN.DF( Transaction.LastModifiedBy ) AS LastModifiedBy_Name,
	Transaction.Nexus,
	BUILTIN.DF( Transaction.Nexus ) AS Nexus_Name,
	Transaction.PostingPeriod,
	BUILTIN.DF( Transaction.PostingPeriod ) AS PostingPeriod_Name,
	Transaction.ShippingAddress,
	BUILTIN.DF( Transaction.ShippingAddress ) AS ShippingAddress_Name,
	Transaction.Status,
	BUILTIN.DF( Transaction.Status ) AS Status_Name
FROM
	Transaction
WHERE
	ID = 271017

And here's the response.

{
    "links": [
        {
            "rel": "self",
            "href": "https:\/\/ns-attachments.r2021.a0729.suitetalk.api.netsuite.com\/services\/rest\/query\/v1\/suiteql?limit=1000&offset=0"
        }
    ],
    "count": 1,
    "hasMore": false,
    "items": [
        {
            "links": [],
            "billingaddress": "2712",
            "billingaddress_name": "Richard Butler
123 Adamant Way
Hilton Head SC 54321
United States",
            "createdby": "4",
            "createdby_name": "Timothy Dietrich",
            "employee": "4",
            "employee_name": "Timothy Dietrich",
            "entity": "11",
            "entity_name": "4 Richard Butler",
            "lastmodifiedby": "4",
            "lastmodifiedby_name": "Jennifer Simpson",
            "nexus": "1",
            "nexus_name": "United States SC",
            "postingperiod": "11",
            "postingperiod_name": "Jul 2020",
            "shippingaddress": "2713",
            "shippingaddress_name": "Richard Butler
123 Psychedelic Way
Sedona AZ 12345
United States",
            "status": "G",
            "status_name": "Sales Order : Billed"
        }
    ],
    "offset": 0,
    "totalResults": 1
}

As you can see, I've used the DF function to map a number of fields to their display values. The two address-related values are of particular interest. They're the full addresses, with the individual components separated by carriage return and newline values.

When DF Isn't Enough

It's worth pointing out that the DF function isn't a "silver bullet." You'll often find that there are values from a related table that you need, and you'll need to join to that table to get to it.

For example, suppose that we want the name and email address of the employee that created a transaction. We can get the name using the DF function, but to get the email address, we'll need to join to the employee record. Here's a query to do that.

SELECT
	Transaction.CreatedBy,
	Employee.FirstName,
	Employee.LastName,
	Employee.Email
FROM
	Transaction
	INNER JOIN Employee ON
		( Employee.ID = Transaction.CreatedBy )
WHERE
	Transaction.ID = 271017

Here's the response.

{
    "links": [
        {
            "rel": "self",
            "href": "https:\/\/ns-attachments.r2021.a0729.suitetalk.api.netsuite.com\/services\/rest\/query\/v1\/suiteql?limit=1000&offset=0"
        }
    ],
    "count": 1,
    "hasMore": false,
    "items": [
        {
            "links": [],
            "createdby": "4",
            "email": "timdietrich@me.com",
            "firstname": "Timothy",
            "lastname": "Dietrich"
        }
    ],
    "offset": 0,
    "totalResults": 1
}

Again, in cases where you need additional data from the related table, a SQL JOIN will be required.

Other Built-In Functions

I've focused on the DF function because I've found that to be the most helpful of the SuiteQL built-in functions. But there are other functions available that you might find helpful, including the CONSOLIDATE function (converts a currency amount stored in a field to a target currency), the CURRENCY_CONVERT function (converts a currency amount stored in a field to a target currency using the exchange rate that was in effect on a specific date), CURRENCY (displays a field value as a currency amount), and the CF function (sets the field usage context to CRITERIA).

There are also a few additional built-in functions coming in NetSuite 2020 Release 2, including HIERARCHY (which will return the hierarchical path of a value), NAMED_GROUP (which will return filter options for things like department, class, location, etc), PERIOD, and RELATIVE_RANGES (which will return calendar ranges for date fields - such as Last Fiscal Year, Current Week, etc).

Wrapping Up

In this post, I've shown how you can use the SuiteQL DF built-in function to get the display values for a number of columns in the Transaction table. But it's worth noting that the function can be used to map values in other tables as well. I encourage you to explore the DF function, and the other built-in functions as well. I think you'll find that in many cases, these functions will save you time and effort as you build your SuiteQL queries.