Sorting a CRM view by a Picklist Attribute causes an error

One of the best features of CRM is the ability to create your own views and be able to sort on any column of the view as and when you wish without having to redesign the view.

While this works great with all types of entities and attributes, we once came across with a bug when we tried to sort the view by the Status Reason Column. Status Reason column is a picklist type of attribute.

Upon further research we found that this fails for any Picklist attribute and only when one of the records present in the view has a null value set for the picklist attribute on which you are sorting the view.

To search for such null value records, you can look for the “Does not Equal” condition and select all the available picklist options.

You can just open this record and reset the value of the status reason and now the sorting on this attribute will work just fine.

Price List Lookup does not show up any Price Lists in Opportunity form

The Price List Lookup on the Opportunity form, displays the price lists for the currency selected on the Opportunity. If no price list has been created for the specified currency it would not show any price lists in the lookup.

In our case this was not the case. Here is what caused it…

We once happened to remove the Price List attribute from the Opportunity form as the customer was not going to use the Price Lists. We had plugns in place to set the Price List by default on the create event of the Opportunity.

But when we removed the Price List from the Form we got the following error

So we planned to put the field back on the form and explained to the customer that price lists would need to be provided on the form so you might as well select the appropriate price list manually from the form.

But what stunned us, is that after putting the field back on the form, the lookup no longer seems to work. It will always bring up an empty lookup window even though we have price lists created in the same currency as that of the Opportunity.


The Price List Lookup works on all other entities except this one as we had initially removed this attribute from the form and later added it back.

On further investigation we found that our adding the attribute back did not bring it to the same state as the original form provided by Microsoft. If you compare the customization file of the original Opportunity entity from MS with the one after you manually added the Price List attribute back… you would notice they are not the same. The original form has the following script that is missing when we added the attribute to the form

Script from our customized Opportunity entity:

<cell id="{2bf4485c-3d3e-444f-ac11-8a6262083d88}">
<labels>
<label description="Price List" languagecode="1033" />
</labels>
<events>
<event name="setadditionalparams" application="true" active="true">
<script><![CDATA[
var oLookup = event.srcElement;
AddTransactionCurrencyParam(oLookup);
]]></script>
<dependencies>
<dependency id="transactioncurrencyid" />
</dependencies>
</event>
</events>
<control id="pricelevelid" classid="{270BD3DB-D9AF-4782-9025-509E298DEC0A}" datafieldname="pricelevelid" />
</cell>

Script from our customized Opportunity entity:

<cell auto="false" showlabel="true" locklevel="0" rowspan="1" colspan="1" id="{2acdf564-bd2b-4392-9a93-52a133f856be}">
<labels>
<label description="Price List" languagecode="1033" />
<label description="Tarifs" languagecode="1036" />
<label description="Preisliste" languagecode="1031" />
<label description="Lista de precios" languagecode="3082" />
</labels>
<control id="pricelevelid" classid="{270BD3DB-D9AF-4782-9025-509E298DEC0A}" datafieldname="pricelevelid" disabled="false">
<<parameters>
<AutoResolve>true</AutoResolve>
</parameters>
</control>
</cell>

And this is the reason that the price list does not work after you add it back…

Resolution: Either populate this field through scripting by copying the customer pricelist to the Price List automatically(this does away with the need to lookup the price list) or replace the Original Opportunity customizations back from an unmodified CRM system.

How to disable the "Do you want to view only the webpages content that was delivered securely? popup for custom pages added to CRM

We had a custom page developed for a customer using CRM Live install. In this case we had the customer host the custom pages locally within their intranet on a IIS server. The URL of this website was added in the IFRAME on the CRM Form.

We noticed that as soon as we added the IFRAME, the customer would receive the following alert "Do you want to view only the webpages content that was delivered securely?".

This was because of the change in the security level of the pages. While CRM Live is hosted on a secure "HTTPS" protocol to provide more secure access to CRM, our custom pages uses "HTTP" protocol, so every time when CRM is accessed you get this prompt.

To remove this follow the below steps.

Go to Tools --> Internet Options --> Security Tab. Here select the proper zone under which your custom pages or the "HTTP" pages came.


Now select "Custom Level" button.

Here go to Miscellaneous --> display mixed content and select "Enable" option as shown in the below screen shot.


With this done, you will not be prompted anymore.

Reading Excel workbooks using GetOleDbSchemaTable returns a sheet with _ suffix

We have a CRM add-on for Importing Price Lists into CRM. For this tool, we expect the details to be provided in an Excel sheet. The tool reads the excel workbook provided and imports the prices.

Once we had an issue with one of our customers complaining of the data successfully being imported, yet an error message is displayed 'Sheet1$'_ not found. What was intriguing was the error could only be replicated with the sheet that are customer had provided us. If we were to create a new Excel workbook and provide it for import, we would receive no errors :( It was a classic example of "it works on my machine..." )

After researching into this issue further, we found the reason.

The tool used the following line of code to read all sheets from the workbook

dtTables = objExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

This returned 2 data tables, one with the name 'Sheet1$' and the other named 'Sheet1$'_.

the first sheet 'Sheet1$' is the normal naming convention when you read an excel workbook. But where did the second sheet 'Sheet1$'_ come from?? If you open the excel workbook, you will find only one sheet in the workbook named 'Sheet1'.

The second sheet 'Sheet1$'_ is a hidden sheet that Excel creates each time you filter the records on a sheet. It will create a hidden copy of the sheet. This is the sheet that the program tries to read through the code.

To fix this problem we added the following code to catch such sheets
if(SheetName.EndsWith("_"))
{
do nothing;
}
else
{
do operation
}

Handle emails with Unresolved email addresses

Sometimes you would notice the sender or receiver email address underlined in red with question mark next to it.

This simply means that the email address you provided in the email is either not related to any user, account, contact or lead record, or is related to more than one record. CRM looks for a unique record having the email address associated with it. When it is not able to match it with a unique record it marks it as unresolved email address.

In such cases you can manually match the email address to an existing record or create a new one.

1. To solve the problem click on the red email address (in above case "mail@test.com" )

2. This open new "Resolve Address" dialog box.

3. Here you can match the email address to the existing account, contact, lead or user by selecting "Resolve to an existing record" as shown in the above screen shot.

4. If you want to create new record then on the same page select "Resolve to new record" option, which enable you to create new record.

5. To create new record you can select account, contact or lead entity and then click on "GO" button.

6. This will open the new record of the related entity type. Here fill the necessary data and save the record.

7. This record gets added to "Resolve to" as shown in the below screen shot.

8. Now click on the "OK" button which will replace your old email address with the new or existing record and as soon as you save the email you can see that the error is gone away.