StateCode and StatusCode coming up as numbers?

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

Jeet Gandhi 320 on at

Like (0)

Report

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,

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: StateCode and StatusCode coming up as numbers?

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

Was this reply helpful? Yes No
MS CRM DYNAMICS 592 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

Nithya Gopinath 17,078 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Matthias Moritz 5 on at

Like (0)

Report

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

Was this reply helpful? Yes No