This topic contains 7 replies, has 0 voices, and was last updated by jmcadams 8 years, 2 months ago.

  • Author
    Posts
  • #5154

    jmcadams

    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.

  • #5155

    Voltron

    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.

  • #5156

    david.smith

    User group on your invoice instead of sum

  • #5157

    jmcadams

    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…

  • #5158

    jmcadams

    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.

  • #5159

    jmcadams

    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

  • #5160

    david.smith

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

  • #5161

    jmcadams

    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)

You must be logged in to reply to this topic.