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."
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.
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.
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).
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.
Hello, I’m Tim Dietrich. I design and build custom software for businesses running on NetSuite — from mobile apps and Web portals to Web APIs and integrations.
I’ve created several widely used open-source solutions for the NetSuite community, including the SuiteQL Query Tool and SuiteAPI, which help developers and businesses get more out of their systems.
I’m also the founder of SuiteStep, a NetSuite development studio focused on pushing the boundaries of what’s possible on the platform. Through SuiteStep, I deliver custom software and AI-driven solutions that make NetSuite more powerful, accessible, and future-ready.
Copyright © 2025 Tim Dietrich.