Saturday, August 20, 2011

A Codeless Way To Complete Activities in the Mobile Express Client

One of the great drawbacks with the Mobile Express client for Dynamics CRM 2011 is how restricted you are when working with activities. Things like completing Appointments, once you have met with a prospect, are not possible with Mobile Express. Hopefully, the new client that Microsoft made mention of at WPC and in the last Statement of Direction will fix this. Until then, here is a nice trick for letting your sales staff complete Appointments, and add how the meeting went, all from within the Mobile Express client.

How Does It Work?

The trick comes from our ability to add Notes to an Appointment and using a workflow to complete the Appointment for us.

Making Appointments Visible in the Mobile Client

Firstly, we will need to be able to see Appointments in the mobile client. Most entities can be made visible in the Mobile Express client by ticking the ‘Mobile Express’ tickbox on the entity detail form.

image

Once you do this, you will see the entity, along with the others.

image

Opening an Appointment reveals the limitations inherent to the Mobile Express client. There is no ‘New’ button and no way to edit existing records. The only thing you can do is add a Note.

image

and therein lies our ‘backdoor’. By constructing a specific workflow, we can complete our Appointment and add details on our meeting.

The Workflow

Here it is:

image

The process runs on Notes on their creation. It checks the title of the Note and if it equals ‘COMPLETE’ it copies the description of the Note onto the Appointment and sets the Appointment status to Completed.

image

What Happens?

The upshot is if our travelling salesman adds a Note to an Appointment with the title ‘COMPLETE’ and puts in some text in the description box,

image

Our meeting will automatically close out with the text added.

image

Sorry about the unavailable warning. I ran a test Appointment first before writing the blog to make sure it would work. You generally will not see this.

Conclusions

There you have it. A relatively simple workflow which makes the Mobile Express client a little more practical. I have used the Appointment entity here but the same trick should work with pretty much any other activity type. Also note that, generally, the title field is automatically populated by CRM when you create Notes through the full client so it is unlikely that a non-mobile user will inadvertently close out an activity.

Enjoy.

Saturday, August 13, 2011

Changing the Lookup Reference When Importing Related Data

This is a handy new feature for the import wizard where matching off the name of a parent is not sufficient to link records you are importing to other records in CRM, such as where the primary name of the parent record is not unique. We used to be able to do this with the old server-side migration manager in v4 but not in the v4 import wizard.

Step 1: Getting a Template For Our Import

A really nice feature of Dynamics CRM 2011 is the ability to generate templates for all the CRM entities straight from the client. To do this, click the drop-down arrow on the Import button in the ribbon for the entity you want to import records for.

image

This generates an Excel-compatible XML file which has nice features like drop-downs for the pick list fields you need to fill in and tooltips to let you know the kind of information that needs to go into a field.

image

You will notice in the above image of the Contact import sheet that I have set the Parent Customer to ‘12345’. This is the Account Number for my parent customer, not their name, as would be traditionally used.

image

Setting the Lookup in the Import Wizard

Importing the data into CRM is very easy with the Dynamics CRM 2011 Import Wizard but, by default, the wizard will try to link our Contact to an Account with the name, ‘12345’, not the Account Number ‘12345’.

image

Therefore we have to adjust our mapping so it does not match on the Account Name but the Account Number. To do this we run the Import Wizard as usual by clicking the ‘Import Data’ button on the ribbon but when we get to the ‘Review Mapping Summary’ screen we click ‘Edit’.

image

Skip setting the entity you are writing the records to and click ‘Next’. This will bring you to the mapping screen where we link the columns in our data source to fields in CRM. In our case we are interested in the ‘Parent Customer’ field.

image

The magnifying glass means this is a lookup field and by clicking on the magnifying glass we can set the matching fields.

image

The Parent Customer field is an interesting one because it can point to either an Account or Contact. In our case we are only interested in modifying the Account referred field to be the Account Number. We modify it by clicking on the magnifying glass and setting Account fields to match on. In our case we might also untick the Contact as an option for the Parent Customer as we will only bringing in Contacts with a Parent Account.

image

The good news is now when we import everything goes to plan.

image

image

Enjoy.

Sunday, August 7, 2011

A Codeless Address Finder Using Filtered Lookups

A bit of a mouthful of a title but, basically, I am going to show how to set up a codeless set of dynamic picklists (now called option sets) by ignoring option sets and using lookups, which can now be filtered, as a standard feature in CRM 2011.

