How to create a dropdown or picklist column with a list of values in ms excel

Say we would like to create a drop down column in excel sheet with a list of predefined values. Create a list of predefined values in a new sheet and select the list of values and right click and select Name a Range..and give some name say "MyCustomList"

Now click on the column on which you would like to create a dropdown. 
Select Data on the top menu and go to Data Validation -> Data Validation. In the validation criteria, select list in the allow section and give the Source as the name of the range you have chosen like
 =MyCustomList

Click ok to see the dropdown column with the list of values defined in the range. If you want to remove the dropdown then under Data validation, choose Allow Any value.

MS CRM 4.0 Enrich Data - Update MS CRM Records using Excel

By using native data import feature in MS CRM 4.0, we have the ability to update the existing data apart from creating new records. This is called Data Enrichment which is the new feature brought up in MSCRM 4.0. This can be done by exporting your data into an Excel Sheet. In general, when we export the data to a static excel sheet, you find the excel sheet columns start from B instead of A. This column A is in hidden state which holds the GUID of existing record.
If you are so curious, you can unhide the column A to see the GUID. Please follow the steps in this link to unhide the excel columns.

Make your changes to the data in the same excel sheet and save it as a .CSV file. Now, re-import the data using standard data import wizard. This time when you upload the file, you will be pointed to a new checkbox option 'Enrich data by updating existing records rather than creating new records'. This option is used to update the changes you made for the existing records instead of creating new ones.

MS Excel How to view hidden columns

You can unhide any column in MS Excel by using the following steps.
1. Select all the columns using Ctrl+A
2. Point the cursor on any column and right click and Unhide. That's it. Now you can see all the hidden columns in the excel sheet.

Scribe Integration with MS CRM Mapping Picklist field in DTS

1. Open up your DTS file and select your source and target fields and click Datalink

2. Now, Right click on the target field and Edit Data Formula.. Click on calculated variable and put the source field like S1 and point the dropdown to String

3 Next, insert this calculated variable in the formula and voila!

4. This maps your source picklist field with text values to the target picklist fields

Scribe Integration with MS CRM Map Lookup field in DTS

1. Open DTS file and select your source field and targte lookup field and click DataLink
2. Right click on the target lookup field and click on Edit Data Formula..
3. Use DBLookup function as

Syntax: DBLOOKUP( TextSourceField, "connection", "table", "lookup_field", "substitution_field" )
Where connection = "T" for Scribe versions below 7.0 and connection = "Adapter for Dynamics CRM" for Scribe 7.0 and later.
Example for an Account lookup field
DBLOOKUP( S1, "Adapter for Dynamics CRM", "account", "name", "accountid" )

MS CRM Workflows Waiting and Failed - How to check the error details

1. Open Advanced Find and point the entity to System Jobs

2. Add the filter criteria as defined below:

   SystemJobName Equals your_customworkflow_name

   ErrorCode ContainsData

3. Edit the columns and include MessageName, ErrorCode

4. Click Find to get the list of records that got failed in running the custom workflow with a

   detailed error message

MS CRM Page Limit - Increase Number of Records Per Page more than 250

The default Page limit for MS CRM is 50. This can be increased to 250 using Tools -> Options from the main menu. This is the maximum value in MS CRM 4.0. 
Sometimes you might want to view records more than 250 which can be used to run the bulk edit feature or run a workflow on more than 250 records which saves time. 
To display a count more than 250, you can modify the PagingLimit value in the database for a particular user using the below query.

UPDATE UserSettingsBase SET PagingLimit=500 WHERE SystemUserId = Guid of the SystemUser

MS CRM 4.0 Timeout Issue - Timeout period elapsed prior to completion of the operation

Error: "The timeout period elapsed prior to completion of the operation or the server is not responding"

You can come across the timeout issue in MS CRM in one of the following scenarios:

1. when installing the update rollup for MS CRM

2. when importing the customization files

3. when running advanced find query that runs more than the expected time to get the results 

4. when importing New Organization

By default, the oledb timeout is set to 30 seconds in MS CRM. It is obvious to increase this value to meet your requirements. To accomplish this, you can modify the OLEDBTimeout and ExtendedTimeout values which is briefly described in the below article:

Increase MS CRM Timeout

MS CRM 4.0 - Set OLEDBTimeout and the ExtendedTimeout registry subkeys

To set the values of OLEDBTimeout and ExtendedTimeout, you have to login to the MS CRM server as administrator.


OLEDBTimeout: 

1. Start, Run, type regedit, and then click OK.
2. Locate the following registry subkey:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM
3. Right-click MSCRM, point to New, and then click DWORD Value to create a new DWORD value.

4. Rename the DWORD value to the following value: OLEDBTimeout. The default OLEDB 

    timeout value is 30 seconds
5. Right-click the DWORD value, and then click Modify. In the Edit DWORD Value dialog

    box, type 86400 in the Value data box, click Decimal in the Base option, and then click

    OK.

ExtendedTimeout:
1. Right-click MSCRM, point to New, and then click DWORD Value to create a new DWORD value.

2. Rename the DWORD value to the following value: ExtendedTimeout

3. Right-click the DWORD value, and then click Modify. In the Edit DWORD Value dialog 

   box, type 1000000 in the Value data box, and then click OK. You can type a value that is

   larger than 1,000,000. However, do not type a value that is larger than 2,147,483,647. 

   This is hexadecimal 0x7FFFFFFF.

If this key already exists, note down the current value. Once you are done with the task like import or the upgrade of Microsoft Dynamics CRM, revert the value to its original value or delete the key if it doesn't exist previously.

Restart IIS to take these values into effect.

MS CRM 4.0 Export To Excel - Export Records more than 10000

In real time scenario, there are situations where you would like to import huge date from MSCRM to Excel. But, there is a limitation in the export to excel feature that, it imports a maximum of 10,000 records by default. To import all the data we need to update the column value of 'MaxRecordsForExportToExcel' in the database. 


1. Open SQL Server Management Studio
2. Open your MS CRM 4.0 Organization Database (OrganizationName_MSCRM)
3. Check the current limit by using the below query
    Select MaxRecordsForExportToExcel From OrganizationBase
4. Update the MaxRecordsForExportToExcel column in OrganizationBase to the desired 
    value.
    Update OrganizationBase Set MaxRecordsForExportToExcel=50000
5. Reset the IIS Server