web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Records not coming in ...
Finance | Project Operations, Human Resources, ...
Answered

Records not coming in proper way in InventTable report.

(0) ShareShare
ReportReport
Posted on by Dineshkarlekar 1,836

I have converted this query 

 EcoResProductDimensionGroup             EcoResProductDimensionGroup;
        EcoResProductDimensionGroupproduct      EcoResProductDimensionGroupproduct;
        EcoResTrackingDimensionGroupItem	    EcoResTrackingDimensionGroupItem;
        EcoResTrackingDimensionGroup			EcoResTrackingDimensionGroup;
        ECORESSTORAGEDIMENSIONGROUPITEM			ECORESSTORAGEDIMENSIONGROUPITEM;
        ECORESSTORAGEDIMENSIONGROUP				ECORESSTORAGEDIMENSIONGROUP;
        ECORESPRODUCTTRANSLATION			    ECORESPRODUCTTRANSLATION;
        EcoResProductMaster                     EcoResProductMaster;
        INVENTMODELGROUPITEM					INVENTMODELGROUPITEM;
        INVENTITEMGROUPITEM						INVENTITEMGROUPITEM;
        EcoResproduct							EcoResproduct,EcoResproduct1;
        InventTable								InventTable;


	while select ItemId from InventTable   
            join   ECORESPRODUCT  where            
			                         ECORESPRODUCT.RECID == INVENTTABLE.PRODUCT
            join Name from  ECORESPRODUCTTRANSLATION where	  
		                           	ECORESPRODUCTTRANSLATION.PRODUCT   == ECORESPRODUCT.RECID

            join  ItemGroupId from INVENTITEMGROUPITEM  where
			                        INVENTITEMGROUPITEM.ITEMDATAAREAID  == INVENTTABLE.DATAAREAID

            join  ModelGroupId from INVENTMODELGROUPITEM   where									INVENTMODELGROUPITEM.ITEMDATAAREAID	== INVENTTABLE.DATAAREAID

            join  EcoResTrackingDimensionGroupItem   where                            								EcoResTrackingDimensionGroupItem.ITEMDATAAREAID == INVENTTABLE.DATAAREAID
           outer join Name from EcoResTrackingDimensionGroup where    
				EcoResTrackingDimensionGroupItem.TrackingDimensionGroup == EcoResTrackingDimensionGroup.RecId

             join ECORESSTORAGEDIMENSIONGROUPITEM     where   
			          ECORESSTORAGEDIMENSIONGROUPITEM.ITEMDATAAREAID == INVENTTABLE.DATAAREAID
           outer join Name from ECORESSTORAGEDIMENSIONGROUP where
			    ECORESSTORAGEDIMENSIONGROUPITEM.STORAGEDIMENSIONGROUP == ECORESSTORAGEDIMENSIONGROUP.RECID

			 

          outer join Name from EcoResProductDimensionGroup
           join EcoResProductDimensionGroupproduct where
                EcoResProductDimensionGroupproduct.ProductDimensionGroup == EcoResProductDimensionGroup.RecId
                 join EcoResProductMaster where
                 EcoResProductDimensionGroupproduct.Product == EcoResProductMaster.RecId
                 join  EcoResproduct1   where
                 EcoResProductMaster.RecId == EcoResproduct.RecId
          
 

        Info(strfmt("%1 - %2  -%3 - %4  -%5  -%6  - %7",InventTable.ItemId ,
			ECORESPRODUCTTRANSLATION.Name
            ,ECORESSTORAGEDIMENSIONGROUP.Name,
			INVENTITEMGROUPITEM.ItemGroupId,
			INVENTMODELGROUPITEM.ModelGroupId,
			EcoResTrackingDimensionGroup.Name,
			EcoResProductDimensionGroup.Name));

to this query but not getting proper data in report , multiple records are coming in my record.

Query q = new Query();
QueryBuildDataSource qbdsInventTable = q.addDataSource(tableNum(InventTable));
QueryBuildDataSource qbdsProduct     = qbdsInventTable.addDataSource(tableNum(EcoResproduct));
qbdsProduct.relations(true);
QueryBuildDataSource qbdsName        = qbdsProduct.addDataSource(tableNum(ECORESPRODUCTTRANSLATION));
qbdsName.relations(true);
QueryBuildDataSource  qbdsItem       = qbdsInventTable.addDataSource(tableNum(InventItemGroupItem));
qbdsItem.relations(true);
QueryBuildDataSource  qbdsGroup      = qbdsInventTable.addDataSource(tableNum(INVENTMODELGROUPITEM));
qbdsGroup.relations(true);
QueryBuildDataSource   qbdstracking = qbdsInventTable.addDataSource(tableNum(EcoResTrackingDimensionGroupItem));
qbdstracking.relations(true);
QueryBuildDataSource   qbdsTrackName = qbdstracking.addDataSource(tableNum(EcoResTrackingDimensionGroup));
qbdsTrackName.relations(true);

