SBX - Search With Button

SBX - Forum Post Title

CRM2011 - Problem with Option Set values not showing in the DB

Microsoft Dynamics CRM Forum

KRHJ asked a question on 16 Feb 2017 9:05 AM

Question Status

Suggested Answer

Hi

We have CRM2011 on premise and we have an issue with an Option Set that has been in use since our go live 4 years ago.

Between our DEV, TEST, Pre-Prod and Production environments we have normally just added new values to option sets manually on each environment instead of creating a solution for a single option set change and moving it through the environments.  This has never been a problem for us as we always ensure that the names and values are exactly the same on each environment.

However we recently added a new value to an option set in all our environment, and we can see it using Advanced Find and we can enter data into CRM using the new value.  However when one of our reporting gurus goes to query the DB they can see the code against the entry in the DB but when they query the StringMap table for the description there is nothing there.  so it seems the value is there in the front end but doesn't exist in the String Maptable

Can anyone help with this - I would like to know why its happened and how we fix it please

Thanks

K

Reply
Alex Shlega responded on 16 Feb 2017 9:47 AM
My Badges

Hi KRHJ,

 do you have more than one language enabled for that organization? If yes, I'm wondering if your reporting folks have different language settings in CRM and, so, maybe you just did not provide a translation for that new option set value..

 Do they have to CRM web application? Did they try Advanced Find as well?

Reply
KRHJ responded on 16 Feb 2017 9:52 AM

Hi Alex

Thanks for the response

We only have a single language (English) set for the organisation and all users are using the web client to connect.

From the UI and Advanced Find everything with the option set looks fine, users can add data in the table and reference the option set and can search for it and pull it via advanced find.

The problem is when one of our reporting team use SQL to pull back statistical information, they can see the option set code but when they query the stringmap table the value is not there.

Hope that makes sense

K

Reply
Alex Shlega responded on 16 Feb 2017 10:06 AM
My Badges

Hi K,

 might be a human error then. Could you ask them to query data from the filtered view for that entity? If they find the value there, it means something is wrong with the joins they are using in the report query. Filtered views are pulling data from the stringmap table as well (actually, CRM is pulling data from there, too.. so I'm almost sure it's an error in the query but who knows.. at least if they can see it in the filteredview they can probably have a look at how stringmap is linked there)

Reply
KRHJ responded on 16 Feb 2017 10:18 AM

Hi Alex

Its established SQL that has worked before and is working for all the other values in the Option Set, its only the new value they cant retrieve.  We also asked someone else to check using their own query and the values aren't showing in the StringMap or StringMapBase tables for them either.  

We haven't seen this before so no idea why

K

Reply
Alex Shlega responded on 16 Feb 2017 10:27 AM
My Badges

Hi K,

 What's bugging me is that you can see the value in CRM but not in the stringmap. I would still try the filteredview to make sure it's not a query error. If it does not show up there but it does show up in CRM.. I'm wondering if it's the right database (though it sounds sort of far-fetched). Other than that, I'm pretty much out of ideas, too.

Reply
KRHJ responded on 16 Feb 2017 10:30 AM

Thanks for all your responses Alex

It is definitely the right DB as it is an established query that she is running and she can see all the other values present in the option set, just not the new one.  I will ask her to check the filteredview again though

Thanks

K

Reply
PaulDonn responded on 1 Sep 2017 6:09 AM

Hi K,

Did you ever find a resolution for this issue to get the new option set value into the StringMap table?

Thanks,

Paul

Reply
KRHJ responded on 19 Sep 2017 3:18 AM

Hi Paul

Apologies for the late response - I was on leave.  I still have no resolution to this.  I raised it at the CRMUG EMEA conference in April with some of the MS MVP's and no one had a solution for it.  At the moment we are having to work around it for reporting

Do you have the same issue?

Regards

K

Reply
Prateek responded on 19 Sep 2017 3:31 AM
My Badges
Suggested Answer

Hi,
Can you post your SQL query here? The 3 column that should suffice are AttributeName, AttributeValue and Value. Please query the stringmap table and do share the screenshot.
Techincally, this appears to be not feasible since CRM store data in SQL and not on frontend.

To debug it further, please enable SQL profiler and then try to set the optionset from UI. Cross validate in profiler whihc table is being hit while retrieving the optionset value.

Also, this can be raised with premier support team incase you table/views are corrupted. You can d othat from customerportal.

Thanks,
Prateek

Reply
PaulDonn responded on 19 Sep 2017 4:14 AM

Hi K,

Yes, I've had the same issue. I wasn't able to have the StringMap populated with the new labels so I queried the AttributePicklistValueView instead. I used the query below to return me all the values and labels of a particular Option Set, including those missing from the StringMap.

SELECT 
  OSV.Name,
  APVV.[DisplayOrder],
  APVV.[Value],
  LLV.Label
  FROM [dbo].[AttributePicklistValueView] APVV
  LEFT JOIN [dbo].[OptionSetView] OSV ON APVV.OptionSetId = OSV.OptionSetId 
  LEFT JOIN [dbo].[LocalizedLabelView] LLV ON LLV.ObjectId = APVV.AttributePicklistValueId
  WHERE LLV.Label != ''
  ORDER BY OSV.Name, APVV.DisplayOrder


Reply
Andy Freeman responded on 14 Nov 2018 9:50 AM
My Badges

We had the same issue just now... option set was displayed correctly in Advanced Find, but value did not match in StringMap table. We had published all customizations after changing the global option set value. Our solution? Publish all customizations AGAIN.

Reply
Prateek responded on 19 Sep 2017 3:31 AM
My Badges
Suggested Answer

Hi,
Can you post your SQL query here? The 3 column that should suffice are AttributeName, AttributeValue and Value. Please query the stringmap table and do share the screenshot.
Techincally, this appears to be not feasible since CRM store data in SQL and not on frontend.

To debug it further, please enable SQL profiler and then try to set the optionset from UI. Cross validate in profiler whihc table is being hit while retrieving the optionset value.

Also, this can be raised with premier support team incase you table/views are corrupted. You can d othat from customerportal.

Thanks,
Prateek

Reply

SBX - Two Col Forum

SBX - Migrated JS