How to show results of a record only if another record type doesn’t exist?
I’m trying to do a saved search on some project profitability information, I want to show the revenue of either the Sales Order OR Invoice by project ID but I’m having a hard time with the criteria of “if an invoice exists as a transaction on a project, then show the sum of all invoice revenue, if not show the sum of all sales order revenue”. I know how to get the revenue and all of the other information, I’m just struggling on how to filter it.
I think what I’m missing is the “by project id” part in the formulas I’ve tried so far…I’m just not sure how to include that.
I’m also doing this as a Transaction saved search, should I be doing it as a Project search?
I am a rookie but since no one is helping I will try and I have not used projects yet in NetSuite.
For saved searches it seams you should start at the parent or the one who has one arms length to all data.
I would start at the project level in saved serach and join to the transactions with types SO and INV. Group by trans type and show sum of trans amount.
I would feel much more comfortable using a SuiteQL search through N/query but maybe because I don’t have enough Saved Search experience yet.