Solved

Pulling base cost for a bundled item

  • 28 November 2023
  • 5 replies
  • 45 views
D
Rank
Userlevel 4
Badge
  • Del
  • Freshman I
  • 39 replies

I have a generic inquiry that shows the average cost of each finish good item.

Where I am running into an issue is we have some items that are “bundles”. 

For instance say I have a Red Widget that has an Inventory ID of RWidget, and the average cost of one Red Widget is $5.00, I also sell this item as a bundle of 3 and I an Inventory ID of RWidgetX3 for the bundled item.

NOTE: These bundles are not entered as a kit and are not a “stock item”, the Inventory ID of RWidgetX3 simply tells the picker to pull 3 of this item for the order. 

The issue I am running into is that these bundles don’t have an average cost in the system.

In my generic inquiry I created a column called X3 and I am using the following code to flag the bundled items.  =iif(Substring([InventoryItem.InventoryCD],7,2)='X3','True','False')   This is working as is expected, I get a list of all the Inventory IDs, and the ones that end in “X3” show as True in my X3 Column, and the rest show as False.

What I would like to do is expand the code so that if the Inventory ID ends in X3 that generic inquiry will pull the average cost for the base Inventory ID and multiply it by 3 to give the average cost for the bundle.

 

Example:

Inventory ID    Description                  Average Cost

RWidget          Red Widget                $5.00

RWidgetX3      Red Widget Bundle   $15.00

 

Thank you for any assistance...

 

 

icon

Best answer by Laura02 29 November 2023, 16:55

View original

5 replies

Laura02
Rank
Badge +18

Hello,

One idea is to add an Attribute to your Bundle Items, such as ‘Multiplier’.

Your generic inquiry can multiply the Multiplier Attribute field times the Average Cost and to show the average cost per bundle. Your GI will now accommodate bundles of different quantities and print the correct average costs.

However, there is no relation from RWidget to RWidget3x.  How will Acumatica know, from which Stock Item, to pull the Average cost?  

Related Items tab of Non-stock Items could be a way to link the Stock item to the non-stock item. A second Attribute on the non-stock Item may also be used to hold the ID of the related stock item, where the average cost will be taken.

 

 

Laura

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
D
Rank
Userlevel 4
Badge

Laura02

Thank you for your reply, we will look into using the Related Items Tab of the Non-Stock bundle and see if we can get that to work.

Thank you again!

D
Rank
Userlevel 4
Badge

I have added base Inventory IDs to the Related Items tab on several of the Non Stock bundled items.

I add the INRelatedInventory table to my generic inquiry and am able to now pull the RelatedInventoryID and display it when the generic inquiry runs. 

The next question is how do I use the RelatedInventoryID to pull the Average Cost from the INItemCost table since the INItemCost  is already linked to the PX.Objects.IN.InventoryItem table to pull average cost for Finish Goods items? 

I’ve attached a copy of the generic inquiry as it is now. Or is it that I will need to have two generic inquiries one for Finish Goods items and one from None stock items?

Thanks again for your assistance!

1 Attachment
Laura02
Rank
Badge +18

Hello,

I think what you need to do is connect the same INItemCost a second time, giving it an Alias name like RelatedItemCost.

Here is an example where I added the same table a second time with Alias:

Then relate to the second copy by joining the RelatedInventoryID field to INItemCost’s inventory id and pull the cost.

Laura

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
D
Rank
Userlevel 4
Badge

Laura02

That worked perfectly!!

Thank you for all your help!!

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved