Use Advanced Search Selects to create linked selection lists

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Use Advanced Search Selects to create linked selection lists

  • Comments 16
  • Likes

Many teams need to link selection lists so that the contents of one selection list determine the contents of another.  For example Support teams love the ability when creating a case, to fill in a value for a type of support issue, and then narrow down the issue with a second set of options, and perhaps even narrow down the issue further.

I was amazed when Jack told me this feature was available in Sage CRM, so I just had to blog about it.

What is the feature?

 On a case summary screen I have added three new fields, Area Select, Category, and Issue:

The field are related to each other.

First of all the Area Selects that I have entered are hardware, software and warranty.  In this example let us say the customer is complaining about some software, and so the user picks software.

Once the user picks software, the Category selection will fill up with different types of software.   In this example the software options are Mac, Microsoft and Red Hat. The user asks the customer which software they have a problem with.    

Let us say the customer is complaining about Microsoft software.  The user selects Microsoft.   Now the Issue selection fills with different Microsoft versions.   In this example the options are Vista, Windows 95 and Windows NT.  The user can pick a version.

 The result is that the case now identifies the exact area of the problem. 

 This example can obviously be reused in many different ways, and can be customised to suit each customer's needs.

How to do it?

Note:

1) Setup of these fields requires no coding. There is some advanced configuration needed, and so you must try it out first on a trial install.

2) You must be familiar with the "Main Entity Wizard" of the Sage CRM component manager.

3) I recommend that you follow my instructions to the letter when doing your first attempt, and then do it again yourself with your own customisations once you know what you are doing. Implementation takes about one hour.

 First I'll show you how to do the Area Select and Category selections.

Step 1)

Using the "Main Entity Wizard" of the component manager create new entities for Area and Category.  (It is assumed that you already know about the "Main Entity Wizard" and the component manager).

Navigate to Administration-> customisation-Component manager, and start up the Sage CRM component manager. 

 Highlight advanced customisation wizard and click install component.

Create a new Area entity: 

 Put in values for Entity name "Area", and entity column prefix "area" as shown.  Click Install component and it will finish with the message below. 

Now do the same for Category:  

 Make the entity name "Category" and the entity column prefix "cate".

Step 2)

Edit Category so that it links to Area.

Navigate to Administration -> customisation -> category -> Fields

Create a new field of type search select advanced:

Make the entry type "Search Select Advanced", column name "cate_areaid", caption "Area", and Search entity "Area".

Note: This bit is the trickiest part of this project.  You must get the column name exactly right or it will not work.  You need to take the name of the selection you are linking to, and add id.  The format of the name is <column prefix>_<entity you are linking to>id

Add this new field to the category screen.  Navigate to Administration -> customisation -> category -> Screens

Edit Category new entry screen.

Add the field "Area", as the first field like so:  

Step 3)

Add some data to the Area and Category entities. 

Note By default non admin users will not see the new -> area, and new -> category buttons unless security permissions are changed.  That is probably a good thing.

Navigate to Main Menu-> New -> Area

Add the following values: "Hardware", "Software", "Warranty".

Now add values to category linked with area. Navigate to Main Menu-> New -> Category

In the Area field pick "Hardware", and for Name pick "Toshiba", and Save.

Now add more hardware values "Sony" and "Samsung".

Now add some new software values.  Create a new category, and select the Area as "Software".  Add values for "Microsoft", "Mac" and "Red Hat".

Step 4)

Add the field to the case summary screen.

Navigate to Administration -> Customisation -> Cases -> Fields

Add a new search select advanced: 

Entry type is "Search Select Advanced", column name is "case_area", caption is "Area Select", search entity is "Area".

Note:  The reason why the caption is "Area Select" and not "Area" is because there is already an "Area" caption on the case screen.

Add a second new search select advanced: 

Entry type is "Search Select Advanced", column name is "case_category", caption is "Category", search entity is "Category", and Restrictor Field is "Area Select".

Navigate to Administration -> Customisation -> Cases -> Screens

Edit CaseDetailBox

Add the Area Select and Category fields like so: 

Now navigate to a case, and edit it, and you will see the Area Select and Category fields working.  When you select an Area the Category field is filled in with the correct values.  If you clear both field and select a Category it will fill in the Area for you.  This also works in the new case screen.

Step 5)

Finally repeat the above steps for the Issue entity.  You will end up with a three tier selection as described in the introduction.

 

