Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

fetching all records from inventsum according to inventtable.

Posted on by Microsoft Employee

Hi!

I am developing a customized report in which i need to add a filter of item group id.

and fetch availphysical from inventsum and minimum and maximum quantity from reqitem table.

so I built a query 

while select ItemId from inventItemGroupItem 

where inventItemGroupItem.ItemGroupId == parmItemGroupId.parmObject()

join sum(AvailPhysical) from Inventsum

where inventsum.ItemId == inventItemGroupItem.ItemId

join MinInventOnhand,MaxInventOnhand from reqItemTable

where reqItemTable.ItemId == inventItemGroupItem.ItemId

here the problem is there are every record in inventtable but not all added to the inventory

and the table inventsum only contains the records which are added to the inventory and reqitemtable only provides the records of which minimum and maximum quantity has been added. so when i run this query it only fetches the records present in all three tables. otherwise it doesnt bring the other all records in invent table.

iwant to fetch all the records from inventtable and of which item the min max and avail physical is not available it show them blank but must fetch the data according to inventtable.

what should i do please help.

*This post is locked for comments

  • Suggested answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,786 on at
    RE: fetching all records from inventsum according to inventtable.

    Make InventTable the first table in your query and use left outer joins for the other tables.

    Also don't forget about InventDim.  You wouldn't want to show the Min/Max quantities for one warehouse along with the available physical quantity for another.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,232 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,064 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans