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.

Comments

  1. Nice post.. you did good work.

    ReplyDelete
  2. Amazing tutorials! Thank you for sharing such a useful information with the readers of your blog and helping us to learn how to use PowerShell and Business Connector data extraction from Microsoft ax dynamics. I'm just starting to use these features and your explanations are very clear and informative :)

    ReplyDelete

Post a Comment