Morning all. The majority of our products are assemblies which we manufacture on demand. This means when an order is placed it goes onto backorder. I am trying to work out how to run a saved search to show the total required inventory items and qty from all the BOM revisions collated together. in other words, a report to show what Inventory I need to fulfil all backorder items across all BOMs? Any ideas on how I can best achieve this?

Do your assemblies have more than 1 level of subassemblies? If not you can do an item saved search and join to the SO transaction. This will allow you to join the member items needed to the SO lines. But it doesn't work if you have subassemblies of subassemblies

Hi, Yes we can have multi level subassemblies

For this we had to use a suite analytics workbook. The saved search only allows 1 level of joins so you would need to use a workbook where unlimited joins is possible Or you would have to script something to store a flattened BOM on every assembly or calculate a flattened BOM per sales order

@AI1 with the workbooks, you can recursively get all subassemblies of an assembly? I've seen this pop up a few times over the last few months and you def can't do it via searching unless you want to run out of governance real fast.

Yes we were able to do this for all level of subassemblies. Some of our assemblies have 8 - 10 levels of subassembly deep. I think their were some limitations but it was possible. Let me check with the guy who did it on our team to confirm all the details

Yeah that would be awesome i'm honestly pretty curious about it. I haven't gotten into the workbooks much at all.

Ok after discussing more with the guy who built it, it seemed to stop at 5 levels of subassembly. It might be because we don't go lower than that very often so finding the ones that were 8 -10 levels was hard in all the data, but it just seemed to stop calculating the newer levels after 5 so he quit trying to go deeper

@AI1 Were you able to create the workbook using the Advanced Bill of Materials? If so, would you mind sharing the joins you used in the dataset? I can't seem to join to the subassemblies.

We just kept adding new columns joining to the Item member multiple times

ok. That is helpful. thanks. We use advanced BOM and I was trying to start with Item as the base record and couldn't join to anything. I then tried Bill of Materials and Bill of Materials Revision as the base record and couldn't get the joins either. Maybe I need to try with a transaction.