I am stalling with this as it may require some VBA I am not too familiar with unless there is another easier way.
Here's what I have so far:
Code:
Component_ID |
MOQ |
Multi |
Planned_Date |
Stock |
Supply |
Demand |
Balance |
Order Qty |
LBalance |
Expr2 |
Expr1 |
item1 |
10000 |
10000 |
16-Aug-19 |
0 |
44000 |
0 |
44000 |
|
|
|
0 |
item1 |
10000 |
10000 |
21-Aug-19 |
0 |
0 |
43554 |
446 |
|
|
|
0 |
item1 |
10000 |
10000 |
01-Sep-19 |
0 |
0 |
31110 |
-30664 |
40000 |
9336 |
0.934 |
0 |
item1 |
10000 |
10000 |
01-Sep-19 |
0 |
0 |
518.5 |
-31182 |
40000 |
8818 |
0.882 |
0 |
The [order qty] in line 3 is correct, this is a previously calculated field inserted in a table this query is pulling from.
The [order qty] in line 4 is incorrect, this calculation is made line by line but is only correct on the first line, L3 in that example.
Expr1 is a calculated field in that query, here's the formula
Code:
Expr1: IIf((([Balance]+[Order Qty])/[MOQ])<0,[MOQ]+(-Int(-((Abs([Balance]+[Order Qty])-[MOQ])/[MOQ]))*[Multi]), IIf(((Abs([Balance]+[Order Qty]))/[MOQ])=1,[MOQ],"0"))
LBalance and Expr2 are also calculated but is not necessary, it's just for ease of building at this time then I'll discard them
Expr1 represents what should be reordered, as soon as the new balance [LBalance] becomes negative again. Expr2 shows the ratio of stock vs MOQ or Multi.
When that ratio is <0, the formula will calculate what needs to be ordered. If, on line 4, the balance LBalance was -15,000 for example, Expr1 would show 20,000 to be reordered which is Roundup(15,000/10000)*10000
This is working fine but it's confusing because it is showing information that is not needed or is incorrect. [order qty] = 40,000 L4 is irrelevant and incorrect.
What I would like to have is a column that would show
L1 = 0 balance is 44,000 so nothing to order
L2 = 0 balance is 446 so nothing to order
L3 = 3 balance is -30664 so we are ordering 10,000(MOQ) + 3x 10,000(Multi) = 40,000
L4 = 0 balance is 8818 so nothing to order
L5 (not shown) = 10,000 balance is -3000 so we are ordering 10,000(MOQ)
That information eventually needs to go on a report and lines where [balance]>0 and LBalance>0 will be filtered out, I just want to show lines when stock becomes negative and the suggested order quantity.
Thank you