0

We currently use Salesforce to track our customers (Accounts Object) and the orders (Orders Object) they place with us.

I am trying to determine how I can create a report that would show me new customer additions by day/week/month.

The challenge that I am having is that we cannot use the Created Date on the Account because we enter accounts into SF well before they place an order with us.

Essentially the report needs to know that an order placed by an account who has not previously ordered is a new customer.

I would greatly any and all help with this!

Thank you

0

There's not a good way to do this without making a data-model change, because you can only use one cross filter per report. If you could use more than one, you'd just do an Account report with an Order cross-filter for "has Orders in this time period" and another for "does not have Orders before this time period". But sadly we can't do that.

What I'd do is create a new field, "First Order Date", on the Account. Then add a Process Builder on Order creation, with action criteria being "Account->First Order Date is null = true". Add an immediate action to Update the related Account and populate the formula TODAY() into that field.

Then your report becomes simple: it's an Account report filtered on that new Date field based on whatever time period you wish.

If you have a lot of reporting that follows this pattern (using summary information about Orders at the Account level). it may behoove you to look at a solution like Declarative Lookup Rollup Summaries.

Here's some screen shots of Process setup. Note that I don't have the field on Account that you'll use, so I picked a different standard field.

select object

build criteria select field

| improve this answer | |
  • Hi David, Thank you for following up with me on this. It's very helpful. With regards to creating a process builder, I've created a process that starts when "a record changes" on the Orders object. When I do this, it only lets me select fields from the orders object. Any suggestions on how I can complete the process builder as you've outlined? Thanks again! – mhinnen Jul 5 '18 at 18:33
  • I added some screenshots that I hope might be helpful, @mhinnen. – David Reed Jul 5 '18 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.