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.

How to create a new User Role in CRM that actually works :)

Its always recommended that we use the user roles already provided by CRM and edit them as per our needs.

Well instead of taking the easy way out, we decided to go ahead and create a new Role from the scratch and there were times I thought, easy way out was a better option :)

Let us share a couple of our findings that should help anyone who decided to take the path less trodden.

When you create a New Role, you will notice that some of the previleges are provided by default. The default privileges added cannot be removed.

We just wanted to make a role that gives the user the permisison to view all accounts and nothing else.

So we created a role with the following privileges

However when we login with the user that has ONLY this role assigned to it, we receive the following error

We spent a lot of time trying our various combinations, when finally we found that the problem was with the user settings entity "Write" permission. By default CRM provided the "Read" permission for this entity, but not the write permission.

Once this permission was added. Run iisreset. Login as the user and it should be just fine now.

In the process we also learnt that System Administrator has certain special privileges that are not presented on the Role user interface, so there is no way to can provide these permissions to any other user.

One of the permissions is to allow users to "Bulk Delete" records. Only System Administrator users have the permission to create "Bulk Delete" Jobs. This helped when we were developing one of the add-ons for Bulk Delete Operations. Details of this add-on has been posted on an earlier post

One of the workarounds though, is to Copy the System Administrator Role and create a new Role which will inherit these hidden privileges from the System Administrator Role.

Call Workflow from ISV button

We had once given an option to one of our customers to perform an action using on-demand workflow. We provided them with the steps to run the workflow from the "Run Workflow..." button that is added to the entity in case and entity has on-demand workflows published against them.

The customer however wanted that we add a button with a descriptive lable that performed the job so that the users are not burdened with having to select the correct workflow from the list and it was a one-click solution.

We found that CRM has an inbuilt function made available that calls the workflow "launchOnDemandWorkflow"

The syntax of the function is
launchOnDemandWorkflow('crmGrid',ObjectTypeCode,Workflow ID) //when called from grid
launchOnDemandWorkflow('',ObjectTypeCode,Workflow ID) //when called from entity form

This function has 3 parameters.
The first one is left blank only if the button is on the record of that entity and workflow needs to be called on that record only. whereas, if the button is placed on the grid of CRM entity view then first parameter should be 'crmGrid' so that the workflow will get called for the records selected in that view.

Second parameter contains object type code of that entity

Third parameter is the id of the workflow to be fired

To get the workflow id, follow the below steps.

1. Go to Setting --> Workflow form.
2. Here open the workflow that you want to run on button click.
3. Now press "Ctrl + N" which will open the same page in the IE.
4. In the Address bar of IE you can find the id of workflow.

This should help provide users with the most familiar option of clicking a button to perform an action.

How to re-assign records of one user to another user in Dynamics CRM

Sometimes when one of the users is no longer associated with the company or if the responsibilities of a user is changed, there is a need to re-assign all records of this user to another user.

Steps to re-assign records:

1. Open the User Form of the user whose records need to be re-assigned.

2. Choose the Re-assign Records menu option from the Actions menu

3. From the form options that come up choose the option to select the user.



This will re-assign all records of the user to another user. This does away with the need to manually create views for each type of entities and then assign it to other users.