NetSuite's saved search joins almost always use left joins, so they should do exactly what you're seeking.

What filters do you have on your saved search? Do you have a filter for Transaction Fields…Type = Purchase Order? If so, then try checking USER EXPRESSIONS and replacing that with two filters:

Transaction Fields…Type = Purchase Order

OR

Transaction Fields…Internal ID (Number) IS EMPTY

This search without the PO information brings 50 rows. When I add in the PO criteria it knocks it down to 26. Need the minimum to be that 50. So its still not showing items that have no open POs? No grouping on the results tab and I am showing 1 purchase order field on the results tab (transaction type). Thanks for your help.

The criteria above will exclude items that are on other transaction types (i.e. vendor bills, sales orders), but not purchase orders.

Yes, of course. Maybe I am not explaining correctly. I want the list to be ALL items and to bring in PO info for any items (but not limited to) that have an open PO.

I know this is going to sound a little nuts but I had the same issue back in 2010. What we ended up doing was to create a "fake" $0 Invoice to a dummy customer whenever we created a new item. That way you could create your item search using the Transaction Join including both PO's and Invoices so you will always get at least 1 record returned. I then used a combination of a Decode formula (with a CASE statement) to capture the Quantity still on open PO's.

Believe it or not it actually worked…

The formula went something like:

DECODE({transaction.type}, 'Purchase Order', CASE WHEN NVL({transaction.quantityshiprecv}, 0) < {transaction.quantity}

THEN {transaction.quantity} – NVL({transaction.quantityshiprecv}, 0) ELSE 0 END)

So in that case you definitely don't want any criteria to be based on the transaction fields because that will exclude some items.

You can still have results based on transaction fields though.

To exclude info from transactions that are not POs, use a formula like this in the results (pseudocode, dont copy/paste):

Case when {transaction.type} like 'purchaseorder'

Then {some_field}

Else ''

End

To avoid getting repeated rows in your results you'll have to use grouping. Depending on exactly how you want the data, you may want to group by item and use formulas with the NS_CONCAT function to return one row per item and the PO info concatenated all on that row.

Thanks… ya I was actually considering trying that, just making sure I wasn't overlooking some more obvious solution. Might try it with a closed PO to keep the transaction joins the same type of record, then criteria would be Open POs OR this one closed PO. I'm no expert on what is best for load time of report….will fiddle around with it.

By the way, the two other options not mentioned yet are:

1. Suitescript

2. SuiteConnect and writing the query via SQL

Originally posted by KCSF Bud

View Post

Thanks… ya I was actually considering trying that, just making sure I wasn't overlooking some more obvious solution. Might try it with a closed PO to keep the transaction joins the same type of record, then criteria would be Open POs OR this one closed PO. I'm no expert on what is best for load time of report….will fiddle around with it.

You would still only include PO's and Invoices in your criteria but you would just never do anything if the Transaction.Type is not Purchase Order