[KCSF Bud]

I've often faced this problem but don't think ever found a good solution. Let's say I need an item search that also brings in information about open purchase orders related to those items. As soon as I add the joined field, of course it only brings back rows for items that have open purchase orders. How can I keep a row for every item while also bringing in information about the PO? In other words, I want the base list to include items that have no related purchase orders.
This is a cached copy. Click here to see the original post.

[pcutler]

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

[KCSF Bud]

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.

[pcutler]

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

[KCSF Bud]

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.

[JCirocco]

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…

---

pcutler replied on 10/09/2017, 10:45 AM: A bit of a hack, but that would definitely work!

[JCirocco]

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)

[pcutler]

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.

---

KCSF Bud replied on 10/09/2017, 10:52 AM: Makes sense. Was hesitant to do it this way because it seems like it would attempt to pull every item-related transaction in the account? Think that would be significantly slower or faster than JCirocco’s method? Again, I’m no expert on how SQL actually gathers the data so maybe it’d be no big deal (something like 60,000 items).

[KCSF Bud]

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.

[pcutler]

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

1. Suitescript

2. SuiteConnect and writing the query via SQL

[JCirocco]

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

[Author]

Posts