NetSuite: Saved Search to Show Items Purchased With Pending Receipts

I've come up with two ways to create this search. I prefer the easy way, which is already seen in the images below.

That's it. No need to read further. The hard way is just an experiment (and flex) on using nested case whens inside a listagg function. The search uses listagg so that for each transaction, there would only be one row with a compiled list of items, instead of having one transaction having multiple rows of items. The search also hides expense lines.

Add the following code in a Formula (HTML) field in the Results tab. Set the Summary Type to Maximum.

'<table width=500>' ||

listagg(distinct(

	case when {item} is not null then

		case when {applyingtransaction.type} = 'Item Receipt' then

			case when {applyingtransaction.quantity} < {quantity} then '<tr><td width=50%>' || {applyingtransaction.item} || '</td><td width=20%>' || to_number({quantity}-{applyingtransaction.quantity}) || '</td><td>' || {applyingtransaction} || '</td></tr>' end

		else '<tr><td width=50%>' || {item} || ' </td><td width=20%> ' || {quantity} || '</td><td>&nbsp;</td></tr>' end

	end

))

|| '</table>'

The criteria would be the same as in the image above. But initially I used a similar line of code. I added a Formula (Numeric) filter in the Summary Criteria using the formula below. Let the value be equal to one.

case when 

listagg(distinct(

	case when {item} is not null then

		case when {applyingtransaction.type} = 'Item Receipt' then

			case when {applyingtransaction.quantity} < {quantity} then {applyingtransaction.item} || to_number({quantity}-{applyingtransaction.quantity}) || {applyingtransaction} end

		else {item} || ' - ' || {quantity} end

	end

))

is null then 0 else 1 end

Note that if listagg doesn't work, you can use ns_concat instead. When using ns_concat you may use replace to remove commas.

Also a limitation here is that listagg/ns_concat does not compute partially received items from different item receipts. Hence, the item receipts display, if any.