Batch Manufacturing App with multiple process steps

[blaineiler]

Hello,

I am trying to develop a batch manufacturing app. We produce various juices and beverages. For each product, we have a bill of materials which might include simple raw materials (e.g. bottles, sugar, water, etc.), but could also include WIP items (e.g. lemon juice), since we are purchasing lemons and juicing them on site.

We produce to order, but the orders contain the finished products (e.g. case of lemonade). The lemonade BOM would include water, sugar, bottles, boxes, and lemon juice.

In this example, we have to track 3 processes -- juicing the lemons, mixing the lemon juice with the other ingredients, and filling them into bottles. We combine the packaging in the last step since it happens at the same time.

Where it can get tricky is that we might also have orders for bottles of just lemon juice. The BOM for that item would be just the lemon juice as well as the packaging. There would be a juicing step to make that product, but no mix step. The lemon juice that goes into this product would be the same as the lemon juice that goes into the lemonade.

What I'm trying to achieve is setting this up to where we can select what order line items we want to produce, and then combine them to create manufacturing orders for each step of the process (e.g. juicing, mixing, filling/packing). I have an order entry screen that automatically creates the line items so we can assign case quantities. I also have item configuration, and can add BOMs pretty easily. I'm having a hard time figuring out how to go from order quantities and consolidating that down into manufacturing orders that takes into account the multi levels of the BOMs. I would appreciate any insight anyone has to offer.

Thanks!

[1minManager]

I think I understand your process, but I'm having a hard time understanding what your after.  Are you saying that you want to put in 60 cases of Lemonade and get an itemised BOM for that?  Where each item (bottles, lemon, sugar etc) is added in to the app as a line item which can then be used for ordering?

Simon, 1minManager.com

[blaineiler]

Sort of. I can easily get an itemized BOM for a certain number of cases for one product. What I'm after is being able to select multiple Sales Order Line rows (each with an item and quantity, potentially multiple lines with the same item), and total up the unique list of BOM items across all. 

[TeeSee1]

This may not satisfy your requirements completely but here is an idea you can tweak and play with.. 

Say you have the following rows in T1

_ROWNUMBER	MATERIAL	QUANTITY
1	Lemon	10
2	Sugar	100
3	Lemon	20
4	Sugar	200
5	Lemon Juice	30

If you want to reduce the above to the following set of rows in T2

_ROWNUMBER	MATERIAL	QUANTITY
1	Lemon	30
2	Sugar	300
3	Lemon Juice	30

Then do the following

1.  Create a VC ([Summed Quantity]) in T1 with the expression

   SUM(SELECT(T1[Quantity],[MATERIAL]=[_THISROW].[MATERIAL]))

   You can add more complex conditions to more precisely identify which rows should be consolidated (I am sure you have more columns in your real app). This expression can be embedded in the Action in Step 2 but may be too bulky in production. Although it may be advantageous in performance (just a guess, you need to test it to be sure..) 
2. Create an action (say "Reduce") in T1 to add a new row to T2 using [MATERIAL] and [Summed Quantity] and set the following execution condition. This will ensure that the action is applied to only one row among the set of rows identified by the selection criteria.

   [id] = MINROW("T1","_RowNumber",[MATERIAL]=[_thisrow].[MATERIAL])

3. To call the action Reduce, since you cannot use manual selection for bulk actions (the action is not valid for all the rows), create a Data: execute an action on a set of rows action (you can filter rows to which to apply the action by whatever conditions that fit your requirements) and attache it to anywhere you deem convenient.

I can easily see that you have to add more complex selection criteria in your production app but this idea may help you get you what you want ... or not.

I would also be interested to see suggestions from other community members because I believe this is something a lot of people would like to solve.

[Elvic]

I was facing the same problem in quite similar app I am working on. Not sure if your question still actual but I solved it by creating a VC which marks as True the item which is unique in some list. Then I processed only those lines and assigned them with sum() in the Action. Let me know if still actual and I will share details.