Product Search Report

(1) Share

Report

Posted on by Ayushaman

275

I am building a product search report in the X++ environment, pulling data from several tables including EcoResProductTranslation, TaxRateType, EcoResProduct, InventModelGroupItem, InventTableModule, and InventTable (with the possibility of additional tables). The report requires filters based also.

i want to get the joining so that i can fetch report including these fields Posting GL field i have to retireve it from inventitemgroup when the itemgroup is xyz or abc i want to fetch the details from purchase order grid where account type will be " Purchase expenditure " when itemgroup is xyz if it is abc then accounttye will be "Cost of purchase" i want to fetch their associated mainaccount.and for tax rate i want to fetch it from taxsolutionscope form through a hsncode i want igst-> rate value for my tax rate percentage.
Fields that i want to retireive

Product Code
Product Name
Item Group
Posting GL inventitemgroup
Posting GL Name
HSN/SAC Code
Tax Rate

Apologies for not being very brief. If you have any question feel free to ask .

Environment : D365 F&O

All responses (4)

Answers (1)

Sort by

Martin Dráb 241,511 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Do you have a question to ask? It's not clear what you want from us.

(Moved from Dynamics AX forum.)
Ayushaman 275 on at

Like (0)

Report
Copy link

Link copied!
Hi Martin !
Yes ! the thing i want to know is joining of these table to retrieve all the fields that i mentioned on the description for my product search report of released product.their might be some table i am missing as i find it out from front end.
the only joining i know is
FROM InventTable WITH (UPDLOCK) JOIN InventTableModule AS InventTableModule_Purch ON InventTable.ItemId = InventTableModule_Purch.ItemId JOIN InventItemLocation ON InventTable.ItemId = InventItemLocation.ItemId LEFT JOIN InventItemGroupItem ON InventTable.ItemId = InventItemGroupItem.ItemId

i want to know the joining so i can retrieve the data for my report.
Verified answer

Martin Dráb 241,511 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

An important thing to learn is that there are relations defined on tables. If you want to know, for example, how InventTable is related to EcoResProduct, open InventTable in the designer, expand Relations and find the right one:

You'll find the relation to TaxRateType at the same place.

Then you can use this information when writing select statements in X++. But often, you don't even need the details, you just need to know that the relation exists and then tell the system to use it. You can do in X++ when defining a query with Query* classes (QueryBuildDataSource has relations() method for this purpose), when defining a query in AOT etc.

Similarly, you'll find a relation to EcoResProduct on EcoResProductTranslation table. You may want to apply filter to LanguageId field (depending on your actual requirements).
Ayushaman 275 on at

Like (0)

Report
Copy link

Link copied!

Perfect !