Summary of Query in an Infolog, based on specific field values.

(0) Share

Report

Posted on by Community Member

Greetings World,

I am looking to create a button that displays an info box.

In that info box I would like to display a summary of the filtered results of a ProdTable, by an InventDim field InventLocationId(warehouse).

For instance, if my table data is as follows then the resulting infolog would result in "Line 1 - Warehouse One - 2 Total", "Line 2 - Warehouse Two - 3 Total", "Line 3 - Warehouse Three - 1 Total".

Work Order	Warehouse	Status
1	one	created
2	one	created
3	two	created
4	two	created
5	two	created
6	three	created

I am running AX 2009, and have not been able to find any code online that i can rework to find a solution to this problem.

Any pointers would be much appreciated.

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

Suggested answer

Jonathan Halland 11,310 on at

Like (0)

Report

Hi

Could you assist us with understanding what you are struggling with in specific? The requirement seems a little contrary to the way things would normally work (i.e. showing a filtered enquiry form). But the basic steps would be as below, please mention which items in specific you do not know how to accomplish:

1. Add a button to your form.

2. Override the clicked method.

3. in the Clicked method construct an X++ query using the Query and related classes including datasources for ProdTable and InventDim.

4. Add a queryRange to the InventDim table datasource created above, passing in your required value (possibly from the current selected record on your parent form.

5. Execute the query using a query run, extract the prodTable record from the query and inside your query loop and use info("") to display the details

Query reference

msdn.microsoft.com/.../aa638454.aspx;MSPPError=-2147217396

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
So here is where i need help. All my results are 0 (when in fact if i filter just in the grid i should get 50-80 results per warehouse(InventLocationID))

public void getdataforthisform() { Query q; Queryrun qr; QueryBuildDataSource qbds; QueryBuildRange qbr; int cWH1, cWH2, cWH3; ; q = new query(); qbds = q.addDataSource(tablenum(ProdTable)); qbr = qbds.addRange(fieldnum(Prodtable,ProdStatus)); qbr.value(queryvalue("Created")); qbds = qbds.adddatasource(tablenum(InventDim)); qbds.relations(true); qr = new queryrun(q); while (qr.next()) { switch (InventDim.InventLocationId) { Case "Warehouse1" : cWH1 ++; break;
Case "Warehouse2" : cWH2 ++; break; Case "Warehouse3" : cWH3 ++; break; } } statictext1.text(int2str(cWH1)); statictext2.text(int2str(cWH2)); statictext3.text(int2str(cWH3)); }

Was this reply helpful? Yes No
Verified answer

Mea_ 60,284 on at

Like (0)

Report
Hi itemLouks,

First of all you need to populate InventDim variable, so change your code to next:

while (qr.next()) { inventDim = qr.get(tableNum(InventDim)); switch (inventDim.InventLocationId) { case "Warehouse1" : cWH1 ++; break; case "Warehouse2" : cWH2 ++; break; case "Warehouse3" : cWH3 ++; break; } }

Also you may use group by to agregate query by InventLocationId, so SQL will calculate quantities for you.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you so much! Its so obvious to me now that I see it. My method works great now i cannot wait to put it to use tomorrow morning.

Was this reply helpful? Yes No