How Option Sets Work in Dataverse
Option Sets (also called Choice columns) in Dataverse store data as integer values but display human-readable labels to users. For example, a Status Reason field might store the value '1' but display 'Active' in the Dataverse UI.
This dual nature presents a challenge for reporting: the numeric value is stable and reliable for filtering, but the label is what users need to see in reports.
What Gets Synchronized
Data Mission Sync synchronizes both the numeric value and the display label for every Option Set column. For each Option Set, two columns are created in your target table:
| Column | Description |
| {columnname} | INT — The numeric value stored in Dataverse (e.g., 1, 2, 100000000) |
| {columnname}_label | NVARCHAR — The display label at the time of sync (e.g., 'Active', 'Inactive') |
Example: Status Code
For an account record with Status Reason of 'Active', you'll see:
| accountid | statuscode | statuscode_label |
| abc-123-def | 1 | Active |
Best Practices for Querying
• Use the numeric column ({columnname}) for filtering and joins — it's more efficient and stable
• Use the label column ({columnname}_label) for display in reports and dashboards
• If labels change in Dataverse, the _label column will update on the next sync of that record
| 💡 Note: Option Set labels are captured at sync time. If an administrator changes the label text in Dataverse without modifying any records, existing _label values won't update until those records are modified and synced again. |