A customer had a need to filter a Dashboard List Gadget by Security Territory.
In this example I shall use the idea of filtering a list of Opportunities by Territories.
The Oppo_Secterr field holds the data that indicates to territory but this field is a numeric field and List Gadgets can only be filtered by strings.
See: https://help.sagecrm.com/on_premise/en/2022R2/Main%20Menu/Content/User/IB_GadgetList.htm
So, we need to cheat. We can do this by using a view that joins the information draw from the Opportunity table to the Territories table that contains the Caption we would want to use.
You can see in the image below I have done this.
I am able to filter a list of opportunities by Territory Caption.
I was able to able to do this by carrying out the following actions
- I created a new view based on vListOpportunity called vListOpportunityExtra and joined opportunity table to the territories table and included the field with an alias. (Territories.Terr_Caption AS oppo_terrCaption) I have discussed creating new views here:
https://www.sagecity.com/sage-global-solutions/sage-crm/b/sage-crm-hints-tips-and-tricks/posts/how-to-create-a-new-view-then-build-a-report-using-that-view-which-is-then-in-turn-used-as-the-basis-of-a-list-gadget-in-an-interactive-dashboard - Because I wanted the new field to look and feel part of the Opportunity table I used the Derived Fields Utility to add the information into the meta data tables. For example I labelled the field "Territory Caption". I have written about the Derived Field Utility here: https://www.sagecity.com/sage-global-solutions/sage-crm/b/sage-crm-hints-tips-and-tricks/posts/adding-derived-fields-in-views-into-meta-data
- I created a new Report (within the Sales category) that included the new oppo_terrcaption field in the output.
- I then was able to create the list gadget for the Opportunity entity based on the new report. The oppo_terrcaption field (e.g. Territory Caption) is now able to be used to filter the gadget.