Update Custom Table when there is an update in InventSum

(0) Share

Report

Posted on by Von

108

I need to update a custom table when updates happened on InventSum table.

I also need to insert new field in InventSum and the data will come from InventTrans. Is that possible? I tried adding it and setting the value of the new field in InventSum Method initFromInventTrans but the new field keeps getting blank value. I tried recompiling and restarting AOS but nothing change.

For you to pictured it out. This is what I'm planning to make. Some item on my inventory has parent Item. So meaning you can attach many items in one single item. You can also attach item that is already attached on the other item.

The above module has already been done and everything is made separately from the original released product module. And we are now able to use all the items in transaction like Sales Order.

But what we are trying to achieve is to also monitor the inventory of the Item that has parentItem.

And what I'm thinking is to add ParentItem field on the InventSum.

Eg.

ItemId ParentItem

Item A Item C

Item B Item C

Item B Item D

Do you think this is a good idea? Do you have any suggestion about this?

Thanks

I'm using AX2012 R3.

*This post is locked for comments

I have the same question (0)

All responses (18)

Answers (2)

Community Member on at

Like (1)

Report
Copy link

Link copied!

Hi Von,

I am trying to simulate the scenario. Hopefully I can contribute an input if that is possible or if there is an alternative.

Cheers!

Was this reply helpful? Yes No
Mea_ 60,286 on at

Like (0)

Report
Copy link

Link copied!

Hi Von,

initFromInventTrans looks right method for me, however, please be aware that it's used only when new record is created, if you already have inventsum record for given itemId and inventDImId AX won't call it.

DId you try to create new item and test your solution with it?

Was this reply helpful? Yes No
Von 108 on at

Like (0)

Report
Copy link

Link copied!

Thank you Kristoffer. Hoping you get a good result. I'm new to AX and still learning.

Yes Ievgen I'm aware about that. ItemId and InventDimId is the current primary key in InventSum. But we want it to be temId, InventDimId and our newfield "parentItemId" so we can monitor items that has parentItem.

So in InventSum Table

ItemId ParentItemId InventDimId

Item A Item C 1

Item A Item B 1

the above entry should be allowed to insert in InventSum table since they have different ParentItemId but thats not what is happening. Because the only validation to insert a new record is ItemId and InventDimId only. So meaning only one record will be inserted regardless of the parentItemId.

Was this reply helpful? Yes No
Martin Dráb 239,040 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

It is possible, but I would strongly recommend to avoid adding such logic to InventSum. Make sure it's absolutely necessary.

It's a key table, heavily used by warehouse-related operations, yet it uses pessimistic locking to ensure data consistency. It can easily become the bottleneck of the whole system. That's why Microsoft introduced optimizations such as using InventSumDelta table and direct DB queries in InventUpdateOnhand class. Your modification would have to deal with this extra complexity and would actually slows down what Microsoft's tried to speed up.

What's the business requirement behind parent items? Maybe you need Bills of Materials or something...

Was this reply helpful? Yes No
Von 108 on at

Like (0)

Report
Copy link

Link copied!

We already tried BOM but it does not fit our requirements. We have different types of product and we categorized it as

Product - Regular Product

Machine - Can be a Parent Item

Accessories - Can have a parent Item.

In every Accessories Parent Item(Machine) is required

Single Accessory can also have multiple Parent Item (Machine) because we sell Machine that has common accessories.

Like I said attaching accessories to a Machine is a new module that we made. So it has no effect on the current process. We just used this for our reference.

Now in Sales Order/Purchase Order if we choose a Machine Item we automatically populate all the accessories attached to that machine to the salesline. This customization is already working.

But we are unable to determine the inventory stock of the accessories per machine because AX does not have that. It only summarize the stock per ItemId and InventDimId. We need to add the ItemParentId or the ItemId of the Machine.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 239,040 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

It sounds to me that your "parent item" can be consider as a type of warehouse location, i.e. an inventory dimension. And then you can follow normal AX logic, because InventSum obviously supports dimensions.

Was this reply helpful? Yes No
Von 108 on at

Like (0)

Report
Copy link

Link copied!

Sorry Martin but we cannot do that. The parent Item is also an actual Item not only for tagging. for example you have Item A and you attached Item B and Item C on it. When you used Item A in salesline it will also populate Item B and Item C so your salesline would be Item A, Item B, Item C. We're done already on this part. What we need now is the inventory part of the attached item. And I'm thinking of changing the InventSum instead of creating my own. Our original plan is to replicate inventsum and put there all accessories then manually update each field related to quantity. The problem is we have no idea where to put our code to update the new table. That's why I come up to the new idea of modifying the InventSum instead.

Was this reply helpful? Yes No
Martin Dráb 239,040 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Yes, I understand that the parent item is an actual item; I took it into account when writing my previous reply. I see you don't want to do that, but I disagree that you can't.

Well, just take my warning that you're going to dangerous waters and your design can have negative impact on the whole system. I thought it's important to point it out if you're saying that you're new to AX.

Was this reply helpful? Yes No
Von 108 on at

Like (0)

Report
Copy link

Link copied!

Thank you Martin. Yes None of us is an expert in AX and we only rely in this community and in the basic knowledge we earned on the training. Hoping someone out there already made this kind of customization so they can guide us. Regarding with your suggestion in warehouse location we already used the warehouse as the transacting department or the department who sells the product. We used it for us to easily generates report per department.

Was this reply helpful? Yes No
Mariano Gracia on at

Like (0)

Report
Copy link

Link copied!

Some years ago I needed to add a new field to InventSum table (and I did success), be careful because AX updates InventSum records using sql commands (take a look at the method sqlUpdateInventSumStr in InventUpdateOnhand class), so to update your custom table when AX updates the InventSum needs to be aware of this (and I think it won't be easy)

Was this reply helpful? Yes No