How to filter an Interactive Dashboard List Gadget by non-text field e.g. Security Territory

1 minute read time.

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

  1. 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
  2. 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
  3. I created a new Report (within the Sales category) that included the new oppo_terrcaption field in the output.
  4. 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.
Parents
  • Hi Jeff:  I am assuming this works the same way for a filter box?  I have an external table for Regions that contains the Record Number and a Description.  I am not able to filter on the Record Number field.  

    Based on this post, it appears that only text and not integers can be searched?  Am I understanding that correctly?

    I will probably drop the integer search but just wanted to confirm that the search is not working for me because it is an integer and not text.  (If I decide to keep it, I will create an external view and cast the integer as text.  I will then link the view to Sage CRM.  I think that will work??  There are 107 values in the table.  :-))

Comment
  • Hi Jeff:  I am assuming this works the same way for a filter box?  I have an external table for Regions that contains the Record Number and a Description.  I am not able to filter on the Record Number field.  

    Based on this post, it appears that only text and not integers can be searched?  Am I understanding that correctly?

    I will probably drop the integer search but just wanted to confirm that the search is not working for me because it is an integer and not text.  (If I decide to keep it, I will create an external view and cast the integer as text.  I will then link the view to Sage CRM.  I think that will work??  There are 107 values in the table.  :-))

Children