Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / StateCode and StatusCo...
Microsoft Dynamics CRM (Archived)

StateCode and StatusCode coming up as numbers?

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

I am writing a powerbi report that pulls the data from CRM Dynamics online. It involves the opportunity by StateCode(Open, Won, Lost) and the StatusCode(In Progress, On Hold, Won, Canceled, Out-Sold, plus some custom others..) but in the report I only get StateCode: 0, 1, 2 and StatusCode: 1,...5, 100000000, 100000001,....,10000003. Best Guess is that there are some more tables I need to reference but so far I have been unable to figure out which. If someone could point me in the correct direction that would be appreciated. 

*This post is locked for comments

  • Suggested answer
    Matthias Moritz Profile Picture
    Matthias Moritz 5 on at
    RE: StateCode and StatusCode coming up as numbers?

    Hello, there is a simpler solution without relations. With a flag, the API sends the data as text and you can read them with a simple Formula. Works also, when you add related entities.

    Check this link: community.powerbi.com/.../1276593

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: StateCode and StatusCode coming up as numbers?

    I ended up changing the column to text and then replacing the values with their corresponding words.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: StateCode and StatusCode coming up as numbers?

    The Table StringMap does not appear in the list that powerbi displays when I pull the list of tables from CRM.  I have not seen an option to querry the Database with SQL.

  • Verified answer
    Nithya Gopinath Profile Picture
    Nithya Gopinath 17,074 on at
    RE: StateCode and StatusCode coming up as numbers?

    Hi,

    We have a Power BI option-set Assistant in XRM toolbox.

    See: http://365lyf.com/how-to-use-dynamics-optionset-labels-within-powerbi/

    Or You can try creating a new table in Power BI, manually enter these option set values and set a relationship with the entity table which has the option-set attribute.

    Another alternate way is to make the option-set column as a string and replace the corresponding option-set values with option-set text.

    Also, see: https://community.powerbi.com/t5/Desktop/CRM-option-set-problem/td-p/150758

    Hope this helps.

  • MS CRM DYNAMICS Profile Picture
    MS CRM DYNAMICS 580 on at
    RE: StateCode and StatusCode coming up as numbers?

    StringMap table

    select AttributeValue, Value

    from StringMap

    where ObjectTypeCode = 1 -- entity type code 1 = account

    and AttributeName = 'new_address2_country' --fieldname

    basically are inside FilteredStringMap.

    check this link: ashwaniashwin.wordpress.com/.../crm-2011-fetch-optionset-text-through-filteredstringmap-table

    it has an example and also screenshots

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: StateCode and StatusCode coming up as numbers?

    That is what I figured but which entity do I need to fetch a column from?

  • Suggested answer
    Jeet Gandhi Profile Picture
    Jeet Gandhi 320 on at
    RE: StateCode and StatusCode coming up as numbers?

    Hello,

    Statecode and Statuscode are optionset fields in CRM. That means, for each of the options like Open, Won, Lost, In-Progress etc it will contain an associated value (integer number). So whenever you fetch option set field value from CRM, it'll always provide you the value of the option and not it's text.

    So to get the text of the option set, you need to fetch another column from the entity table that contains the text value of that field.

    Hope this would be helpful to you.

    Please mark it as answer if found useful.

    Thank you,

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans