Hi,
I am trying to create a search using system notes fields and I'm wondering if it's possible to get a bit of help! We have a business process which relies on our sales opportunities moving through the Status (Entity Status) in fairly strict order. When users skip these stages, it presents significant risks to us as a business.
An opportunity absolutely must go through our "Procurement Review" Status before it moves to any of the Proposal Accepted, Proposal Issued or Order Received Statuses. I am trying to create a report that shows all opportunities that have reached any of these 3 statuses, but have skipped the "Procurement Review" Status.
I started by adding a criteria to return opps with only these statuses, which was simple and the search returns 205 results, which is what I would expect:
In order to monitor whether what I was building was working, I added fields to the results as follows:
The search then returned 12933 results because it shows a separate line for every system notes change, there are obviously an average of 63 system notes changes per opportunity! I only want to see changes relating to the entity status, so I added an available filter to this in order to limit changes to just that field.
I can now set this field to show me changes relating only to entity status, and the search only returns 682 results (avg of 3 to 4 changes to entity status per opp). I want to exclude all of the opportunity records that have been through the procurement review status, so with all the best intentions I add the following line to the criteria:
Search now returns 614 results and I think - great, that's now excluded all opps that have been through procurement review, and is only showing opps that have not been through procurement review:
All I need to do is work out how to remove the duplicate lines and then I'll have my report. But - on further investigation, I realise that it is actually still returning opportunities that HAVE been through procurement review status, it is just excluding the system notes change line from the search results. SO - if I have an opp that has had 6 Entity Status changes as follows:
...and one of them is procurement review, it shows the opp 5 times on the search, and just excludes the line where the Entity Status change New value was procurement review:
This is of course not what I want.
Do you have any suggestions on how to build a search that does what I need? I have other un-skippable opportunity stages I want to do this on, but I'm hoping if you can help me to get it working on this one, I can build the other ones up based on some guiding principles.
Many Thanks in advance
Chris