How Do I create Columns that sort in MS Dynamics CRM 4.0.

Posted on 2009-02-17
Last Modified: 2012-05-06
I am adding columns to a view in CRM and would like to sort on those columns but they do not have a sort feature like some of the default columns. What is the procedure for making those columns "sortable"?
Question by:jplan
    LVL 9

    Expert Comment


    You click the 'Configure Sorting' link when customizing the view in the CRM Customization area, select the column you want and choose to sort ascending or descending.

    You can also just click the column name in the actual view itself of course to sort by that column.

    LVL 10

    Expert Comment

    It sounds as if you're trying to sort on columns that actually come from another entity (for example, if you put the email address of the Primary Contact on an Account view, you cannot sort by this field).  Unfortunately, this is a limitation of Microsoft CRM.

    One workaround (though painful) is to write appropriate fetchXML and javascript code to actually copy the field to the entity that you want it to be displayed on (you could also use a workflow to do this - though it takes a few minutes after record updates for workflows to finish processing).  Once it is physically part of that entity, you can display that field and sort on it.  Back to my earlier example: you could copy the email address of the Primary Contact from the Contact entity to the Account entity and then sort by that field.

    One other possibility (that I have run into a few times) is that you might be displaying a field from another entity that is already stored on the entity you are working with.  For example, if you want to display the name of the Account on an Opportunity view, you don't have to select the Account (or Contact) entity while building the Opportunity view - you can just select the Parent Customer field - this field will be sortable.

    I hope I've understood your issue correctly and have provided some helpful information.  I wish I had better news for you!

    Author Comment


    You understand the question exactly - thanks.

    What I am trying to do, more specifically,  is close to what you mention above. In an Opportunity View, I would like to see the name of the Contact if the Company(Account) is showing in the "Potential Customer" or show the name of the Company(Account) if the Contact is showing in the "Potential Customer" Column. Would like to be able to sort these columns.

     I would also like to show the zip code(Postal Code) and be able to sort by that.

    Would creating an "Advanced Find" be a better solution to all of this? If I could enter multiple zip codes that are located in a geographical area into an Advanced Find and bring me back all of those Potential Customers, that would probably be good enough. Thoughts?

    Would still like to know if there is a way to build "code" for the columns that are not sortable.
    LVL 10

    Expert Comment

    An advanced find is going to give you almost the exact same functionality as using a View (with a few exceptions).
    Here are answers to your couple of questions:
    SHOW ZIP/POSTAL CODE: No problem there.  Just add it to the view and it will be sortable
    MULTIPLE ZIP CODE ADVANCED FIND: Sounds like this may be a frequently changing need.  For this, what we do is to create a view that we call something like "Zip Code Search Template" - it contains all the things we want (columns to display, sort order, and base filter).  We then train users that, when they want to have a view of only a set of zip codes, then they should select that view, then click Advanced Find, then add the zip codes that you want to the list and click the Find button.  If they repeat that same find frequently, they can save it under a different name and have it on their custom list of views whenever needed.
    SHOW POTENTIAL CUSTOMER/SORT: So it sounds like you will show the Potential Customer on an Opportunity view (no problem, you should be able to sort on that), but you also want to show either the Account (if the Potential Customer is a Contact) or the Primary Contact (if the Potential Customer is an Account) - the only way to "natively" do this would mean that you have to display two columns and you wouldn't be able to sort by either one of them.
    SIMPLE SOLUTION: You could require that your users have an Account as the Potential Customer or a Contact as a Potential Customer (most businesses that we work with would consider themselves to be Business-to-Consumer, thus requiring that a Contact be the customer, or Business-to-Business, thus requiring than an Account be the customer - very few would say that they work with both).  While this doesn't completely solve your problem, it does limit you to having to show only a single column (and it probably improves the consistency and data quality of CRM as well).
    WORKFLOW SOLUTION: Here is a way to create a workflow to solve this problem (not perfect, because newly entered data will take 5 seconds - 5 minutes before it shows up in views):
    (1) Create a new field called Primary Contact and another called Account on Opportunity entity. Both of these should be varchar.   You don't need to add them to the form.  If you prefer, you can create only a single field called "Other Info" or something like that (the title is confusing though, since it could contain either a person or an account depending upon who the potential customer is).
    (2) Create a workflow on the Opportunity entity.  Have it run On Create and On Change of the Potential Customer attribute.
    (3) Have it test the Potential Customer field to see what it belongs to (your logic can say something like "If Account (Potential Customer) name contains data" (if this is true, then it means the Potential Customer is an Account - then have it put the name of the primary contact in the field you created (this will be an Update Record step - you'll browse to the last tab on the form which shows you fields that you have not placed on the form).
    (4) Repeat step #3, but have it test to see if it belongs to a contact and, if so, populate the Account field.
    (5) Test it out.  It should work by populating one of the two fields (you can also have it populate BOTH fields, just copying the potential customer into the Account field if it is an account or the contact field if it is a contact ... that way you don't have to show 3 fields on the View, just 2).
    CODE-BASED SOLUTION:  Ugh.  You'd write JavaScript to do this.  You would first create the same two fields that you created above.  Your JavaScript would need to go back to the Account or Contact entity to grab the data that you need.  This is possible, but it's some hairy javascript.  Here is a good blog entry if you want to go that direction:
    THIRD PARTY-BASED SOLUTION: Try  They have an add-on that takes care of this.  There is definately a learning curve, but it is a very robust solution once you learn it.
    Hope something above solves your problems.

    Author Comment

    Great information and great communication! I will have to take some time to digest the information and try out some of these solutions.

    Before I had asked these questions, I did add the zip code to the "Opportunities" view and it did not have the sorting ability. I guess the big question is; which zip code from which entity should I be pulling? I added Potential Customer(Account) and Potential Customer(Contact) The zips show up in two different columns, but not sortable.

    I like the idea of making all Potential Customers - "Company" only. This is the default when converting a Lead to Opp, Com, and Contact, as you said this adds consistency.

    Thank you so far.
    LVL 10

    Expert Comment

    Ok, I wasn't thinking when I made the comment about zip codes ... I forgot that they aren't in the Opportunity entity (duh!)  So you wouldn't be able to sort using Zip Codes either.
    You can add zip codes to Opportunities using the various different methods that I described above.  Again, if you're consistent with making Potential Customers limited to the Company, then at least you'll only have to go to one place to grab a zip code.
    Also, if you're consistently using Account for the Potential Customer, then you may be better off creating a view from the Account entity (all Accounts where there is an Active Opportunity).  You won't be able to list any Opportunity items on the view, but you can list and sort by account name, primary contact name, zip code, etc.
    There's one other way to get zip codes and other customer name items on an Opportunity that I had overlooked - you can use Mapping.  When you Map a field from a parent entity to a child entity, then the field is automatically populated when a record is created.
    For example: You can map Zip code from Lead to Opportunity and from Account to Opportunity. (you could do the same with primary contact name, company name, etc).
    When you train your users, you will have to train them that Opportunities should ONLY be created by converting a Lead OR by going to an Account and adding an Opportunity.  If they use this method, the appropriate fields will automatically be mapped.  If they create a "blank" opportunity, then this approach won't work.  This isn't ideal (ideally you would BOTH map the fields and either use a workflow or javascript to populate those fields if the user does not use the correct procedure) - but it's pretty simple to setup.
    To add mappings:
    (1) Make sure that the fields you want to map are available on both entities (i.e. you'll need to add zip code to the Opportunity entity).  Also make sure that both fields have the exact same field type (i.e. a 50 char field won't map to a 25 char field).
    (2) Settings | Customize | Customize Entities | Select the "parent" entity (i.e. Account)
    (3) 1:N Relationships
    (4) Click the Related Entity column to select the entity that you want to map to (i.e. Opportunity).
    (5) Find the appropriate related entity.  If you see two copies of the same entity, select the Parental Type of Behavior - NOT System.
    (6) Click Mappings
    (7) Click the New button
    (8) Select the source field and the destination field
    (9) Save and Close | Save and Close | Publish
    (10) Test it out to see if it works
    (11) Announce it to users and provide training.
    (12) Enjoy the status of "CRM Hero" for 24 hours
    (13) Start fielding complaints from users who are not following the training and wondering why the zip code column is blank.  Be awarded status of "CRM Loser" for not having a well thought out approach.
    (14) Visit local pub.
    (15) Call CRM partner and have them do the remaining work.  Become "CRM Hero" again.
    Okay, got a little carried away there.  Point is, if you take the simple route, expect to have to train and provide follow-up training to get your users used to doing things this way.  By combining the Mapping with the Workflow approach in an earlier message, you'll have about a 95% solution, which may be more than adequate for your needs.

    Author Comment


    Great advice. I did everything in the procedure and the field (Attribute) showed up on the opportunity view when I added it. I published everything, but there are no zip codes under the column. I even clicked the sortable Zip Code column  and there were none on either end. I have a screen shot of the mapping, do you see anything wrong?

    LVL 10

    Expert Comment

    Your screenshot looks good.  Remember, though, that zip codes will only map on NEW opportunities that you create when you go to the Account entity.
    Try going to an Account, clicking the Opportunity list on the side-nav, and then clicking NEW.  You should immediately see the zip code on the newly created Opportunity.

    Author Comment

    Yes, I missed that part about NEW - but it did work as you said.

    I will try the Workflow and see how it goes.

    Author Comment

    I've tried the workflow for many hours - no joy. The Workflow is firing because I can see it in the System Jobs and it reads "Waiting on Resources".  I tried the If Then statement like you said and after it didn't work I then I removed it and just put an "update record" and have the new_contact = "Potential Customer"(Contact). This did not work either. I have the newly created Contact and Company on the Open Opportunity View and not on the form.

    Call the CRM partner??
    Go to the pub?
    LVL 10

    Accepted Solution

    Pub sounds like the best option. :)
    Workflows in CRM can stop working for a number of reasons.  Let's go through a few quick troubleshooting items:
    (1) Check your System Jobs.  Are there ANY that are showing with a Status Reason as Succeeded?  If not, then it is likely that your workflows in general are not working correctly.  Let me know if that's the case and I'll send instructions on how to do that.
    If there are some that have succeeded (recently) then the fundamentals of your Workflow Service are in tact, so we'll move on...
    (2)Still in System Jobs, double-click one of your jobs that is waiting.  You should see a list of steps.  Where does it get hung up?  I suspect there is something with your If/Then that is never becoming "True".
    (3) Check your workflow to make sure that all of the following is true (I'm attaching a screen shot that will hopefully help, but I can't show all the details in the shot):
    (a) The workflow is created on the Opportunity
    (b) It runs when an opportunity is created, or the Potential Customer field changes
    (c) Assuming you are requiring your users to enter an Account as the potential customer, then you don't need to check a condition (so I'm leaving that out).
    (d) Update the Contact Name field with the name of the primary contact from the regarding account (in my image, I'm using the description field b/c I don't have the contact name field on my opportunity entity).
    (e) Publish or Perish.

    LVL 10

    Expert Comment

    One mistake on the previous post, the image for the "Set Properties" on the Update Opportunity should have read:
     {Primary Contact(Potential Customer (Account))}

    Author Closing Comment

    Great answer to the question. The Master really took the time to thoroughly answer my questions and was very clear in his/her solutions.

    Thank you very much.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
    Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now