NetSuite Saved Search: Showing line items that match a child item but also show line items that don't have a child?

Question

I am trying to show a specific field on a child record, but I also don't want to exclude line items that don't have a child record, I am unsure how to achieve this... This is what I had tried (and wouldn't work on my Transaction saved search):

• for my criteria (where the formula should equal 1):

CASE {custrecord.item} WHEN {item} THEN 1 WHEN NULL THEN 1 ELSE 0 END

• for my Results:

CASE {custrecord.item} WHEN NULL THEN NULL ELSE {custrecord.lplotnumber} END

Doing this excluded line items that didn't have a child, but I wanted those lines to display just blank the resulting field out.

---

Added details:

Here is an example table of what I'd like to see:

{Item}	formula for {custrecord.lplotnumber}	Theoretical {custrecord.item}
1234567		1234567 (this wouldn't actually exist)
2345678	LP1234567	2345678 (this would exist)
3456789	LP2345678	3456789 (this would exist)
4567891		4567891 (this wouldn't actually exist)

I want it to show the correct {custrecord.lplotnumber} by matching the item to the custom record item, but I don't want to exclude the lines that don't have a matching custom record item.

Here is an example table of what I'm actually seeing currently:

{Item}	{custrecord.lplotnumber}	{custrecord.item}
2345678	LP1234567	2345678 (this would exist)
3456789	LP2345678	3456789 (this would exist)

It's excluding the lines that don't have a matching custom record item. But if I don't filter my data I get repeating data on every line.

Here is an example of what happens without the filter I am looking for:

{Item}	{custrecord.lplotnumber}	{custrecord.item}
1234567		1234567 (this wouldn't actually exist)
1234567	LP1234567	2345678 (this would exist)
1234567	LP2345678	3456789 (this would exist)
1234567		4567891 (this wouldn't actually exist)
2345678		1234567 (this wouldn't actually exist)
2345678	LP1234567	2345678 (this would exist)
2345678	LP2345678	3456789 (this would exist)
2345678		4567891 (this wouldn't actually exist)
3456789		1234567 (this wouldn't actually exist)
3456789	LP1234567	2345678 (this would exist)
3456789	LP2345678	3456789 (this would exist)
3456789		4567891 (this wouldn't actually exist)
4567891		1234567 (this wouldn't actually exist)
4567891	LP1234567	2345678 (this would exist)
4567891	LP2345678	3456789 (this would exist)
4567891		4567891 (this wouldn't actually exist)

Answer 1

I think your formula is fine. Try switching Equals 1 to Does Not Equal 0.

You could also try: CASE WHEN LENGTH({custrecord.item}) > 0 AND {custrecord.item} <> {item} THEN 0 ELSE 1 END

Again, Not Equal To 0