Multi select box in MSCRM v1.2

The current controls which can be put on a CRM entity form are:
- String
- Integer
- Float
- Currency
- Date/Time
- Boolean
- Picklist
- Memo

As you can see, there is no such option like a multi select box. Even within CRM you do sometimes need this. For example: Several contacts are receiving business gifts every now and then. You do not want the enduser to fill in which gifts they received in by hand, because they might make typing mistakes. Then you can't use this field for searching. So you want to use a multi select box.

In this article I will describe a way to create a workaround for archieving this by using a combination of a picklist and a memo field.

First we will have to add the picklist and memo field to the deployment manager. We will add a picklist called CFPgifts and a memo field called CFMgifts. It probably wouldn't surprise you that the next step is to add these fields to the contact form of CRM. We will add them under eachother, the picklist on top, the Memo under. I have removed the label for the Memo field to make it look better.

Now we can fill the picklist with the values. I have added the values 'Christmas card', 'Wine', 'PDC tickets'. Now we need to copy the selected value to the memo field. This will be done by using Javascript. The properties window of a picklist field allows you to add some scripting to the onChange event. This field is very useful, but not really user friendly. First lets get the control working, later I will describe an easier way to modify the script.

We communicate with the picklist value by using:

crmForm.CFPgifts.value

The memofield is available by using:
crmForm.CFMgifts.value

That means that if we enter this script in the onChange event, that we can copy data from the picklist to the memo field:
crmForm.CFMgifts.value = crmForm.CFMgifts.value + crmForm.CFPgifts.value;

Don't forget to switch the event on with the checkbox!

Now we can extend this task by putting a separator between the values. We should also check if the new item is a new item or if it is already in the list. If we add all this to the onChange event, then that small box will not be clear anymore. It also removes the breaks you enter manually. Therefore I prefer to only call a function from this onChange event and store the real code in a separate file, although this is not support. To do this, we have to modify the edit.aspx located at: drive:/website/SFA/conts/edit.aspx. In this file there are several other javascript files linked. We add one for ourselves which will look like this:

<script language="javascript" src="/sfa/conts/PicklistEx.js"></script>


Here is the file which includes the separator and check for existing items.

// Functions needed by the multi select box in MSCRM
// Author: Ronald Lemmen
// Company: Avanade Netherlands

// function to check if an item exists and if not, add it to the item list
function checkAndAdd(item, itemlist){
if (item != ""){
if (checkItemExists(item, itemlist) == false){
itemlist = addItem(item, itemlist);
}
itemlist = clearup(itemlist);
}
return itemlist;
}

// function which adds a new item and clears up the item list
function addItem(newItem, itemlist){
return newItem + ";" + itemlist; //modified by ronald 05-11-04
}

//this function checks if the submitted item is in the list;
//this can be done by checking on ;item; ;
//an item like "wine" is not the same as "red wine";
function checkItemExists(item, itemlist){
itemlist = strReplaceAll(itemlist, " ", ""); // remove the spaces from the existing list;
item = strReplaceAll(item, " ", ""); // also remove the spaces from the new item
itemlist = ";" + itemlist + ";"; // also the first charactar without a semicolon will be recognized now.;
if (itemlist.indexOf(";" + item + ";") >= 0){
return true;
}else{
return false;
}
}

function clearup(itemlist){
itemlist = strReplaceAll(itemlist, ";;", ";"); // remove double semicolons;
if (itemlist.substring(0,1) == ";"){ //remove the first semicolon;
itemlist = itemlist.substr(1);
}
if (itemlist.substring(0,1) == " "){ //remove the first space;
itemlist = itemlist.substr(1);
}
return itemlist;
}

//function to search and replace all occurances of a string;
function strReplaceAll(str,strFind,strReplace){
var returnStr = str;
var start = returnStr.indexOf(strFind);
while (start>=0){
returnStr = returnStr.substring(0,start) + strReplace + returnStr.substring(start+strFind.length,returnStr.length);
start = returnStr.indexOf(strFind,start+strReplace.length);
}
return returnStr;
}

When this file is stored on the correct location (drive:/website/SFA/conts/PicklistEx.js), then you can modify the onChange event to:
crmForm.CFMgifts.value = checkAndAdd(crmForm.CFPgifts.value, crmForm.CFMgifts.value);
This keeps the CRM code readable and you will be able to modify this code a lot easier compared to that small box. Now you just have to publish the modifications and run an iisreset and you're done.

You might want to enhance this control. Now the users are still able to modify the memo field by hand. You could add another picklist which will remove values from the memo field and then disable the memofield upon opening of the form. This is up to you to create :)


Update: James indeed did take the time to enhance this control and to make this for CRM 3.0! See his blogpost at this address:
http://jameswilcox.ca/random/ms-crm-30-multi-select-boxes/

15 comments:

Anonymous said...

How do you disable a field and still have changes to it saved in the Crm DB?

I tried crmForm.field.disabled=true and it disabled the field, but any changes to the field through javascript were ignored.

Ronald Lemmen said...

It appears to be that memo fields are not being stored as long as they are disabled. Even not when setting the returnValue as well.