Comments
  • I hope that a better solution is developed in the future.  This is a request I have quite often from clients and this solution is not something a client could easily do.

  • Hi

    Is there a way of getting the lists to populate in a way that if i was to select an option in the 3rd list the first two lists would get auto-populated eg select an option from the issue list and the catagory and area are auto-populated, at the moment i can only get it to work so that when i select an issue only one of the other fields are auto-populated.

    Many Thanks

  • Hi Andrew,  Can this be used for a multi-select field?

    Regards,

    Penny Vaskess

  • Hi,

    In answer to some of the comments that have been made - I think this works only for fields of type advanced search select, not for multi select fields. I do not know of a way that when you select an option in the 3rd list that it back populates the previous two lists.  

    Andrew

  • @shargreaves Radically reducing the effort required by the end user to customize the product and codeless customization in general is a hot topic at Sage CRM right now. It is at the top of our agenda for upcoming releases and roadmaps and as such the kind of customizations described in Andrew's blog are absolutely the kind of complex customizations that we want end users to be able to accomplish to provide in a much more straightforward and accessible manner.

    As such - I have logged an Enhancement Request in our internal bug/enhancement tracking system to cover this specific customization issue. It will be addressed as part of our codeless customization/ease of customization ambitions for the product in an upcoming release.

    Thanks for the great feedback,

    Frank Kennedy (Business Analyst, Sage CRM)

  • Hi,

    I managed to successfully implement 2 linked lists by the above instructions, thanks.

    I will try a 3rd level, but my question is: how do you edit the values in the Advanced Search Select tables once you have created them?  I cannot seem to find them using the Administrator back end.

    Can anyone help?

    Thanks.

  • @andybkk

    Good question.  I had not put much thought into that use case.

    Are you trying to locate the values by clicking Administration  and then going to either the translations area or the fields area under customizations?  They will not be there because the values in the search selects are data entries in database tables.  

    The values for Area will be in a new database table in SageCRM called Area, and the values of Category will be in a new database table in SageCRM called Category.

    The way the demo works the data is fairly static, but it is possible to make some changes if you are prepared to get your hands dirty.

    Fistly to add new values go to the main menu in CRM and click New -> Area, or New -> Category, and so on as described in the original posting.  That is straightforward.

    To Delete values. This is more tricky.  The way I did it was by going into the database and finding the datarow entry you want to remove and marking the deleted field equal to 1.  For example if you wish to delete the Area value of "Software".  Go into the SageCRM database, open up the Area table for editing, find the Area row, and change the field for area_deleted to 1 from null. that will hide the value of Software, and all the values that are linked to it under Catergory.   (Note that if you have selected "software" as an area on any of your cases, it will still say that value, which would be the expected behaviour.)

    By adding new values and deleting unwanted values you should be able to change the relationships to suit your needs.  

    I hope that helps.

  • Andrew,

    I've followed these directions exactly and poured over them again and again.  In my use case, I have Topic and Subtopic.  The controls work well independently, properly selecting the available data, but when I try the restrictor field, it does not filter the subtopics properly.  

    When I look at the SQL code, it is not filtering the subtopics by topic.  The where clause evidences this.

    SELECT subt_SubtopicID, subt_Name,subt_SubtopicID FROM Subtopic WHERE (subt_Name LIKE N'%' ESCAPE '|' OR COALESCE(subt_Name, N'') = N'') and COALESCE(subt_SubtopicID, 0) = 6001 and subt_Deleted is Null order by subt_Name

    Note it should be filtering the Subtopics by subt_TopicID.  Any ideas or know bugs?

    Thanks, Chris P.

  • Hi Andrew

    I've just encountered exactly the same problem that Chris has reported.

    It seems that the 2nd entity is being filtered by the ID of the first on its own ID instead of the parent category ID.

    Did you come up with any answers on this??

    Thanks

    Roger

  • Hi,

    I've seen two comments from people with problems reproducing this exercise, so I went and ran through the steps again myself on a fresh installation of SageCRM 7.1c.    It worked again for me.  

    I guess I may be sub consciously doing an additional step that I have not outlined, but I don't think so.  

    Could I suggest that you make a test copy of your database and run through MY steps exactly line by line without changing the names of anything to see if you can get that working? Then once that is working you can try changing the fields to ones that you would like.    All the steps need to be exactly followed to get the feature to work.  Note that there is a piece in step 2 that is tricky, which is getting the code name for the advanced search select exactly right.

    If you can't get it working by following my steps, post up some information on what you are seeing.  I now have a fresh copy of the demo working on a virtual machine so I can compare your results with my ones.

  • Hi Andrew

    At long last I've been able to test as requested. And as expected it works perfectly!?

    I've run through my scenario again and it still fails?

    FYI. My new entities are SIC (prefix sic) and SIC_SubCategory (prefix sics). In the sub category table I have added the Adv Search Select field sics_sicid with the Search Entity of SIC.

    When I add the 2 new fields to the Company table I've set the Restrictor on the Sub Category to SIC

    But when I select an SIC code it will only populate the Sub Category with the record that matches the ID of the SIC Code?

    Is it because my tables and prefixes are so similar??

    Any assistance would be appreciated.

    Thanks

    Roger

  • Okay, I have given it a try.  And I am stumped.

    This looked like it could be used to populate Canadian Provinces when Country = Canada.  Not wanting to break anything, I created a new address field (addr_province) and modified the country codes.  addr_province was added to the Address Entry Screen. My problem is when I try to paste the script into the custom content box, it only accepts the first 9 lines of the code.  What have I missed here?

    addr_country

    1 Canada

    2 Mexico

    3 United States

    addr_province

    101 AB

    102 BC

    103 MB

    ...

    201 CH

    211 DG

    ...

    305 AZ

    306 CA

    310 FL

    Frank

  • Hi,  addr_country is a selection, not a search select advanced, and I didn't put anything into the custom content box as far as I can remember.  So I think you are trying to do something different to what i was doing and it wouldn't necessarily do the same thing.  I was able to add as many lines as I wanted to the address entry screen custom content though.

  • I successfully used this guide to create my SIC Code 3 tiered selection lists - community.sagecrm.com/.../11647.aspx

    As Rehman already asked, it is only auto selecting two fields if the user directly selects the 3rd field, not the 2nd, is there a way to link the 3rd field to the 1st so that it auto selects the 1st and 2nd option if user selects from the 3rd list?:

    Rehman

    12 Oct 2010 10:17 AM

    Hi

    Is there a way of getting the lists to populate in a way that if i was to select an option in the 3rd list the first two lists would get auto-populated eg select an option from the issue list and the catagory and area are auto-populated, at the moment i can only get it to work so that when i select an issue only one of the other fields are auto-populated.

    Many Thanks

  • Hi

    I've used this for a 4 tier linked selection and it works brilliant with one major exception...

    Just like lynwen has commented, users do not always do A then B then C then D (which works perfectly). If the user uses the find on D, C gets populated correctly but A and B are left blank.

    Is there a way to cascade the results up the tree????