Accessing Optionset Display values and their corresponding numeric values with the hidden Stringmaps entity

by

One of the lesser-known but incredibly powerful tables in Dataverse is the “Stringmaps” table. This table holds a treasure trove of information about optionsets and their options, making it a valuable resource for developers and administrators alike. In this blog post, we’ll dive deep into the “Stringmaps” table, understanding its structure and how it helps in mapping display names to optionset values.

Understanding Optionsets in Dataverse

Let’s quickly look into what optionsets are in Dataverse. Optionsets are a versatile way to represent a set of pre-defined values for a field.

Optionsets use the following parts:

  1. @odata.etag: This is an HTTP header used in the OData protocol (which the Dataverse Web API follows) to manage concurrency control. It represents the entity tag associated with the resource (in this case, a row in the “Stringmaps” table) and is used to ensure that multiple clients are not updating the same resource simultaneously. It helps prevent data conflicts and maintains data integrity.
  2. displayorder: This column in the “Stringmaps” table is used to specify the order in which optionset values should be displayed in user interfaces. It determines the sequence in which the values appear when presenting a list of options to users. Display order ensures consistency and user-friendliness in how options are presented.
  3. value: This column holds the numeric values associated with each option within an optionset. It is the actual value that gets stored in the database when a user selects an option. In code and data manipulation, this numeric value is often used for programmatic processing.
  4. attributename: This column indicates the name of the optionset attribute to which the option belongs. It identifies the specific optionset to which each row in the “Stringmaps” table corresponds. For example, it might contain “Project Status” if the optionset represents project statuses.
  5. versionnumber: This is a system column in most Dataverse tables, including “Stringmaps.” It’s used for optimistic concurrency control, ensuring that updates are only applied if the data hasn’t changed since it was last retrieved. It helps prevent data overwrites in concurrent operations.
  6. langid: This column represents the language code associated with the optionset display name. It’s particularly useful in multi-language environments, allowing the same optionset to have different display names based on the language preference of the user. (examples would be 1031 for German language or 1033 for English United States)
  7. objecttypecode: This column specifies the unique code that identifies the type of entity to which the optionset attribute belongs. Different entities in Dataverse have distinct object type codes, making it clear which entity is associated with the optionset.
  8. organizationid: This column identifies the organization or tenant within Dataverse. It’s essential in multi-tenant environments to ensure data separation between different organizations sharing the same Dataverse instance.
  9. stringmapid: This is the primary key of the “Stringmaps” table, serving as a unique identifier for each row. It’s used as a reference point for querying, updating, or deleting specific optionset mappings.

The “Stringmaps” Table: An Overview

The “Stringmaps” table is a system table that stores all the information related to optionsets and their options within a Dataverse environment. This table is designed to maintain the mapping between the user-friendly display names and the underlying optionset values. Here are some key points to know about the “Stringmaps” table:

  1. Central Repository: The “Stringmaps” table serves as a central repository for optionset information. It includes data for all the optionsets and their associated options in your Dataverse environment.
  2. Structure: Just as a recap, the table has several columns, but the most important ones for our discussion are:
    • Value: The name of the optionset, such as “Project Status.”
    • AttributeValue: The numeric value associated with each option.
    • AttributeName: The user-friendly display name for each option, like “Not Started.”
  3. Mapping: The “Stringmaps” table effectively maps the display values to their corresponding numeric values. This mapping is crucial when you need to work with optionsets programmatically, as it allows you to easily convert between user-friendly names and numeric values.

You can make use of the api to see the stringmaps entity for your environment:

https://yourdomainname.crm4.dynamics.com/api/data/v9.2/stringmaps

The result should look something like this:

Practical Applications

Understanding the “Stringmaps” table can be incredibly beneficial in real-world scenarios:

  1. Data Migration: When you’re migrating data from one environment to another, the “Stringmaps” table helps ensure that optionset values are correctly mapped, preventing data inconsistencies.
  2. Custom Code: In custom code or plugins, you might need to interact with optionset values. By querying the “Stringmaps” table, you can easily retrieve the numeric values associated with display names or vice versa.
  3. Reporting: When building reports or dashboards, you can use the “Stringmaps” table to display user-friendly optionset values instead of numeric codes, improving the clarity of your reports.

For an example, we can use it in Power Automate Flow. With using filters and select columns, we only get the relevant information and it significally improves our performance.

Table Name “String Maps” (Englisch) = “Zeichenfolgenzuordnungen” in German (What a word…). Quick Tip: If you are unsure on how the table is called in your language, you can input “stringmaps” as custom value as the table name. Once you test the flow, it will automatically be replaced by the local name.

You can use the Power Automate Flow .json shema below to skip a test run:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "@@odata.type": {
                "type": "string"
            },
            "@@odata.id": {
                "type": "string"
            },
            "@@odata.etag": {
                "type": "string"
            },
            "@@odata.editLink": {
                "type": "string"
            },
            "attributevalue@OData.Community.Display.V1.FormattedValue": {
                "type": "string"
            },
            "attributevalue": {
                "type": "integer"
            },
            "value": {
                "type": "string"
            },
            "stringmapid@odata.type": {
                "type": "string"
            },
            "stringmapid": {
                "type": "string"
            }
        },
        "required": [
            "@@odata.type",
            "@@odata.id",
            "@@odata.etag",
            "@@odata.editLink",
            "attributevalue@OData.Community.Display.V1.FormattedValue",
            "attributevalue",
            "value",
            "stringmapid@odata.type",
            "stringmapid"
        ]
    }
}

Conclusion

The “Stringmaps” table in Dataverse is a hidden gem that holds the key to managing optionsets effectively. It simplifies data management, migration, and development tasks by providing a clear mapping between user-friendly display names and their underlying optionset values. Whether you’re a developer, administrator, or data analyst, understanding the “Stringmaps” table is a valuable skill in working with Dataverse.

So, the next time you find yourself working with optionsets in Dataverse, remember the power of the “Stringmaps” table and how it can make your life easier by handling the mapping between display names and optionset values seamlessly.

Leave a comment