Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Change the State and Country fields to be drop down lists

  • 1.  Change the State and Country fields to be drop down lists

    Posted Nov 01, 2018 12:18 PM

    I am looking for the best practice to change two of our our address fields - state/province and country/region - to be chosen from a global option set rather than a text field. I found a great article related to this, but it dates back to 2012 and is based on CRM 2011. We are using D365 online v9.

    Adding a State or a Province Field that is a Dropdown or Lookup in CRM 2011

    Do any of my CRMUG community friends know of a resource that might be more recent?
    Have you implemented the same thing in your organization?
    If so, what should I watch out for?

    Any insight is appreciated.
    Lisa Johnson




    ------------------------------
    Lisa Johnson
    CRM Analyst
    Interstates
    Sioux Center IA
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Change the State and Country fields to be drop down lists

    MICROSOFT MVP
    Posted Nov 01, 2018 01:06 PM
    Edited by Seth Bacon Nov 01, 2018 01:06 PM
    Hi Lisa,

    Anticipating some great answers from the community on this item as this is a common request. Will throw my 2 cents in initially and then be following the thread to see ways for improvement.

    Overall the basics haven't changed between CRM 2011 and D365 for this functionality around creating the reference (lookup) tables and then populating the out of the box fields. One main change would be to leverage business rules instead of JavaScript to populate the out of the box field.

    The other big catch with replacing the fields now is the composite fields for address which leverages the out of the box fields. I don't have a great answer on how to leverage both the composite fields and the new state/country fields (hence why I want to follow the thread).

    Excited to see what others have to contribute to the thread and sounds like a great blog post once you get all of the amazing answers.

    ------------------------------
    Seth T. Bacon
    Seattle, WA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Change the State and Country fields to be drop down lists

    MICROSOFT MVP
    Posted Nov 01, 2018 01:20 PM
    Edited by Seth Bacon Nov 02, 2018 01:59 AM
    Two other quick notes:
    1. Make sure to take into account if you are using geocoding that you account for leveraging the new fields. Will have to do more digging to confirm which fields Dynamics uses to calculate the latitude and longitude.
    2. Something that is often overlooked (as very few people leverage it), but address have a special relationship as it is a stand alone table. When you see the multiple addresses on the contact and account, those are actually connect to the address table. This allows you to have multiple address types (mailing, physical, receiving, etc.) and unfortunately you have been unable to update the address table in the past. This has the impact that when you create the state/country field, you are really only creating the fields on the contact and account entity and would need to take that into consideration when you need the information to flow to other entities or external to the system. All things that can be worked around but need to have an awareness.

    I am hoping that some of these 'features' will be improved in future versions.

    ------------------------------
    Seth T. Bacon
    Seattle, WA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Change the State and Country fields to be drop down lists

    Posted Nov 01, 2018 02:37 PM
    Thanks Seth!

    After I submitted my post, the thought of the composite address field crossed my mind as another issue to overcome.
    I appreciate the heads up about multiple addresses as that could eventually be an issue for us as well.

    It seems like this should be a lot easier to accomplish. Keeps life interesting, I guess.

    Lisa

    ------------------------------
    Lisa Johnson
    CRM Analyst
    Interstates
    Sioux Center IA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Change the State and Country fields to be drop down lists

    Posted Nov 02, 2018 06:03 AM
    Hi,

    think about using the relative new "onkeypress" functions:
    Dynamics CRM 2016 - Turn Any Text Field into an Auto-Complete
    With that you can enhance the oob textfield to fit your needs.

    Regards,
    Marc
    ​​

    ------------------------------
    Marc Lübberstedt
    Sycor
    Goettingen
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Change the State and Country fields to be drop down lists

    TOP CONTRIBUTOR
    Posted Nov 02, 2018 08:15 AM
    ​Lisa,

    I can be a dicey endeavor at best.  A lot depends on your answer to this question:  do you know, or have any plans to, synchronize with Outlook?

    - Glen

    ------------------------------
    Glen Wolinsky
    Lead Applications Developer
    Miller & Martin PLLC
    Chattanooga TN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: Change the State and Country fields to be drop down lists

    GOLD CONTRIBUTOR
    Posted Nov 02, 2018 09:45 AM
    Lisa,

    In our system we have 3 different set fields for country/address!  I know this is ugly, but this is historical.

    1. Original text fields (Only visible on some views and some addresses. Used for import from other systems)
    2. Option Set  fields (This is our main way to enter and display data. Those are hierarchical. We are using a javascript that we copied from a MS sample a couple of years back) 
    3. Lookups   (those are kind of obsolete in the UI, but are still used in some reports and the state/country entities contain other info like the ISO codes etc. Note there is a limitation in CRM where lookups cannot be added to the 'customer address' entity and this was an issue for us)

     

    Anyway, the trick to make this work well is to synchronize the values between the 3 sets of fields.  We wrote a plugin that does it. When a field is updated, the plugin modifies the 2 other fields accordingly.



    ------------------------------
    Christian J. Betrisey
    Software Developer
    American College of Emergency Physicians
    Irving TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: Change the State and Country fields to be drop down lists

    GOLD CONTRIBUTOR
    Posted Nov 02, 2018 10:09 AM
    Lisa,

    What you can do is create two custom entities for Country and State. Then, add custom lookup fields on any entities where you want to use these (i.e. Account, Lead, Contact). The post you referenced has some good recommendations, some of which I will reiterate below. There are many considerations:

    1. Third party integrations will most likely write to and read from the built in fields (address1_country, address1_stateorprovince). Because of this, you probably want to allow user selection with the lookup fields but then write the description/character code values into the built-in fields. This needs to be a two-way transfer of information - if a user selects a lookup value, write the backing text to the OOB field and if an integration writes a text value to the OOB field, populate the lookup value.

    2. In my opinion it is a worthwhile customization because it helps very much with data integrity. I have seen systems that allow free form text in those two fields and you can end up with terribly messy data.

    3. You need to decide on a standard for the two OOB fields (i.e. full description or ISO code). Having a mixture of those creates problems. Again, third party systems may dictate a standard here, but many I've seen allow a choice between full description or code.

    4. This will only enforce limited choices in the UI. Any process that writes directly to the OOB fields will need to be writing clean values so that the population of the custom lookup fields can happen.

    5. You could use drop downs and optionsets as the post mentions, but I think the entities are a good way to go in terms of management of the values and the ability to have other things associated with each country/state. Additional data items on those entities could include a phone mask or default currency value.

    6. As soon as you introduce additional fields, there is a possibility that the data between the lookup and OOB field may get out of sync. That's a risk with this implementation rather than using replacement fields and not storing anything in the OOB fields. In my experience this risk is acceptable because pure replacement fields don't work when there are integrations.

    7. The "additional addresses" feature for Contacts presents a unique challenge. That form is not very customizable like other forms. As far as I know, you can't add the lookup fields to this form in place of the OOB fields like you can on the full Contact form. A creative solution will be needed there. I have seen where a developer used an HTML web resource control with JavaScript in order to present country and state data in dropdowns on the additional addresses form. These drop downs still made use of values from the custom entities in order to populate the drop downs.

    I hope this information is helpful. #CustomerEngagement #Technical
    ​​​

    ------------------------------
    Andy Arndt
    Minitab, Inc.
    State College PA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: Change the State and Country fields to be drop down lists

    Posted Nov 02, 2018 10:20 AM
    Thanks to all of you for input!

    Glen - we do sync with Outlook. If I understand correctly, Outlook uses free text fields for state and country and thus so does D365. This contributes to my issue.

    Christian - thanks for sharing your process.

    Andy - you have given me much to consider. Thank you!

    We'll definitely need to put some thought to this. On the surface, users think it should be as easy as 'adding a new field'. I wish that were the case.

    Lisa

    ------------------------------
    Lisa Johnson
    CRM Analyst
    Interstates
    Sioux Center IA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: Change the State and Country fields to be drop down lists

    MICROSOFT MVP
    Posted Nov 03, 2018 06:00 PM
    Since you are on V9 of Dynamics 365, you could potentially use another option that is available in the new Unified Interface. This option is the use of Controls. This will allow you to use the OOB State & Country fields.

    How this works is that you would still need to create the State entity and Country entity as mentioned in a previous post. But, with the controls, you don't need to set up a relationship between the entities. Before doing the next steps, I would recommend doing this in your Sandbox environment or in a Trial environment.

    Reference Microsoft article: Additional controls for Dynamics 365 for phones and tablets

    To get this set up, go to the Account form (for example) and open the editor. Select the State field and open the field properties. There is now a new tab called "Controls". In this tab, select the Add Control option and choose the Auto Complete. Once the Auto Complete is selected, there are editable options below that allow you to reference an Option Set or an Entity, choose Entity. Then you can look up the State entity and a particular View from that entity to use for the options.

    Once the Control is set up, click Ok to apply, and Save and then Publish the form. Open an Account record in the new UI and click on the State field. You should now see a list of options to choose from.

    As a disclaimer, I have tested this out and noticed a few oddities with how the options are listed, but it could be browser related. I need to do further testing.

    ------------------------------
    Aaron Back, MCP
    Sr. Microsoft Dynamics 365 Consultant
    www.acemicrotech.com
    _______________________________________
    CRMUG Board Member
    CRMUG Chapter Leader - Cincinnati, Ohio
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 11.  RE: Change the State and Country fields to be drop down lists

    Posted Nov 05, 2018 09:38 AM
    Thanks Aaron!  I'll look closer at the article you reference. I notice the Microsoft note that the controls are only available for mobile and tablet use, but it might get us closer to what we need.

    Thanks again!
    Lisa

    ------------------------------
    Lisa Johnson
    CRM Analyst
    Interstates
    Sioux Center IA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 12.  RE: Change the State and Country fields to be drop down lists

    Posted Nov 05, 2018 05:11 PM
    Edited by Lorenc Koka Nov 05, 2018 05:12 PM
    Some additional considerations, based on similar experience:
    1. Using lookups will be helpful to filter state options based in country value, example if Country = USA list only USA States, If country = Canada list only Canadian provinces and territories. You can add ISO code field and translations, but still main field only will be displayed on the lookup.
    2. Using an option set will need custom code to filter states based on country value, but it is extremely useful on multilingual implementations as you can translate options values.

    ------------------------------
    Lorenc Koka
    Senior Technical CRM Consultant | Business Analyst Lead
    PowerObjects
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 13.  RE: Change the State and Country fields to be drop down lists

    SILVER CONTRIBUTOR
    Posted Mar 29, 2020 12:18 AM
    Hi Lisa,

    Please read this blog post, where I illustrate how to achieve this kind of functionality in Dynamics 365 new Unified Interface.
    https://www.powerappsd365.com/post/configure-out-of-box-field-control-in-microsoft-dynamics-365

    Feel free to let me know if you have any questions/concerns. As always, I am happy to help the community.

    Thanks
    Jay

    ------------------------------
    JayBuddhdev
    https://www.powerappsd365.com
    Regina SK Canada
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 14.  RE: Change the State and Country fields to be drop down lists

    TOP CONTRIBUTOR
    Posted Mar 30, 2020 04:03 PM
    Jay's approach is solid. One thing to be aware of is that if a lead/account is created via another method (IE imported via integration), on the plus side, the system will allow the record to be created, but form will not force the user to update any invalid data unless they touch it, at which point they will be forced to select a valid option.

    I can send you a simple solution file with a demo entity and field using this approach if you want to test it without building it out.
    ​​

    ------------------------------
    Ryan Perry
    Business Systems Analyst
    Auric Solar
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role