[jmcadams Contributions: 0Level 1☆]

I’m not new to Netsuite by any means so I understand it is very difficult to pull associated records into a single saved search but we need it and this one has my head spinning. Anyone willing to help me figure out what is going on?

Customer Saved Search

1) Add Criteria for July 2016 invoices.

2) Result return the invoice Amounts.

Fine

3) Add Criteria for Cases Closed in July 2016

4) Add Case Count to Results

Case Count is accurate but invoice Amount is way off.
This is a cached copy. Click here to see the original post.

[Voltron Contributions: 0Level 1☆]

Remove the grouping and see what it’s doing. My guess is it’s adding up the invoices as many cases as there are. If there is 1 invoice and 20 cases, you’ll see the invoice amount summed up 20 times. I *think* there’s a way to use DISTINCT in your formula, but not entirely sure that would solve this.

[david.smith Contributions: 0Level 1☆]

User group on your invoice instead of sum

[jmcadams Contributions: 0Level 1☆]

Remove the grouping and see what it’s doing. My guess is it’s adding up the invoices as many cases as there are. If there is 1 invoice and 20 cases, you’ll see the invoice amount summed up 20 times. I *think* there’s a way to use DISTINCT in your formula, but not entirely sure that would solve this.

I wish there we had more ‘Amount’ options to choose from (sarcasm) but I think you are right, it is adding the invoice sum for each case record returned. I’m not familiar with DISTINCT…

[jmcadams Contributions: 0Level 1☆]

Originally posted by Voltron

View Post

Remove the grouping and see what it’s doing. My guess is it’s adding up the invoices as many cases as there are. If there is 1 invoice and 20 cases, you’ll see the invoice amount summed up 20 times. I *think* there’s a way to use DISTINCT in your formula, but not entirely sure that would solve this.

This pretty much sums up why its doing this after I add cases to Criteria. I don’t know DISTINCT so I’ll have to look into that more but at least that explains why its doing this now if I can find a way around it.

[jmcadams Contributions: 0Level 1☆]

Originally posted by david.smith

View Post

User group on your invoice instead of sum

Won’t work – need the sum of the all the invoices in a single row per client. Sounds so simple!

Client – # of Closed Cases in Previous Month – Total Invoice Amount in Previous Month

[david.smith Contributions: 0Level 1☆]

May seem silly, but have you put the mainline = T so you’re not pulling line level results?

[jmcadams Contributions: 0Level 1☆]

When mainline = T the GROUPED amount value in the saved search is not accurate.

Saved Search Amount mainline = T is $5,958.49

Saved Search Amount no mainline criteria is an the accurate total (after you manually add them together)

[Author]

Posts