Any Saved Search Experts out there that maybe can help me figure out what is going on. I am trying to determine the number of total kits sold over a 6 month period using an Item to Transaction saved search as I need to write this value to an item record. My issue is, when I try to use the Sum Summary to get the total I get 56 instead of 14 for the Number of Kits Per Invoice column. I determined that it is giving me 56 due to it taking the total of invoices (13) and multiplying it by the Average Component Usage Per Invoice (4.3). I have tried removing the Average Column and I still get 56, why and how is the Saved Search doing that, is there a fix?

Have you tried summing the Number of Kits Per Invoice column? You mentioned this is an "Item to Transaction" saved search - is it an "Item" search or a "Transaction" search?

Yes, I have summed it, counted and tried multiple different things, and it is an Item Saved Search.

You might have better luck with a transaction search. I know kit/packages can be a headache in saved searches, but summing based on transaction lines ("Main Line = F") could get you there, if you can get the criteria to filter to the right items and time frame? Apologies if you've already tried this, or if I'm misunderstanding.

Yes, but how do you populate a Custom Field on an Item Record using a Transaction saved search, you can't, unless there is a secret way!

Make a transaction saved search. Type = Invoice Mainline = False Item:Type = Kit/Package In the results do Date Document Number Line ID Item Quantity (and whatever else you want) Then do a SUM on quantity and group whatever else you want

Yes, that works, but you can not use a transaction saved search and populate a field on the Item Record.

oh you are trying to source this into an item field?

Umm, maybe I had the filter set incorrectly, I might have had just Internal ID, not Item:InternalID. Let me try that.

make sure it is Item: Internal ID, not Item: internal ID (number)

While that works if I want to know the demand for an item as a whole, I need to know the demand for the item when it is sold as part of a Kit or Assembly, not by itself, for example, I need to know if Item A, has been sold as part of Kit B or Assembly C

Doesn’t the location on hand - location committed give you that?

I don't think I am explaining it very well, let me try again - I need to know the total units of a component that are sold as part of a Kit or Assembly over a period of 6 months. So if Kit A, consists to 2 of Inventory Item B, and there are 13 invoices over the period of 6 months, and 1 of those invoices there was 2 of Kit A sold, I need to get the total of 14 Units of Inventory Item B were sold. Hopefully that makes more sense

You might be able to do this using the member items join...but if you have more than 1 level of subassembly it won't work

Yes, we do have subassemblies. and I do think the workorder route will work, as we typically do not use workorders to build a kit, they are just 2 Inventory or Assembly items that are picked from current inventory.

I would do the following then Create a Transaction saved search Type = Item Fulfillment and work order issue or completion (depending on if you use backflush) main line = false date = within the last 6 months Account type = COGS (might have to use other current asset) Results Date Doc number line item