My example will be an address finder. In this case we will enter a country and this will then restrict the states. This will then restrict the postcodes (zipcodes for our American friends) we can select and then this will restrict the towns and cities we can select.

First Step: Get Some Data

For this example, I am going to use Australian postcodes, but it will work with any postcodes. Australian postcodes are available here and US zipcodes are available here. I cannot find a decent list of UK postcodes so, if you know of one, please add a comment.

The Structure

We will create four new entities:

  • Country: We will use the name field to store the name of the country
  • State: We will use the name field for the state and create a lookup to country
  • Postcode (or Zipcode): We will use the name field to store the code and create a lookup field to state
  • City: We will use the name field to store the name of the city and have a lookup field to the postcode entity

I will also add lookup fields to these in a new section on the Account record.

The results are something like this.

The Country record:

image

The State record:

image

The Postcode record:

image

The City record:

image

and finally the lookups on the Account record:

image

Getting Data Into the System

Using the downloaded CSV record, we adjust the data so that we have four columns in each file:

  • Postcode
  • City
  • State
  • Country

Given we are only dealing with Australia, I added the country record by hand. For the other entities, we can download templates by going to Settings-Data Management-Templates For Data Import. The templates that are downloaded are XML files but they are completely compatible with Excel.

Populate the Postcode.xml, City.xml and State.xml files using the data from the source file. You will have to eliminate the duplicates. Excel 2010 has a ‘Remove Duplicates’ function in the Data tab of its ribbon to clean up the data.

If the xml file is over 8M in size, it will not import. This happened to me so I saved them all as CSVs and all was good. I also noticed that the files had a bunch of extra empty lines at the end of them, where the duplicates had been removed (nice work Excel!). I opened up each file in notepad and stripped out the extra commas.

Now combine the three xml files into one zip file (highlight them, right click and Send To Zip File)

Go to Settings and click ‘Import Data’ in the ribbon. It should just work. In my case the postcode file did not automap but once I mapped the fields for it, within the import wizard, I was back on track. Here are my results.

image

You will notice that 275 records failed. This confused me but, with a little investigation, it turns out that there are some postcodes in Australia that go across multiple states. Therefore, when I imported the cities and tried to link it to one of these postcodes, there were two records in the system to link to for the two states the postcode traversed. Given the blog is more about filtered lookups than about importing data, I will live with the 2% fail rate.

Creating the Filtered Lookups/Dynamic Picklist

If we ran with our system now, the lookups on the Account record would have no knowledge of each other. I would have to trawl through all the cities in the database to link to the right one. This is where the new filtered lookup feature comes into play. To set up the filtered lookup, go to the Account form, select the State field and click ‘Change Properties’ in the ribbon. Down the bottom of the default tab are the filtered lookup settings.

image

We want to configure the ‘Related Records Filtering’ because we are matching the Country that the Account is associated to and the Country that the State is associated to. I untick the user’s ability to turn off the filter and turn off the search box.

The result is a list of states just for the country I have selected on the Account record.

image

For those of you outside of Australia, these are the states and territories of Australia. I rinse and repeat for the Postcode and City

image

image

The result is every time I click a lookup, as long the lookup above is filled in, the list will be appropriately adjusted, just like a dynamic picklist (dynamic option set). In the case of the postcode lookup, there is probably limited value (in the database, Australia has eight state values and almost 3,000 postcodes and we have no good concept of ‘county’ like the USA) but the solution shows its value in the city lookup.

image

Because I have already set the postcode to ‘2089’, CRM knows I must be in one of the three cities in this area.

Conclusions and Takeaways

If you have a nice taxonomy of values and need to dynamically adjust one as you set the value of another, this is not a bad way to do it. One thing to note though is that the solution does not enforce consistency. What I mean by this is, in the above picture, we could go back to country and select ‘USA’, even though the rest of the data are for Australia.

Also, (and this one is more for Microsoft on the off-chance they read this) what would be really great is, as well as the ability to use a common entity to filter the lookup, the use of a global option set. However, at this time, we only have the option of a common linked entity.

Finally, for this specific example, we are replacing the default address fields on the form with new lookup fields. If you are using default reports or have legacy reports which reference the system address fields, you will either need to adjust these reports or keep the system fields in sync via a workflow or plugin.

Enjoy.