14.09.2017, 13:11 | #1 |
Участник
|
domhk: Use PowerShell and Business Connector to retrieve data from Dynamics AX (AX2009) - Part 2
Источник: 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 # 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 |
|
|
|