You should enable your field before submitting the page. You can do this by overruling the form its save method. Here's a sample code which should be put in the edit.aspx to achieve the goal:


//disable the memo field
crmForm.fieldname.disabled = true;

//make the form aware of our new save method
function window.onload(){
crmForm.onsave = overriddenSave;
crmForm._bUseCustomSaveEvent = true;
}

//implementation of the new save method
function overriddenSave(){
//enable the disabled field before saving the form
crmForm.fieldname.disabled = false;
//save the form.
crmForm.SubmitCrmForm(1, true, false, false, false);
}


Good luck!

Anonymous said...

Hey Ronald... Hope Amsterdam is treating you well. My mother is Dutch (from Zeist), but I'm writing you from the USA. Quick question, do you know how you would accomplish all of this (multi value field) in CRM v.3.0?

What you propose is inserting the number for the Gift and not the Gift Name. Thoughts?

Ronald Lemmen said...

Hi!

Holland is fine for me, except for the traffic jams and the rain ;)

To answer your question: CRM 3.0 does allow you to create custom entities. This would be a great example of a custom entity to be used. A name is almost default for a custom entity (it is, but you can change it), and the id would become a guid.

Hope this helps,

Ronald

Anonymous said...

Thanks Ronald...

The custom entity won't work in this case, as it won't allow the user to create a picklist that would appear on contact records. Therefore, it's a text field that then would populate both the contact record and the custom entity. It therefore would allow the user to have duplicate "Gifts" to use your example, and as it's a text field, there's no validation to see if it's being typed correctly. That being the case, the drop down and memo field seems to be the best option. I love your idea of making the memo field read-only and having two picklists for adding and removing values. I'm just not sure how that would be structured in 3.0.
Thoughts???
Thx.
Darren

Ronald Lemmen said...

Hi Darren,

I'm still wondering why a custom entity wouldn't work. If I do understand you correctly, then you would need a many to many relationship between your 'gifts' (or what it is in your case) and your contact. Since CRM -even 3.0- does not allow many to many relationships, we will have to find a workaround to create this.

This behaviour we can create by making 2 custom entities. One entity called 'Gift' and one entity called 'ContactGift'. The first entity will contain all the gifts, the second will act as connection table. You can think of the object model as a normalized database. There you would also create a connection table to set up a many to many relationship. This connection entity (ContactGift) will contain 2 one to many relationships. As you might have guessed, one to contact and one to Gift. Now you have created a many to many relationship between gift and contact.

If this is not what you want, then you can still stick with the way as described in the posting. The difference is that you can set the disabled settings in the form designer instead of javascript code. Furthermore I would not modify the edit.aspx anymore, but move that code into the form's OnLoad event.

Let me know if this is a workable solution for you.

Kind regards!

Anonymous said...

Hi Roland, i hope to visit Netherlands soon!! It seems to be a great place to have vacations!!
I'm from Mexico and i would like to go there.

I'm writing to you because i have a problem. I need to migrate invoices into CRM, but i need to use information already inside into CRM. DMF seems to allow me just migrate invoices with new data. Can you give me some ideas to accomplish this problem?

Thanks a lot, and i hope see you soon on Amsterdam's bars!! ;)

Ronald Lemmen said...

Hi anonymous,

Could you please tell me if you're using crm 1.2 or 3.0?

Ronald

Anonymous said...

Hi, Roland, i'm using crm 1.2
btw, my name is fernando, nice to meet you, hehehe.
Thanks for your atention.

Ronald Lemmen said...

Hi Fernando,

You're using the DMF for importing data right? How do you get the data into the DMF database tables? Are you using DTS for this purpose? If you do so, thats at least what I would do, then you can use the option "Lookups" in the "Transform Data Task". There you can search for existing data in the crm database.

Hope this helps,

Ronald

Greg said...

Note that the link to James Wilcox's CRM3.0 version of this code was moved. I found it at:
http://geek.jameswilcox.ca/post/63

Ronald Lemmen said...

Hi Greg,

Thanks for the updated link.
I'd also like to inform that the code that I have used James' codes and debugged these. These codes are used in the many to many relationship article on the crm team blog. See here for the article and a link to download the debugged codes:
http://blogs.msdn.com/crm/archive/2007/02/15/many-to-many-relationships-in-ms-dynamics-crm-3-0.aspx

Ronald

Divya Sekaran said...

Hi Ronald,
i had seen some code for creating own lookup feature without having relationship in mscrm but i need to have a multi select lookup in form without creating relationship through javascript.

Thankx
Divya

Ronald Lemmen said...

Hi Divya,

Your question has been answered here: http://ronaldlemmen.blogspot.com/2006/03/crm-blog-world.html. Also please use the public communities for general questions.

Thanks,
Ronald

Anonymous said...

Hi Ronald,
I need the lookup text to be captured in Plugin code without using retrieve query.The reason why i need this is I'm trying to do a generic Plugin in which i need the look up text.i.e for Contact 'A' if i give Parent Customer as 'ABC'(it may be contact or account)i need the 'ABC' value captured in my plugn code.I checked in few sites and got to know how to do that in Jscript but no idea how to capture in Plugins.