Harness the power of PowerShell and the Business Connector too.

Hi Everyone. My name is Jason Larson. I am a Premier Field Engineer with the Dynamics AX team in the central region. I was recently asked how we could compare data in a text file outside of AX with data in AX.

The business scenario for the customer was to compare financial dimensions between ERP systems but in this example I am comparing users in the userinfo table with a list of users in a text file.

 I thought of a few ways to do this:

1) Import the external file into an intermediate custom table in the AX database and use T-SQL to compare.

2) Import the external file using DIXF(DMF) and perform the compare using x++.

3) Using the business connector and connect into AX using PowerShell and perform the compare in a PowerShell Script by pulling the AX data out.

I thought option 3 was interesting. Using the BC and PowerShell seemed like a logical fit to me. Here is what I came up with:

File on the filesystem is c:\userlist.txt

This is what the file contains:

Admin

Guest

Jason

wfexc

The table in AX I am comparing against in this example is the userinfo table. The table contains this:

Admin

Guest

MSDAXRea

wfexc

 #Here is the script.

#Read in the userlist file and create an arraylist without a fixed size.

$userlist = Get-Content c:\userlist.txt
$array = New-Object System.Collections.ArrayList

Add-Type -Path "C:\Program Files\Microsoft Dynamics AX\60\BusinessConnector\Bin\Microsoft.Dynamics.BusinessConnectorNet.dll"
$ax = new-object Microsoft.Dynamics.BusinessConnectorNet.Axapta
$ax.logon('','','','','','')
$b = $ax.CreateAxaptaRecord("userinfo")
$b.ExecuteStmt("select id from %1")
while($b.found){
    $array.add($b.get_field("id")) | out-null
    $b.next() | out-null
}

$a = "<style>"
$a = $a + "BODY{background-color:AliceBlue;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
$a = $a + "</style>"

compare-Object $userlist $array | Sort { $_.InputObject.diff } | convertto-html -head $a -body "<H2><= is found in external file. => Is only found in AX. </H2>" | out-file "c:\out.html"

The results:

The script above will match on different case. To change that behavior use -caseSensitive like so on the compare-object line

Compare-Object $userlist $array -caseSensitive | Sort {$_.InputObject.diff } | convertto-html -head $a -body "<H2><= is found in external file. => Is only found in AX. </H2>" | out-file "c:\out.html"