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

[Blog bot]

Источник: http://domhk.blogspot.com/2015/11/us...nector_20.html
==============

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"

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


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

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.

Источник: http://domhk.blogspot.com/2015/11/us...nector_20.html