https://netsuiteprofessionals.com logo
r

RJMNS

04/28/2022, 4:01 PM
Just to make sure I'm not missing it, there's still no concept of a Left Outer join in Saved Searches, correct? My use case is that I want to see account balances of all child customers under a parent customer. Starting with a Customer search, it's easy enough to link to transactions and summarize, but that excludes the couple subcustomers that have never had any activity. Anyone know a way around this?
k

KevinJ of Kansas

04/28/2022, 6:24 PM
Well, all "joins" are left outer joins - right up until you put a criteria on your joined to record.
once you do that it effectively becomes something like an inner join - but it's not a true inner join.
It still treats the result set as a left outer join in terms of multiplying rows - it just doesn't include any records that don't have the joined to rocords.
To get a true inner join, you have to do case statement criteria where recordA.field = recordB.field
Though, that's really only necessary if you are joining to multiple records - i.e. from the item to inventory detail and from the item to the location
n

NS Admin

04/28/2022, 8:56 PM
THIS IS REALLY GOOD INFO @KevinJ of Kansas Thanks for sharing!
r

RJMNS

04/28/2022, 9:57 PM
I guess maybe the more direct question would be if there's any way to have a Customer search containing fields from the Transaction join show all subcustomers, even if they have no transaction activity.
While still having criteria from the Transactions join
k

KevinJ of Kansas

04/29/2022, 3:09 AM
Not that I'm aware of without a 3rd party for stitching the data together
Rsm has something for that and there's a tool out there that I'm blanking on its name