Dyota's blog

Dynamics F&O: GlobalOptionsetMetadata

Many tables in Dynamics 365 F&O have columns that are enums rather than stored values. Joining onto the correct option set is very cumbersome to write by hand.

The process I use is the following:

  1. Download all of the options sets ever, from GlobalOptionsetMetadata (gosm)
  2. For every table in gosm, construct a view that has every option set possible on it joined on, as comments. This way, they can be un-commented if they actually need to be used.

It's a bit brute-force, because it will generate option sets for every join ever, but it's convenient.

First, download all of the option set definitions as a csv file. The following PowerShell script will pick up on these definitions.

select
    [EntityName]        -- this is the source table
    , [OptionSetName]   -- this is the name of the enum
    , [Option]          -- this is the value of the enum, that will be on the source table. This has to be enclosed in brackets because Option is a protected keyword
    , [LocalizedLabel]  -- this is the full text that we want to pick up
from dbo.GlobalOptionsetMetadata
order by [EntityName]
    , [OptionSetName]
    , [Option]
    , [LocalizedLabel]

Next, target the csv file using thie PowerShell script:

using namespace System.Text; #for StringBuilder

# "os" stands for Option Set
$os = cat .\GlobalOptionsetMetadata.csv | 
    convertfrom-csv -delim ","

$db = 'database' # change this
$schema = 'dbo'  # change this
$source = 'GlobalOptionsetMetadata'
$dest = '.\optionset'

$columns = [StringBuilder]::new()
$joins = [StringBuilder]::new()

$os |
    group EntityName |
    % {
        $entity = $_.Name
        ($_.Group | group OptionSetName).Name |
            % {
                $_
                $thisOS = $_ 
                $jointable = "os_$thisOS"
                [void] $columns.AppendLine( "`t-- , $jointable.LocalizedLabel AS $($thisOS)name")
                [void] $joins.AppendLine( "`t-- LEFT JOIN [$db].[$schema].[$source] AS $jointable ON base.$thisOS = $jointable.[Option] AND $jointable.EntityName = '$entity' AND $jointable.OptionSetName = '$thisOS'" )
            }
        
        $filename = "$dest\$entity.sql"
        $sql = "CREATE OR ALTER VIEW [$schema].[$entity]`n`tAS`nSELECT`n`t*`n$($columns.ToString())  FROM [$db].[$schema].[$entity]`n$($joins.ToString())"

        $sql > $filename
        [void] $columns.Clear()
        [void] $joins.Clear()
    }

The end result looks like this. Below is GeneralJournalAccountEntry, as an example.

CREATE OR ALTER VIEW [dbo].[generaljournalaccountentry]
	AS
SELECT
	*
	-- , os_assetleasepostingtypes.LocalizedLabel AS assetleasepostingtypesname
	-- , os_assetleasetransactiontype.LocalizedLabel AS assetleasetransactiontypename
	-- , os_iscorrection.LocalizedLabel AS iscorrectionname
	-- , os_iscredit.LocalizedLabel AS iscreditname
	-- , os_postingtype.LocalizedLabel AS postingtypename
	-- , os_skipcreditcalculation.LocalizedLabel AS skipcreditcalculationname
	-- , os_sysdatastatecode.LocalizedLabel AS sysdatastatecodename
  FROM [database].[dbo].[generaljournalaccountentry]
	-- LEFT JOIN [database].[dbo].[GlobalOptionsetMetadata] AS os_assetleasepostingtypes ON base.assetleasepostingtypes = os_assetleasepostingtypes.[Option] AND os_assetleasepostingtypes.EntityName = 'generaljournalaccountentry' AND os_assetleasepostingtypes.OptionSetName = 'assetleasepostingtypes'
	-- LEFT JOIN [database].[dbo].[GlobalOptionsetMetadata] AS os_assetleasetransactiontype ON base.assetleasetransactiontype = os_assetleasetransactiontype.[Option] AND os_assetleasetransactiontype.EntityName = 'generaljournalaccountentry' AND os_assetleasetransactiontype.OptionSetName = 'assetleasetransactiontype'
	-- LEFT JOIN [database].[dbo].[GlobalOptionsetMetadata] AS os_iscorrection ON base.iscorrection = os_iscorrection.[Option] AND os_iscorrection.EntityName = 'generaljournalaccountentry' AND os_iscorrection.OptionSetName = 'iscorrection'
	-- LEFT JOIN [database].[dbo].[GlobalOptionsetMetadata] AS os_iscredit ON base.iscredit = os_iscredit.[Option] AND os_iscredit.EntityName = 'generaljournalaccountentry' AND os_iscredit.OptionSetName = 'iscredit'
	-- LEFT JOIN [database].[dbo].[GlobalOptionsetMetadata] AS os_postingtype ON base.postingtype = os_postingtype.[Option] AND os_postingtype.EntityName = 'generaljournalaccountentry' AND os_postingtype.OptionSetName = 'postingtype'
	-- LEFT JOIN [database].[dbo].[GlobalOptionsetMetadata] AS os_skipcreditcalculation ON base.skipcreditcalculation = os_skipcreditcalculation.[Option] AND os_skipcreditcalculation.EntityName = 'generaljournalaccountentry' AND os_skipcreditcalculation.OptionSetName = 'skipcreditcalculation'
	-- LEFT JOIN [database].[dbo].[GlobalOptionsetMetadata] AS os_sysdatastatecode ON base.sysdatastatecode = os_sysdatastatecode.[Option] AND os_sysdatastatecode.EntityName = 'generaljournalaccountentry' AND os_sysdatastatecode.OptionSetName = 'sysdatastatecode'

#dynamicsfo