Summary saved search: Criteria sum formula `case w...
# administration
k
Summary saved search: Criteria sum formula
case when {transaction.type} = 'Opportunity' then 1 else 0 end
goal of zero
c
@KevinJ of Kansas - thanks much for the reminder. I really was having a slow day mentally yesterday and this clue got me 90% of where I needed. I hate to ask this but I'm at my wits end trying to finish this sucker off: I built a transaction search using the above criteria, and it does a great job of returning the counts of projects with at least one transaction. So far, I have counts for Opportunities, Estimates, Sales Orders, and Invoices. Problem is that it will not return a project that does not have any transactions at all, and I need to get those too. Any ideas how to make the search return all zeros across the board?
k
Can you share rest of search setup?
You might be stuck doing this in two searches
But maybe add an "or" count transaction.internal ID is zero
c
Hi Kevin, missed this late yesterday. Here's the criteria for the transaction search:
With the results baking in the counts:
Which gives this result:
Thing is, I know there are 130 projects in this status, and by hand checks, I've found that 12 have no transactions at all...should be zeros across the board.
k
Turn on use expressions change your first summary criteria to say "or" at the end - add another SQL formula count
case when transaction.internald is null then 1 else 0 end
with a goal of 1.
this might not work - you might be forced to use two searches.
maybe you could edit the first formula -
case when transaction.type = 'opportunity' then 1 when transaction.type is null then 0 else 0
Either way - I'm pretty confident this would have to be two separate searches. "Projects not yet transacted against" and "Projects with No Opportunity assigned"
👍 1
c
Thanks Kevin. As you suspected, no joy. Sounds like a two search deal is in my future.
k
There is a tool out there called alterview that allows stiching of two saved searches together.
Fairly cheap, and comes with a full refund trial period if you don't like it.
That said, I had some serious troubles getting a hold of their staff because they were acquired a while back and haven't updated their website at the time.
So, not sure if that's been fixed since.
c
Cool, thanks -- looking for it now!
I think this is the one that does it.
c
Awesome