This topic contains 1 reply, has 0 voices, and was last updated by khultquist 6 years, 9 months ago.

  • Author
    Posts
  • #22617 Score: 0

    dominicb
    • Contributions: 0
    • Level 1

    I have a transaction saved search, which sums daily transaction counts, ie.

    Date: Group

    Document Number: Count

    This works fine, but excludes days when there is no result. We'd like to be able to paste a date range into Excel, but can't do so, as the days with no results are generally weekends, which obviously fall on different dates each year.

    Can anyone suggest how I can get the search to include a date and 0 when there are no results for that day?
    This is a cached copy. Click here to see the original post.

  • #22618 Score: 0

    khultquist
    • Contributions: 0
    • Level 1

    It's always tricky to get NetSuite to return search results for records that don't exist. The key is to return results in one column but not in another. For exampleCreate a placeholder transaction everyday, such as an invoice for $0 to "Misc Company". You can create these through script, csv, etc.
    Modify your existing transaction searchCriteria allows your new placeholder transaction
    ResultsDate: Group
    Document Number: Count
    Formula (Numeric): Sum — CASE WHEN {name} = 'Misc Company' THEN 0 ELSE 1 END

You must be logged in to reply to this topic.