QueryBuildDataSource   qbdsStorage = qbdsInventTable.addDataSource(tableNum(ECORESSTORAGEDIMENSIONGROUPITEM));
qbdsStorage.relations(true);
QueryBuildDataSource  qbdsStorageName = qbdsStorage.addDataSource(tableNum(ECORESSTORAGEDIMENSIONGROUP));
qbdsStorageName.relations(true);

QueryBuildDataSource  qbdsproductmast = qbdsProduct.addDataSource(tableNum(EcoResProductMaster));
qbdsproductmast.relations(true);

QueryBuildDataSource  qbdsproductmastgrp = qbdsproductmast.addDataSource(tableNum(EcoResProductDimensionGroupproduct));
qbdsproductmastgrp.relations(true);

QueryBuildDataSource  qbdsproductmastdim = qbdsproductmastgrp.addDataSource(tableNum(EcoResProductDimensionGroup));
qbdsproductmastdim.relations(true);

this is what output I am getting

Invent-tbl-report.PNG 

Can you please help me out what is wrong with my report.

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 239,471 Most Valuable Professional on at

    It's immediately obvious that your X++ code generates a different SQL query than the one above. Your SQL code uses outer joins, but you don't have any in X++ code. You need to compare them more carefully.

  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,836 on at

    hii , martin thanks for reply

    actually invent table has only 1 item id  for each product and when i connect invent table with ecoresproduct and ecoresproducttranslation  to get name their i start getting multiple item for single item can you suggest where should i look into plz.

  • Martin Dráb Profile Picture
    Martin Dráb 239,471 Most Valuable Professional on at

    Yes, that's the expected behavior. For example, if the product has names in three languages (three records in EcoResProductTranslation), your query will return three records for the item. If you want just one record, both of your queries are wrong.

  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,836 on at

    so what I have to do to get 1 record  for item id  as u see there are  5 to 6 RECID a single product .

  • Martin Dráb Profile Picture
    Martin Dráb 239,471 Most Valuable Professional on at

    You need to think about what result you'll want to get.

    To continue my previous example, you're currently saying "give me all three records from EcoResProductTranslation". If this isn't what you want, you must decide what you do want. For example, maybe you want translation just in one language. If so, decide which one and filter by it. If you look into the relevant X++ code, you'll see that it uses the language defined in SystemParameters.SystemLanguageId as the default. I would recommend using that unless you have different business requirements.

  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,836 on at

    multproduct.PNG

    this is my sql query i want to convert it into x++ code , I want to get only one result for 1 item as you can see there are multiple results for 1 item in diffrent languages and recid are also common. what do you suggest for this. where should i look into.

  • Abhinay Tharwal Profile Picture
    Abhinay Tharwal 1,244 on at

    Hi Dinesh,

    Can you also post your expected output (maybe a screenshot of excel rows?)

    That would explain which names to pick up (it'd be helpful if you could tell us why)

    With regards,

    Abhinay

  • Martin Dráb Profile Picture
    Martin Dráb 239,471 Most Valuable Professional on at

    Dineshkarlekar, I think I already covered everything above. Please read my replies once more.

    Yes, I know that there may be records for several languages. That's what I said.

    I also told you what you need to do - think about your business requirements and decide what data you want to get. And I also gave you a concrete suggestion in my previous reply: filtering by the system language. That's often the right solution, but we obviously can't know your business requirements, because you didn't share them with us.

  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,836 on at

    thanks, martin 

    my business requirements is to get item id, name , item group id , model group id , product dimension group , storage dimension group , tracking dimension group in report  and i am checking the SQL query by breaking it as you said and  I got this result rest of join  i will check and  and let you know if i  have any doubt.

    .inventtbl1.PNG

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 239,471 Most Valuable Professional on at

    Your requirement doesn't cover the question of language, therefore you must stop coding. You need to go back to requirement gathering and ask for requirements regarding the product name language.

    We can't tell you which language your users need. You need to ask them. But you can pass to them my suggestion of using the system language.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the April Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 622

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 532 Super User 2026 Season 1

#3
CP04-islander Profile Picture

CP04-islander 430

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP
License usage reporting (in-app and PPAC) with Entra dynamic groups

Product updates

Dynamics 365 release plans