This post seeks to demonstrate that SuiteQL is not just for querying data that can’t be queried using saved searches, but even if possible to do with a saved search, SuiteQL can often be much simpler and efficient.
The Question
This question was asked by a user on the NetSuite Professionals slack channel.
Brian asks: How would you create a saved search to identify any bills paid solely with bill credits and not payments? Using a transaction search with the criteria of ‘paying transaction : none of payment’ isn’t working because a bill might have partial payments AND credits. That criteria will capture both those bills and I only want ones with ONLY credits applied. Any ideas?
The Solution
While I do think it’s possible to do this with a transaction search, I think it’s easier to do this with a SuiteQL query.
First, let’s get a list of all bills that have payments.
1
2
3
4
5
6
7
8
9
10
11
WITH paymentinvoices AS (
SELECT
DISTINCT transactionline.createdfrom AS id
FROM
transaction
INNER JOIN transactionline ON transactionline.transaction = transaction.id
WHERE
transaction.type = 'CustPymt'
)
Then let’s filter our query for invoices that didn’t match the above query.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH paymentinvoices AS (
SELECT
DISTINCT transactionline.createdfrom AS id
FROM
transaction
INNER JOIN transactionline ON transactionline.transaction = transaction.id
WHERE
transaction.type = 'CustPymt'
)
SELECT
transaction.tranid
FROM
transaction
WHERE
transaction.type = 'CustInvc'
AND transaction.id NOT IN (
SELECT
id
FROM
paymentinvoices
)
This will give us a list of all invoices that don’t have payments.
I hope this helps!