Wednesday, July 8, 2009

CRM 4.0: Increasing Page Limit for CRM Grid Views

The number of records displayed on the lookup, or for that matter any view that displays the grid has a Paging Limit to it, which can be configured for each user from the Workplace->Personalize Workspace.

The maximum value available in Workplace however is only 250. Once this value is set to 250, the number of records per page in the lookup, and all other views will be 250 records.

However, there is a workaround to increase this Paging Limit for any user from the Database.

You can use the following query to set the value to say 1000.

UPDATE UserSettings SET PagingLimit=1000 WHERE SystemUserId IN
(Select SystemUserId from SystemUserBase WHERE FullName like 'Ashish%')

This will display 1000 records per page in views and lookups.

Thursday, July 2, 2009

CRM 4.0 Import Data Wizard: Now Updating Records is Possible

In Microsoft Dynamics CRM 3.0 there existed an option to update records when using the built-in Import Data Wizard. This was an extremely useful feature; we could export thousands of rows to Excel directly from CRM, update the information using Excel tools (such as Find and Replace), and then import this enriched data back into CRM. This would update our existing records rather than creating entirely new records.

This feature was in the beta versions of CRM 4.0, but when final release came around, this feature was nowhere to be found; a victim of the final cut. It was a perplexing omission, as all documentation and help files related to the Import Data Wizard mentioned the ability to update rather than create new. The Data Import Wizard could now only be used to create new records, which made any type of bulk update a near impossible task.

The surprising news is that you can still use this non existing feature. How? Here is an example:

In this example scenario, I want to update all my contacts with new data: email address.

1. Select an existing view or edit a new view using the Advanced Find. Make sure the columns you want to add data to are included in the view.

2. Export the view data using the ‘Export to dynamic worksheet’ option and save it.

3. Open the exported file, select all records, go to the Format menu, select Column sub menu and then the Unhide option. A new column should appear, containing the records GUIDs.

4. Rename the GUIDs Column to the name of the exported entity for example ‘Contact’. Move the column to the left of all other columns.

5. Update the required data. In this example, the email data is added to the existing records.

6. Save the Excel file as .csv file.
7. Use the Import Wizard tool in Microsoft Dynamics CRM 4.0 to import the newly created .csv file. Select ‘none’ for Data Delimiter, ‘Comma (,)’ for field Delimiter.

8. Click next and select the exported entity, ‘Contact’ in this example. You can see the ‘Enrich data by updating records rather than creating new records.’ option available and checked. Select a data map if required and click next

9. Check the ‘Import duplicate records’ option and click next

10. Complete the import process.
11. Go to the workplace and open the data import section. Once the data import job is done, open the the job records and see which records were updated. Notice that existing records were updated, no new records were created.

12. Finally, refresh the view you started with to see the updated data for the existing records.

Thanks to Yaniv for detailing out these steps. You can view his blog link at


Wednesday, July 1, 2009

CRM 4.0 Lookup: Retrieve Column values from Lookup window

There are times when you need to retrieve some values from the parent record, on the selection of the lookup value. In such a scenario, you can retrieve all column values of the lookup window using the following script:

Example: Order Form - Customer Field OnChange event
if(crmForm.all.customerid.DataValue != null)
var parentCustomerID = document.getElementById( "customerid" );
var lookupItems = parentCustomerID.items[0].values;
alert(lookupItems[0].value); // First column value of Lookup
alert(lookupItems[1].value); // Second column value of Lookup
alert(lookupItems[2].value); // Third column value of Lookup
alert(lookupItems[3].value); // Fourth column value of Lookup
alert(lookupItems[4].value); // Fifht column value of Lookup
alert(lookupItems[5].value); // Sixth column value of Lookup

This will display the Contact's 'Full Name', 'Parent Customer Name', 'Address 1: City', 'Address 1: Phone', 'Business Phone' and 'Email' values from the selected Contact (Default Contact Lookup View). If you select an Account, this will display the 'Account Name', 'Account Number', 'Primary Contact', 'Address 1: City', 'Main Phone' and 'Email' values from the selected Account.

The lookupItems array will hold all the values in the same sequence as defined in the Lookup View.

You can now add your required values in any lookup view and retrieve the same without making service calls.

Note: You should disable the Automatic Resolution, so that users cannot add values automatically. This script will work only if the Lookup View is loaded.

Feel free to reach out in case of any issues/queries in this regard.