Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Display Inventory On H...
Microsoft Dynamics AX (Archived)

Display Inventory On Hand on Sales Lines

(0) ShareShare
ReportReport
Posted on by antlar 55

In AX2012 we are trying to get the Physical Available Quantity from InventSum to be shown at the SalesLine (in the grid) on the form SalesTable.

 

We've tried the solution on this URL: http://truongcongda.blogspot.se/2010/04/display-inventtory-onhand-in-sales.html, which is based on display() methods. It works, however it's too slow for us.

Since the display method was too slow we tried to join the InventSum table as a joined Data Source on the SalesTable. Our problem is that the join can't be done on two fields (ItemId + InventDim), instead it only joins on the ItemId field which  results in multiple rows on the sales table for one single sales line.

Anyone have solution, hint or suggestion which we could implement without having to redesign the whole Form and also get some speed to the lookup the quantity? Unfortunately it's not ideal for us to have the quantity in a pane or any other way except on the line it self.

*This post is locked for comments

  • antlar Profile Picture
    antlar 55 on at
    RE: Display Inventory On Hand on Sales Lines

    Thank you all. I'm overwhelmed with the high quality answers and smart suggestions.

    Robert: Thank you for your suggestion however if I start adding group by's to the datasource it would be required to group on all fields returned from all datasources in the query generated. In this example when trying this suggestion I got the following:

    Column "SALESLINE.DATAAREAID" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

    Andre: Indeed it would be preferable in many situations however in our situation we need this quick overview what we can delivery now and also by that directly take action on that information.

    Gustavo: Great thank you, skipping inventdimparm, caching and setting index gave me acceptable performance.

    Thank you all.

    Best Regards

    Lars

  • Verified answer
    Gustavo Bagno Profile Picture
    Gustavo Bagno on at
    RE: Display Inventory On Hand on Sales Lines

    I would seriously consider doing some refactoring on the code that web page has shown. You dont need to use InventDimParm and such. Go straight to InventSum table using ItemId and InventDimId.

    You can create a display method within SalesLine table and create your own "select" at InventSum.

    A simple select using the Macro #InventSumFields (as in Select #InventSumFields from InventSum...) and the ItemId/InventDimId combination (taken from SalesLine record) would suffice.

    Plus, run the same select on SQL Server using Query Analyzer to check if there's need to create e new Non-Clustered index, as it can greatly improve reads to the database.

    Doing that, plus caching the method would greatly reduce the performance penalty.

    Cheers.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,280 Super User 2024 Season 2 on at
    RE: Display Inventory On Hand on Sales Lines

    I do agree with the suggestion of Robert. In stead of place it at the bottom, you can also consider building a factbox with this information in AX2012. Then also per line the on hand inventory is shown for the item/inventdim combination.

  • Suggested answer
    Robert Allen Profile Picture
    Robert Allen 60 on at
    RE: Display Inventory On Hand on Sales Lines

    What you're asking to do is show analytical data on a journal.  You're combining data entry with business intelligence.  This sort of activity always results in deterioration of performance because of the inherent slowness of analytic actions.

    What I would suggest is putting the on hand display field/method on the bottom of the form so it only shows for the active sales line.  This way the user can see the on hand inventory for the line active line and the performance hit will be very small (it will only happen when a line is selected).  Display methods should rarely be used as grid fields since they're slow, not sortable, not filterable, and unintuitive to the user.

    To make the second option you tried work, you need to add the InventDim datasource to the form again and link it to the InventSum table via the inventDimId (which should be joined to the SalesLine table via the ItemId) .  You can try adding this line to the init method of your "InventDim_2" datasource (the new one, not the existing one):

    this.query().dataSourceName('InventDim_2').addGroupByField(fieldNum('InventDim', 'InventLocationId'));

    this.query().dataSourceName('InventDim_2').addGroupByField(fieldNum('InventDim', 'InventSiteId'));

    this.query().dataSourceName('InventDim_2').addGroupByField(fieldNum('InventDim', 'InventSerialId'));

    (plus any other dimensions you use that you want to group on)

    Just make sure to set the InventSum_1.AvailPhysical.AllowEdit(false); so they cant edit it.

  • antlar Profile Picture
    antlar 55 on at
    RE: Display Inventory On Hand on Sales Lines

    Thank you Bilal,

    We've tried and succeeded in caching the display method however we feel it has only a small performance increase probably because it still needs to do a select for each unique salesline (and we only have unique inventdim + itemid combinations on the sales lines).

    BR

    Lars

  • Bilal Issa Profile Picture
    Bilal Issa 4,370 on at
    RE: Display Inventory On Hand on Sales Lines

    Hi Lars,

    Please try to cache the display method as shown in the link below.

    msdn.microsoft.com/.../aa596691(v=ax.10).aspx

    Regards,

    Bilal

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!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans