I’m using SuiteQL to query and using the Records Catalog for the field id. However, I can’t locate the Amount Unbilled field in the catalog. Haven’t had an issue with locating any of the other fields. Anyone know why that field wouldn’t show in the records catalog?

I believe we calculate this on our own using linked billing transactions

Ahh so it's not a field we could query? I was confused by this because Amount Unbilled is a field on the schema browser for NS

In my experience, there are calculated fields exposed in one schema that aren't available in others (i.e. different between searches and queries)

Gotcha, makes sense. Thanks!

Wouldn't it just be more logical to put the formula to calculate in the query?

☝️

Possibly. I'm completely new to SuiteQL but can see what I can figure out. I've used {amountunbilled} for saved searches. Not sure how I'd go about it for SuiteQL

There's a column on the TransactionLine table that provides the quantity billed (QuantityBilled). In a query, you could use it like this.

SELECT TOP 1000
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.Entity ) AS Customer,
	BUILTIN.DF( Transaction.Status ) AS Status,
	SUM ( ( COALESCE( TransactionLine.Quantity, -1 ) * -1 ) * TransactionLine.Rate ) AS AmountTotal,
	SUM ( TransactionLine.QuantityBilled * TransactionLine.Rate ) AS AmountBilled,
	SUM ( ( ( COALESCE( TransactionLine.Quantity, -1 ) * -1 ) - TransactionLine.QuantityBilled ) * TransactionLine.Rate ) AS AmountUnbilled
FROM 
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
WHERE 
	( Transaction.Type = 'SalesOrd' )
	-- SalesOrd:B = Pending Fulfillment, SalesOrd:D = Partially Fulfilled
	AND ( <http://BUILTIN.CF|BUILTIN.CF>( Transaction.Status ) IN ( 'SalesOrd:B', 'SalesOrd:D') )
	AND ( TransactionLine.MainLine = 'F' )
	AND ( TransactionLine.TaxLine = 'F' )
GROUP BY
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.Entity ),
	BUILTIN.DF( Transaction.Status )