Published on May 15, 2022.

The goal of a NetSuite project that I worked on recently was to provide an alternative interface to sales order information. As you might expect, this required displaying information from the order's header and lineitems, as well as the shipping and billing addresses.

My client also wanted to be able to see any email messages that were related to an order, regardless of whether the messages were incoming or outgoing. I've written in the past about how you can use SuiteQL to monitor email. But prior to this project, I hadn't needed to access information about transaction emails.

In this post, I'll share what I discovered, as well as the SuiteQL query that I ended up creating.

The Message Table

In NetSuite, messages that are sent and received, that are related to a transaction, are stored in a table named Message. Here's a query that I wrote that returns all messages for a given Transaction ID.

SELECT
	Message.ID,
	Message.DateTime,
	Message.Subject,
	BUILTIN.DF ( Message.Author ) AS Sender,
	BUILTIN.DF( Message.Entity ) AS Company,
	Message.To,
	Message.CC,
	Message.BCC,
	CASE Message.Incoming WHEN 'F' THEN 'Outgoing' ELSE 'Incoming' END AS Direction,
	Message.Emailed,
	Message.Message,
	Message.HTMLMessage,
	Message.HasAttachment,
	Message.Attachments
FROM 
	Message 
WHERE 
	( Message.Transaction = 3019550 )

Most of the columns in the Message table are self-explanatory. However, I want to point a few things out.

The "Message" column is where the actual message body is stored, and the value can either be plaintext or HTML. You can use the "HTMLMessage" column, whose value will either be T or F, to determine if the Message is HTML or not.

The "Attachments" column is interesting. The column contains the numeric values of any files that were attached to the message, and that have been stored in the File Cabinet. At first glance, this seems very helpful. However, if you try to join to the File table on that column, you'll immediately run into problems. The values in it are strings, and from what I've been able to tell, there can be multiple file IDs stored in it for a single row. In other words, it's multi-valued, with the IDs either separated by a return character or comma. Regardless, it doesn't help if you're trying to actually join to the File table to get information about the file attachments.

The MessageFile Table

The proper way to join from a Message to its file attachments is to use a table named MessageFile. MessageFile serves as a join table, connecting records in the Message table to zero, one, or more records in the File table.

Here's a revised version of the previous query showing the additional joins, and the columns that I pulled from the File table.

SELECT
	Message.ID,
	Message.DateTime,
	Message.Subject,
	BUILTIN.DF ( Message.Author ) AS Sender,
	BUILTIN.DF( Message.Entity ) AS Company,
	Message.To,
	Message.CC,
	Message.BCC,
	CASE Message.Incoming WHEN 'F' THEN 'Outgoing' ELSE 'Incoming' END AS Direction,
	Message.Emailed,
	Message.Message,
	Message.HTMLMessage,
	Message.HasAttachment,
	File.Name,
	File.Description,
	File.FileType,
	File.URL
FROM 
	Message 
	LEFT OUTER JOIN MessageFile ON
		( MessageFile.Message = Message.ID )
	LEFT OUTER JOIN File ON
		( File.ID = MessageFile.File )
WHERE 
	( Message.Transaction = 3019550 )

A question that I get asked quite often is, "Can I use the File table to get a file's contents?" Unfortunately, as far as I know, you cannot. But you can use it to get a file's ID, and then use the ID along with the SuiteScript "N/file" module to get the contents. For more information, and an example of how to do this, see my post titled "Use SuiteScript and SuiteQL to Work With the File Cabinet."

Wrapping Up

In this post, I've introduced two SuiteQL tables - Message and MessageFile - and shown how you can use them to query NetSuite for email messages (and any attachments) related to a specific transaction.

In my case, I was querying to get messages for a specific Sales Order. However, I should point out that the queries can also be used with a variety of other transaction types, including:

• Bill
• Bill Payment
• Blanket Purchase Order
• Credit Memo
• Customer Deposit
• Invoice
• Item Fulfillment
• Opportunity
• Payment
• Purchase Order
• Quote
• Request For Quote
• Return Authorization
• Sales Order
• Vendor Return Authorization

As always, I hope you find these SuiteQL queries to be helpful.