Dynamic 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.

Dynamic Selection Lists

  • Comments 28
  • Likes

Below was tested on Sage CRM v6.0d

Wouldn't it be nice to be able to have one selection list's options filtered by the current value of another selection list. For example, Software Vendor and Software Product, when I select a vendor from one list I only want to see the products in another list made by that vendor.

I have to admit the original example came from a very clever colleague of mine in Dublin. However it has not been updated for a while, so I have dusted it off and put some v6.0d polish on it and now it is as good as new.

This example adds 2 new selection columns to the case table, and makes the second one dependant on the first.

The worked example below use 2 selection fields on the case table:

case_vendor (this is the parent field)

case_vendorproduct (this is the child field)

In order for this to work, the caption codes used for the selection's list items are important. For the parent field, the Code should only be ONE CHARACTER long. For example:

Code Caption

1 Microsoft
2 Sage
3 Other


For the child field, make sure the “child item's” caption code starts with the character the parent starts with. For example, Office 2000 “belongs to” Microsoft, this is represented by the "1" of 101. When Microsoft is selected in the first list the items 101;102;103 will be added to the case_VendorProduct selection list.

Child Field (case_vendorproduct)

Code Caption

101 Office 2000
102 Office XP
103 Microsoft SQL Server
201 CRM
202 Financials
203 Commercials
302 Crystal Reports Xi
303 Adobe Reader


You will then need to update the screen’s Custom Content and the parent field’s OnChange Script. See the worked example below.

*************************************************

************WORKED EXAMPLE***********************

*************************************************

1. Create a new selection field on Case table, called case_vendor

Add the following to the selection list.

Parent Field (case_vendor)

Code Caption

1 Microsoft
2 Sage
3 Other

2. Create a new selection field on Case table, called case_vendorproduct

Add the following to the selection list.

Child Field (case_vendorproduct)

Code Caption

101 Office 2000
102 Office XP
103 Microsoft SQL Server
201 CRM
202 Financials
203 Commercials
302 Crystal Reports Xi
303 Adobe Reader

3. Add both columns to the Case Detail Screen (CaseDetailBox)

4. Copy the following code into the screen’s Custom Content script (hint: update the following the two variables with your parent and child field names if different: strParentFieldName; strChildFieldName.)

<script>
// see readme.txt for selection list naming convention
// update strParentFieldName & strChildFieldName to you field names
// add to custom content
// add the following code to your parent field's OnChangeScript

// ParentOnChange();

var strParentFieldName = "case_vendor";
var strChildFieldName = "case_vendorproduct";
var oParent;
var oChild;
var blnParentPopulated = false;
var ChildOptions=new Array();
var de;
var o;
var strKey='';

window.attachEvent("onload",customContentOnLoad);

function ChildOption(v,t,k)
{
this.value=v;
this.text=t;
this.key=k;
}

function customContentOnLoad ()
{
if ( document.all(strChildFieldName) != "undefined" && document.all(strChildFieldName) != null )
{
// we are in edit mode
// get objects and selected options
oChild = document.all(strChildFieldName);
var childSelected = oChild.options[oChild.selectedIndex];
var blnChildPopulated = true;
if (childSelected.text == "--None--")
blnChildPopulated = false;
// iterate through children creating collection
var i = 0;
for(i=0; i < oChild.options.length; i++)
{
o=oChild.options[i];
strKey=o.value.substring(0,1);
if (o.text != "--None--")
ChildOptions[i]=new ChildOption(o.value,o.text,strKey);
}
ClearDownChildrenAndBuild(blnChildPopulated, childSelected );
}
}

function ClearDownChildrenAndBuild( blnChildPopulated, childSelected)
{
// see if parent has a value
oParent = document.all(strParentFieldName);
var parentSelected = oParent.options[oParent.selectedIndex];
blnParentPopulated = true;
if (parentSelected.text == "--None--")
blnParentPopulated = false;

// clear down children
while(oChild.options.length>0)
{
oChild.options[0]=null;
}

// rebuild children if there is a parent value
if (blnParentPopulated)
{
for(i=0;i<ChildOptions.length;i++)
{
if(ChildOptions[i].key==parentSelected.value)
{
o=document.createElement('OPTION');
oChild.add(o);
o.value=ChildOptions[i].value;
o.text=ChildOptions[i].text;
if (blnChildPopulated)
{
if(o.value == childSelected.value && o.text == childSelected.text)
o.selected=true;
}
}
}
}

// rebuild none option and select if no child
o=document.createElement("OPTION");
oChild.add(o);
o.value='';
o.text='--None--';
// select none if there was no child
if (!blnChildPopulated)
o.selected=true;
}

function ParentOnChange()
{
ClearDownChildrenAndBuild(false, null);
}

</script>

5. Update your Parent Field’s onchangescript with the following line of code:

ParentOnChange();

To test it open a new case. Note when you select a value from case_vendor, the available values in case_vendorproduct change.

Comments
  • Is it possible to use this with more than 9 vendor choices?

  • This is a vey VERY useful piece of code. Thank You.

    As Welther asked, Is it possible to use this with more than 9 "parent" choices?

  • I've had to use this code, but modifiedit to allow for more than 9 parent choices, even more than 26 if you used alpha characters as the first char in the child selection list's code.

    With the below change, you can use an unlimited number of parent and child choices, if you create your selection list choices as follows;

    Parent

    ---------

    Start at 1, I have tested up to 9999

    Child

    --------

    Depending on the number of child elements you anticipate start with 1001 or 10001. 1001 will allow for up to 999 child options, 10001 will allow for up to 9999.

    The only change I made to the existing code was to check whether the length of the child option was equal to 4 in which the first character of the child option is selected for the parent key, otherwise a formula determines how many characters to select for the parent key.

    // iterate through children creating collection

           var i = 0;

           for(i=0; i < oChild.options.length; i++)

           {

               o=oChild.options[i];

               if (o.value.length == 4)

               {

                   strKey=o.value.substring(0,1);

               }

               else //for key lengths longer than 4, apply below floor formula to get no of digits to extract from key

               {

                   var strKeyLength = Math.floor(o.value.length /2); // min 3 digits for the child key, parent key always has fewer digits...

                   strKey=o.value.substring(0,strKeyLength);         //...so round that down

               }

  • Is it possible to add one more child field like 'Sub Product'..?

  • Have you seen this article?  community.sagecrm.com/.../use-advanced-search-selects-to-created-linked-selection-lists.aspx

    By using Search Select Advanced fields you can get very complex nested relationships built.

  • A very helpful post, even though it's quite old.

  • 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

  • Frank

    I've just tried pasting the code into the Custom Content box of the casedetailbox screen in Sage CRM v71sp2 and it goes in without an issue.  The field in the database holds the long text happily.  I don't know why you can't paste more than nine lines of the code.

  • Solved.  I was working remotely from home over the weekend.  The code works now that I am in the office.

  • Yay!

  • I used this article on dynamic selection lists, this article on managing client side code - community.sagecrm.com/.../managing-client-side-code-in-sage-crm.aspx

    and this article on getting stuff client side - community.sagecrm.com/.../getting-server-side-stuff-client-side.aspx

    to simplify the implementation of dynamic selection lists and also to easily get them to work on workflow screens.

  • Excellent!  Thanks for letting me know.

  • Hi Lexnet CRM,

    I am trying to get this to work on workflow screens, can you post up how you got this working.

    Thanks

  • Solved my problem with this simple solution - community.sagecrm.com/.../adding-clientside-code-into-dynamically-built-workflow-action-screens.aspx

  • Rich - Thanks for sharing.