I have extensive background in report writing, SQL, and data analysis. I am very new to Netsuite. I am trying create a couple saved searches. I've tried starting what a Transaction search and Customer search. I ultimately want the Top Level Parent and all outstanding transactions (ex: amount remaining != 0).
So:
TopLevelParent.Name (Group)
Sum( 0 < transaction.duedate <= 30 )
Sum( 30 < transaction.duedate <= 30 )
Sum(60 < transaction.duedate <= 30 )
Sum( transaction.duedate > 90 )
I'm running into an issue of the Sums being inflated by a multiple of the number of records in the group. If TopLevelParent "A" has 4 outstanding transactions, the total of each sum column is 4x what it should be. If there are 3 outstanding transactions the total of each sum column is 3x what it should be. Now I know this has to be a join issue. However, I am not familiar with how the Netsuite tables look behind the scenes. I'm used to working in SQL where I can see what's going what. Trying to do this through a GUI is going to be my demise.
How do the transactions and customer tables relate to each other? The Oracle site has a lot of diagrams, but I can't find one for this specific relationship. I'm going to attach the Customers search that is causing me issues.
Criteria:
Results:
I have tried to find documentation on the schema and/or someone that has explained this issue. I have tried creating the search as both a Transaction search and a Customer search. I end up with the same results. I tried dumping all the fields of the Customer records and Transaction records on the search results to see if I could deduce the structure by looking for data that doesn't match.
There is a possible join issue where you are including Address details in the results. The Top Level Parent could have multiple addresses, so each transaction will be repeated for each address. You can verify this if you look at ungrouped results of your search.
To address this, you could change the results to look at the transaction address, or add a filter to limit results to a specific address for each customer - eg: (default) Billing Address or Shipping Address, whichever is more appropriate for your use case. Note that if you limit results by default address type, any customers with no default set will be filtered out. (If you run into this issue, it may be possible to mitigate it using a Summary Criteria formula, but that's a bit beyond the scope of what I'll attempt on this answer.)
There is schema documentation (of sorts) under the Records Browser at: https://system.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2023_1/script/record/account.html. It's not a DB schema as you probably mean, but it might be of some use.