functions/get-d365tablefield.ps1

<#
.SYNOPSIS
Get a field from table
 
.DESCRIPTION
Get a field either by FieldName (wildcard search allowed) or by FieldId
 
.PARAMETER TableId
The id of the table that the field belongs to
 
.PARAMETER Name
Name of the field that you are looking for
 
Accepts wildcards for searching. E.g. -Name "Account*"
 
Default value is "*" which will search for all fields
 
.PARAMETER FieldId
Id of the field that you are looking for
 
Type is integer
 
.PARAMETER DatabaseServer
The name of the database server
 
If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN).
 
If Azure use the full address to the database server, e.g. server.database.windows.net
 
.PARAMETER DatabaseName
The name of the database
 
.PARAMETER SqlUser
The login name for the SQL Server instance
 
.PARAMETER SqlPwd
The password for the SQL Server user.
 
.PARAMETER TableName
Name of the table that the field belongs to
 
Search will only return the first hit (unordered) and work against that hit
 
.PARAMETER IncludeTableDetails
Switch options to enable the result set to include extended details
 
.PARAMETER SearchAcrossTables
Switch options to force the cmdlet to search across all tables when looking for the field
 
.EXAMPLE
Get-D365TableField -TableId 10347
 
Will get all field details for the table with id 10347
 
.EXAMPLE
Get-D365TableField -TableName CustTable
 
Will get all field details for the CustTable table
 
.EXAMPLE
Get-D365TableField -TableId 10347 -FieldId 175
 
Will get the details for the field with id 175 that belongs to the table with id 10347
 
.EXAMPLE
Get-D365TableField -TableId 10347 -Name "VATNUM"
 
Will get the details for the "VATNUM" that belongs to the table with id 10347
 
.EXAMPLE
Get-D365TableField -TableId 10347 -Name "VAT*"
 
Will get the details for all fields that fits the search "VAT*" that belongs to the table with id 10347
 
.EXAMPLE
Get-D365TableField -Name AccountNum -SearchAcrossTables
 
Will search for the AccountNum field across all tables.
 
.NOTES
The cmdlet supports piping and can be used in advanced scenarios. See more on github and the wiki pages.
 
#>

function Get-D365TableField {
    [CmdletBinding(DefaultParameterSetName = 'Default')]
    param (
        [Parameter(Mandatory = $true, ParameterSetName = 'Default', ValueFromPipelineByPropertyName = $true, Position = 1 )]
        [int] $TableId,

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 2 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableName', Position = 2 )]
        [Parameter(Mandatory = $true, ParameterSetName = 'SearchByNameForce', Position = 1 )]
        [string] $Name = "*",

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', ValueFromPipelineByPropertyName = $true, Position = 3 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableName', ValueFromPipelineByPropertyName = $true, Position = 3 )]
        [int] $FieldId,

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 4 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableName', Position = 4 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'SearchByNameForce', Position = 3 )]
        [string] $DatabaseServer = $Script:DatabaseServer,

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 5 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableName', Position = 5 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'SearchByNameForce', Position = 4 )]
        [string] $DatabaseName = $Script:DatabaseName,

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 6 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableName', Position = 6 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'SearchByNameForce', Position = 5 )]
        [string] $SqlUser = $Script:DatabaseUserName,

        [Parameter(Mandatory = $false, ParameterSetName = 'Default', Position = 7 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableName', Position = 7 )]
        [Parameter(Mandatory = $false, ParameterSetName = 'SearchByNameForce', Position = 6 )]
        [string] $SqlPwd = $Script:DatabaseUserPassword,

        [Parameter(Mandatory = $true, ParameterSetName = 'TableName', Position = 1 )]
        [string] $TableName,

        [Parameter(Mandatory = $false, ParameterSetName = 'Default')]
        [Parameter(Mandatory = $false, ParameterSetName = 'TableName')]
        [switch] $IncludeTableDetails,

        [Parameter(Mandatory = $true, ParameterSetName = 'SearchByNameForce', Position = 2 )]
        [switch] $SearchAcrossTables
    )
    BEGIN {}
    
    PROCESS {
        if (!$script:IsAdminRuntime -and !($PSBoundParameters.ContainsKey("SqlPwd"))) {
            Write-Host "It seems that you ran this cmdlet non-elevated and without the -SqlPwd parameter. If you don't want to supply the -SqlPwd you must run the cmdlet elevated (Run As Administrator) or simply use the -SqlPwd parameter" -ForegroundColor Yellow
            Write-Error "Running non-elevated and without the -SqlPwd parameter. Please run elevated or supply the -SqlPwd parameter." -ErrorAction Stop
        }

        if ($PSCmdlet.ParameterSetName -eq "TableName") {
            $TableId = (Get-D365Table -Name $TableName | Select-Object -First 1).TableId
        }

        $sqlCommand = Get-SQLCommand $DatabaseServer $DatabaseName $SqlUser $SqlPwd

        if ($SearchAcrossTables.IsPresent) {
            $sqlCommand.CommandText = (Get-Content "$script:PSModuleRoot\internal\sql\get-alltablefields.sql") -join [Environment]::NewLine
        }
        else {
            $sqlCommand.CommandText = (Get-Content "$script:PSModuleRoot\internal\sql\get-tablefields.sql") -join [Environment]::NewLine
            $null = $sqlCommand.Parameters.Add("@TableId", $TableId)    
        }

        $datatable = New-Object system.Data.DataSet
        $dataadapter = New-Object system.Data.SqlClient.SqlDataAdapter($sqlcommand)
        $dataadapter.fill($datatable) | Out-Null

        foreach ($obj in $datatable.Tables.Rows) {
            if ($obj.FieldId -eq 0) {
                $TableName = $obj.AotName

                continue
            }

            if ($PSBoundParameters.ContainsKey("FieldId")) {
                if ($obj.FieldId -NotLike $FieldId) { continue }
            }
            else {
                if ($obj.AotName -NotLike $Name) { continue }
            }
            $res = [PSCustomObject]@{
                FieldId   = $obj.FieldId
                FieldName = $obj.AotName
                SqlName   = $obj.SqlName
            }

            if ($IncludeTableDetails.IsPresent) {
                $res | Add-Member -MemberType NoteProperty -Name 'TableId' -Value $obj.TableId
                $res | Add-Member -MemberType NoteProperty -Name 'TableName' -Value $TableName
            }
            if ($SearchAcrossTables.IsPresent) {
                $res | Add-Member -MemberType NoteProperty -Name 'TableId' -Value $obj.TableId
            }

            $res
        }
    }

    END {}
}