Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have a question on where and how a filter logic should be applied to a dashboard I am developing.
The filter logic in the original report that I am replacing is a complex filter that goes something like:
1 AND 2 AND ((3 AND 4) OR (5 AND 6)) AND 7
where each number is a filter (e.g. 1 = OpportunityRecordType='Renewal' or 'Standard' or 'Partner')
I'm stuck if I should implement this in the load, as a button on the UI that applies these, as a trigger event, or what. It's been bugging me for a week now, advice would be greatly appreciated!
I would try to apply this within the script in a field which the user could simply select within the UI. A part like your 1 could be solve with a match() like:
match(OpportunityRecordType, 'Renewal', 'Standard', 'Partner')
- Marcus
Thanks for the suggestion. I considered it, but a lot of the Boolean logic takes fields from different tables. I think that'd create a Frankenstein table, wouldn't it?
yes but if the logic of the filter is complex, you have to choose between complex in script or complex in the user interface; usually, in similar situations I choose the script; if you want and can, post the logic, fields and tables
Boolean expression is as follows:
1 AND 2 AND ((3 AND 4) OR (5 AND 6)) AND 7
1. OpportunityRecordType = 'Renewal' or 'Standard' or 'Partner Deal'
2. SalesStage = '01 - UC' or '02 - VO' or '03 - QO' or '04A - DS' or '04B - PS' or '05 - NC' or '06- WDE' or '6 - WDE' or 'Lost'
3. OpportunityType = 'Renewal'
4. SalesMotion = 'New Solution'
5. Opportunity Type != 'Renewal'
6. SalesMotion = 'Care Pack' or 'Day 1' or 'Per Event' or 'New Solution' or 'Renewal' or 'Conversion' or 'Bus Type 'W''
7. PipelineExclusion != 'Y'
all of that, PLUS:
CampaignName={*NEWLead* | *NewLead* | *Employee Lead Gen* | TM;* | LOGO*}
I cannot post the entire table structure, as it's quite large, but here is a snippet with some of the corresponding tables and the fields corresponding to the above logic highlighted.
- flag1 on Opportunity table: 1 and 2 and 7
- flag2 on OpportunitItem: (3 or 4) and (5 or 6)
to create this flag you need a join to add OpportunityType in OppurtunityItem (or applymap)
I suppose the relation OpportunityItem : Opportunity is N : 1
- flag3 on Campaign
----------
flag1
match(OpportunityRecordType, 'Renewal', 'Standard', 'Partner Deal')
and match(SalesStage, '01 - UC', '02 - VO', '03 - QO', '04A - DS', '04B - PS', '05 - NC', '06- WDE', '6 - WDE', 'Lost')
and not match (PipelineExclusion, 'Y')
flag2
(OpportunityTypeBis = 'Renewal' or SalesMotion = 'New Solution')
and
(OpportunityTypeBis <> 'Renewal' or match(SalesMotion, 'Care Pack', 'Day 1', 'Per Event', 'New Solution', 'Renewal', 'Conversion', 'Bus Type W'
flag3
wildmatch(CampaignName, '*NEWLead*', '*NewLead*', '*Employee Lead Gen*', 'TM;*', 'LOGO*')
----------
in the user interface you can AND the 3 flags
another solution could be to only add one (or two) flag but I can't find the right table, maybe OpportunityItem?
Great idea. I've implemented the fields, but am having difficulty AND'ing them in the UI. I created a button and added the 3 filters as Actions, but it acts as if it is OR'ed. i.e. - It filters on Action 1 and Action 2, but if it finds no hits for Action 3, it just shows records for Action 1 and 2.
The expected behavior of an AND dictates that it should thus show no records (or at least that's the behavior I'm looking for). Any advice?