Friday, November 20, 2015

Use PowerShell and Business Connector to retrieve data from Dynamics AX (AX2009) - Part 2

In my last post I mentioned the reason I use Powershell to draw data from AX. What I want to achieve is ease of drawing data from AX and the ability to save and re-run queries. I wrote a basic script module to do this.
The main utility from the module is a new cmdlet New-AXSelectStmt. It is used to pass X++ select statement to AX and get a list of PSObject as return.
A simple example would be:
                
New-AXSelectStmt CustTable -stmt "SELECT * FROM %1" 
<# Result
 # AccountNum     : 1302
 # Name           : Turtle Wholesales
 # Address        : 123 Peach Street Aberdeen, MD 21001 US
 # Phone          : 123-555-0168
 # TeleFax        : 321-555-0157
 # InvoiceAccount : 
 # CustGroup      : 10
 # LineDisc       : 
 # PaymTermId     : N060
 # CashDisc       :  
 #>
It is also possible to run select statement with multiple tables:
                
New-AXSelectStmt SalesTable,SalesLine -stmt "SELECT * FROM %1 JOIN %2 WHERE %1.SALESID == %2.SALESID"  -top 50
<# Result
 # SalesTable_SalesId           : SO-100004
 # SalesTable_SalesName         : Contoso Retail San Diego
 # SalesTable_Reservation       : 0
 # SalesTable_CustAccount       : 3001
 # SalesTable_InvoiceAccount    : 3001
 # SalesTable_DeliveryDate      : 7/2/2008 12:00:00 AM
 # SalesTable_DeliveryAddress   : 456 Peach Road
 #                                San Diego, CA 92114
 #                                US
 # SalesTable_URL               : 
 # SalesTable_PurchOrderFormNum : 
 # SalesTable_SalesTaker        : 
 # SalesLine_SalesId            : SO-100004
 # SalesLine_LineNum            : 5
 # SalesLine_ItemId             : 1401
 # SalesLine_SalesStatus        : 3
 # SalesLine_LedgerAccount      : 
 # SalesLine_Name               : Car Audio System Model 01
 # SalesLine_ExternalItemId     : 
 # SalesLine_TaxGroup           : Resale
 # SalesLine_QtyOrdered         : 1
 # SalesLine_SalesDeliverNow    : 0
 #>

In terms of actual usage, I have a case where a user's ask about the net weight shown on an invoice. In the invoice printout, a line of 10pcs is shown to weight 0.197g. However, on the item details form the net weight per piece is 0.02. The user thinks there is an error.
I used the query below to quickly check the actual net weight stored in the database and show user what happened.
                
New-AXSelectStmt InventTable -stmt "SELECT * FROM %1 WHERE %1.ItemId == 'itemid'" -fieldLists "ItemId,NetWeight,ItemNAme,ItemGroupId" -showLabel
<# Result
 # Item number  Net weight Item name                                      Item group 
 # -----------  ---------- ---------                                      ---------- 
 # itemid           0.0197 High-Definition Digital Video Recorder Model 01       DVR 
 #>
In addition, after getting the return result I am able to do all sorts of thing. I can write scripts for scheduled run, I can also:
                
# Export the result to a csv
New-AXSelectStmt CustTable -stmt "SELECT * FROM %1" -top 10 | Export-CSV "C:\Temp\custList.csv"

# Email the exported file
Send-MailMessage -From $mailFrom -To $mailTo -Subject $subject -Body $body -Attachments "C:\Temp\custList.csv" `
                 -SmtpServer $SMTPServer -Port $SMTPPort -Credential $mycreds -BodyAsHtml -UseSsl -Attachment

Well, that's all! Happy DAXing...until next time. =]

This posting is provided "AS IS" with no warranties, and confers no rights.

Wednesday, November 18, 2015

Use PowerShell and Business Connector to retrieve data from Dynamics AX (AX2009)

Not long ago I started a new position as an in-house AX specialist. Part of my job is to provide support to users on their AX issues. Here I face a challenge that, due to organization structure, I don't have direct access to the SQL database. If I want to quickly check some data in AX I am then left with the table browser. Another option is to write X++ jobs but then I'll have to take care of showing the data (e.g. using infolog) myself. In short, it's not ideal.

I missed the ability to write ad-hoc scripts and the ability to conveniently re-run them (Used to be T-SQL scripts for me). Then I thought I can use PowerShell! I tried to look up the internet for PS scripts to access AX and saw this. Unfortunately, I couldn't get it to work. Well, time to write my own. =D

Here I connect to AX using the business connector. First I have to get the Microsoft.Dynamics.BusinessConnectorNet.dll and put it somewhere. Load it up and create an Axapta object from it.
   
$targetPath = "C:\Temp\Microsoft.Dynamics.BusinessConnectorNet.dll"

[reflection.Assembly]::Loadfile($targetPath) | Out-Null
$ax = new-object Microsoft.Dynamics.BusinessConnectorNet.Axapta          

Then I connect to AX and retrieve data.
   
$company = ""
$language = ""
$aos = ""
$config = ""
$ax.logon($company,$language,$aos,$config)
             
$t1 = $ax.CreateAxaptaRecord("SalesTable")
$t2 = $ax.CreateAxaptaRecord("SalesLine")
$ax.ExecuteStmt("SELECT * FROM %1 JOIN %2 WHERE %1.SalesId == %2.SalesId && %1.SalesId == 'SO-100004'",$t1,$t2)  
Do 
{ 
    $t1.get_field("salesId") + " - " + `
    $t2.get_field("itemId") + "; " + `
    $t2.get_Field("SalesQty") + "; " + `
    $t2.get_Field("LineAmount")
} 
while ($t1.Next())

The scripts above returns below when run against the demo database.
SO-100004 - 1151; 2; 121.38
SO-100004 - 1151; 2; 124.42
SO-100004 - 1161; 5; 197.95
SO-100004 - 1161; 7; 333.83
SO-100004 - 1401; 1; 204.39
SO-100004 - 1402; 1; 257.53
SO-100004 - 1606; 2; 800

What's written above works but it just run a very specific query. I'll share a basic script module I put together in my next post. It'll have better reusability.

This posting is provided "AS IS" with no warranties, and confers no rights.