Published on July 16, 2020.
Yesterday, I wrote about how I used the SuiteQL capabilities of SuiteTalk REST Web Services to implement PromoStandards-based services for a client running on NetSuite. I received several comments and questions about the post, but there was one topic in particular that several people asked about: The Status field on NetSuite's Transaction table.
When I first started experimenting with SuiteQL and running queries against the Transaction table, I found that the Status field behaved in ways that I wasn't expecting. In fact, the table itself can be a little tricky to work with. For example, when working with the table, you need to be aware that it is used to store a number of different transaction types: Credit Card Charges, Credit Card Refunds, Checks, Customer Credits, etc. Take a look at this query...
SELECT Transaction.Type FROM Transaction GROUP BY Transaction.Type
Here's an example of the result...
{
"links": [
{
"rel": "self",
"href": "https:\/\/ironforge.r2020.2a.suitetalk.api.netsuite.com\/services\/rest\/query\/v1\/suiteql?limit=1000&offset=0"
}
],
"count": 13,
"hasMore": false,
"items": [
{
"links": [],
"type": "CardChrg"
},
{
"links": [],
"type": "CardRfnd"
},
{
"links": [],
"type": "Check"
},
{
"links": [],
"type": "CustCred"
},
{
"links": [],
"type": "CustInvc"
},
{
"links": [],
"type": "Deposit"
},
{
"links": [],
"type": "Estimate"
},
{
"links": [],
"type": "ItemRcpt"
},
{
"links": [],
"type": "ItemShip"
},
{
"links": [],
"type": "Journal"
},
{
"links": [],
"type": "SalesOrd"
},
{
"links": [],
"type": "VendBill"
},
{
"links": [],
"type": "VendCred"
}
],
"offset": 0,
"totalResults": 13
}
So there are at least 13 types of transactions that are stored in the Transaction table. That's something to keep in mind when querying against the table.
If you query the Transaction table for distinct Status values, you'll see that there are at least 11 possible values, and that the values are characters. For example:
SELECT Transaction.Status FROM Transaction GROUP BY Transaction.Status
A quick comment: Notice that in that query, to get the distinct values, I'm grouping by the status field. You would think that I could use "SELECT DISTINCT Status FROM Transaction." However, DISTINCT is not supported by SuiteQL.
The result:
{
"links": [
{
"rel": "self",
"href": "https:\/\/ironforge.r2020.2a.suitetalk.api.netsuite.com\/services\/rest\/query\/v1\/suiteql?limit=1000&offset=0"
}
],
"count": 11,
"hasMore": false,
"items": [
{
"links": [],
"status": "A"
},
{
"links": [],
"status": "B"
},
{
"links": [],
"status": "C"
},
{
"links": [],
"status": "D"
},
{
"links": [],
"status": "E"
},
{
"links": [],
"status": "F"
},
{
"links": [],
"status": "G"
},
{
"links": [],
"status": "H"
},
{
"links": [],
"status": "V"
},
{
"links": [],
"status": "X"
},
{
"links": [],
"status": "Y"
}
],
"offset": 0,
"totalResults": 11
}
That's not very helpful, is it?
Thankfully, NetSuite provides a "built-in function" that you can use to map the status codes to something more meaningful. According to the NetSuite documentation, built-in functions "extend the capabilities that are provided by the SQL-92 specification." There are currently 5 built-in functions, and I encourage you to check them out. (Details can be found by searching the Help Center for "Using SuiteQL with the N/query Module in SuiteScript.")
The built-in function that we're interested in is the "DF" function. The documentation states that it "returns the display value of a field from the target record type without having to join the target record type explicitly." So we can use it to map the status codes to their names.
Here's a query that uses the built-in function:
SELECT Transaction.Status, BUILTIN.DF( Transaction.Status ) AS Status_Description, FROM Transaction GROUP BY Transaction.Status, BUILTIN.DF( Transaction.Status )
The response looks like this:
{
"links": [
{
"rel": "self",
"href": "https:\/\/ironforge.r2020.2a.suitetalk.api.netsuite.com\/services\/rest\/query\/v1\/suiteql?limit=1000&offset=0"
}
],
"count": 24,
"hasMore": false,
"items": [
{
"links": [],
"status": "A",
"status_description": "Bill : Open"
},
{
"links": [],
"status": "A",
"status_description": "Credit Memo : Open"
},
{
"links": [],
"status": "A",
"status_description": "Estimate : Open"
},
{
"links": [],
"status": "A",
"status_description": "Invoice : Open"
},
{
"links": [],
"status": "B",
"status_description": "Bill : Paid In Full"
},
{
"links": [],
"status": "B",
"status_description": "Credit Memo : Fully Applied"
},
{
"links": [],
"status": "B",
"status_description": "Estimate : Processed"
},
{
"links": [],
"status": "B",
"status_description": "Invoice : Paid In Full"
},
{
"links": [],
"status": "B",
"status_description": "Journal : Approved for Posting"
},
{
"links": [],
"status": "B",
"status_description": "Sales Order : Pending Fulfillment"
},
{
"links": [],
"status": "C",
"status_description": "Item Fulfillment : Shipped"
},
{
"links": [],
"status": "D",
"status_description": "Sales Order : Partially Fulfilled"
},
{
"links": [],
"status": "E",
"status_description": "Sales Order : Pending Billing\/Partially Fulfilled"
},
{
"links": [],
"status": "F",
"status_description": "Sales Order : Pending Billing"
},
{
"links": [],
"status": "G",
"status_description": "Sales Order : Billed"
},
{
"links": [],
"status": "H",
"status_description": "Sales Order : Closed"
},
{
"links": [],
"status": "V",
"status_description": "Check : Voided"
},
{
"links": [],
"status": "X",
"status_description": "Estimate : Expired"
},
{
"links": [],
"status": "Y",
"status_description": "Bill Credit : Undefined"
},
{
"links": [],
"status": "Y",
"status_description": "CCard Refund : Undefined"
},
{
"links": [],
"status": "Y",
"status_description": "Check : Undefined"
},
{
"links": [],
"status": "Y",
"status_description": "Credit Card : Undefined"
},
{
"links": [],
"status": "Y",
"status_description": "Deposit : Undefined"
},
{
"links": [],
"status": "Y",
"status_description": "Item Receipt : Undefined"
}
],
"offset": 0,
"totalResults": 24
}
That's much more helpful - and something you can more easily work with.
Let's put the built-in function to work. Suppose that you want to summarize sales orders by status, with a count of the orders in each status.
Here's the query:
SELECT BUILTIN.DF( Transaction.Status ) AS Status_Description, COUNT(*) AS Order_Count FROM Transaction WHERE ( Transaction.Type = 'SalesOrd' ) GROUP BY BUILTIN.DF( Transaction.Status )
And here's an example response:
{
"links": [
{
"rel": "self",
"href": "https:\/\/ironforge.r2020.2a.suitetalk.api.netsuite.com\/services\/rest\/query\/v1\/suiteql?limit=1000&offset=0"
}
],
"count": 6,
"hasMore": false,
"items": [
{
"links": [],
"order_count": "270094",
"status_description": "Sales Order : Billed"
},
{
"links": [],
"order_count": "53591",
"status_description": "Sales Order : Closed"
},
{
"links": [],
"order_count": "172",
"status_description": "Sales Order : Partially Fulfilled"
},
{
"links": [],
"order_count": "111",
"status_description": "Sales Order : Pending Billing"
},
{
"links": [],
"order_count": "12",
"status_description": "Sales Order : Pending Billing\/Partially Fulfilled"
},
{
"links": [],
"order_count": "704",
"status_description": "Sales Order : Pending Fulfillment"
}
],
"offset": 0,
"totalResults": 6
}
One key takeaway from this post is that the Transaction table is used to store a number of different transaction types, so filtering on it by type is critical. Another takeaway is that the Status field, by itself, probably isn't going to be very helpful. Make use of the NetSuite "built-in" DF function to map the codes to names to make your job a little easier.
I hope you found this post to be helpful. If you have any questions or comments, please feel free to get in touch.
Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on NetSuite, including mobile apps, Web portals, Web APIs, and more.
I'm the developer of several popular NetSuite open source solutions, including the SuiteQL Query Tool, SuiteAPI, and more.
I founded SuiteStep, a NetSuite development studio, to provide custom software and AI solutions - and continue pushing the boundaries of what's possible on the NetSuite platform.
Copyright © 2025 Tim Dietrich.