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 PS C:\> Get-D365TableField -TableId 10347 Will get all field details for the table with id 10347. .EXAMPLE PS C:\> Get-D365TableField -TableName CustTable Will get all field details for the CustTable table. .EXAMPLE PS C:\> 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 PS C:\> Get-D365TableField -TableId 10347 -Name "VATNUM" Will get the details for the "VATNUM" that belongs to the table with id 10347. .EXAMPLE PS C:\> 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 PS C:\> Get-D365TableField -Name AccountNum -SearchAcrossTables Will search for the AccountNum field across all tables. .EXAMPLE PS C:\> Get-D365TableField -TableName CustTable -IncludeTableDetails Will get all field details for the CustTable table. Will include table details in the output. .NOTES Tags: Table, Tables, Fields, TableField, Table Field, TableName, TableId Author: Mötz Jensen (@splaxi) 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 = $false, 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 { $UseTrustedConnection = Test-TrustedConnection $PSBoundParameters $SqlParams = @{ DatabaseServer = $DatabaseServer; DatabaseName = $DatabaseName; SqlUser = $SqlUser; SqlPwd = $SqlPwd } $sqlCommand = Get-SqlCommand @SqlParams -TrustedConnection $UseTrustedConnection } PROCESS { if ($PSCmdlet.ParameterSetName -eq "TableName") { $TableId = (Get-D365Table -Name $TableName | Select-Object -First 1).TableId } if ($SearchAcrossTables) { $sqlCommand.CommandText = (Get-Content "$script:ModuleRoot\internal\sql\get-alltablefields.sql") -join [Environment]::NewLine } else { $sqlCommand.CommandText = (Get-Content "$script:ModuleRoot\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) { $res | Add-Member -MemberType NoteProperty -Name 'TableId' -Value $obj.TableId $res | Add-Member -MemberType NoteProperty -Name 'TableName' -Value $TableName } if ($SearchAcrossTables) { $res | Add-Member -MemberType NoteProperty -Name 'TableId' -Value $obj.TableId } $res } } END {} } |