a little more complex Query and sending it to Excel - need help

This question is answered

Okay – I admit it, I am lost.  I spent days reading just about everything I can find but still don’t know what to do – I asked this question earlier, and thought I could figure it out with the answers given, but I am still too new to AX and just do not know enough to get there.  

There are two parts to the question – first is how to get the query to work, and second is how to allow someone to run it to a spreadsheet.

A)

I have 3 files – let’s say I am pulling Partnumber L12345-67

InventTable  (ItemID, PrimaryVendor )

InventTableModule (ItemId, ModlueType, Price)

EcoResProductionTranslation (Name, Description)

A simple linking the 3 files together in a Query is easy – I can get

Part Vendor Module Price Name Description

L12345-67   V1234 Inventory 100.00 PartName PartDescrtipion

L12345-67   V1234 Purchase Order    95.00 PartName PartDescrtipion

L12345-67   V1234 Sales Order 125.00 PartName PartDescrtipion

But what confuses me is the data in the InventTableModule I want in the query.  There are 3 records for part L12345-67.  One with Module type Inventory, one for Sales Order and one for Purchase Order.  What I want to do is have it all come out in one line instead of 3.

Part Vendor Inv.Price PO.Price SO.Price Name Desc

L12345-67 V1234 100.00 95.00 125.00 PartName PartDescription

How do I do that?  I know that there are methods created for each of those prices, but how do I use them in a query to get my one line instead of 3?

B)

Now on to question 2 – once I get the query built – how do I get it so the user can dump the data it to the spreadsheet from AX – I remember reading something about putting queries in the favorites so they can run them from there, but not sure how to go about doing it.   I don’t want to use SSRS, since it is going to be more of a grid than a report, and will not fit on a page in one line (yes, there will be more fields in the query than I show here).

I know it is better to teach someone to fish instead of fish for them, but I learn by examples – and I am so clueless right now that hints don’t work yet.  I am ordering a few books that I hope will help me but need to get this done before the books arrive (let alone have time to read through them).

Verified Answer
  • Hi there,

    1) You should have 3 instances of InventTableModule table in your query. The 1st one will filter by ModuleType = Purch, 2nd one by ModuleType = Sales, 3rd one by ModuleType = Invent. You can modify their names to make it easier to read. (e.g. InventTableModule_Purch, InventTableModule_Sales, InventTableModule_Invent)

    So basically, imagine you are having 3 InventTableModule tables instead of 1. Then you should have a query ready.

    2) This is easy in AX2012. Build a form with the query from step 1, which has a grid displaying the data. Add a command button in the action pane (or action strip). There is a standard action of "Export to Microsoft Excel". Open the form, click the button, it's done.

    In fact, even without the button, you can simply click Ctrl-T to trigger the export to excel action.

    Good luck.

    My blog | PBC

    This forum post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

All Replies
  • Hi there,

    1) You should have 3 instances of InventTableModule table in your query. The 1st one will filter by ModuleType = Purch, 2nd one by ModuleType = Sales, 3rd one by ModuleType = Invent. You can modify their names to make it easier to read. (e.g. InventTableModule_Purch, InventTableModule_Sales, InventTableModule_Invent)

    So basically, imagine you are having 3 InventTableModule tables instead of 1. Then you should have a query ready.

    2) This is easy in AX2012. Build a form with the query from step 1, which has a grid displaying the data. Add a command button in the action pane (or action strip). There is a standard action of "Export to Microsoft Excel". Open the form, click the button, it's done.

    In fact, even without the button, you can simply click Ctrl-T to trigger the export to excel action.

    Good luck.

    My blog | PBC

    This forum post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

  • That finally makes sense to me!  Simple and workable - while I was trying to think of hard complex ways of doing the same thing (it is great having experienced minds to pick).  Guess my brain has been on overload trying to learn too much stuff in too short a time.    Now to see if I can put it all together. Thank you very much!!!