I have one table that shows the material needs by item and date, and another table that shows on-hand inventory by item. I need to find out when we are running out of stock. I want to clarify that I only have access to Web Focus reporting tool. Our company does not use Web Focus maintain. Here is an simplified example of the tables.
TABLE: MATNEEDS This table shows the quantity needed for each part at a giving date. ======================================================================================== ITEM DATE NEED UNIT A01 01/11/15 10 EA A01 01/12/15 5 EA A01 01/15/15 8 EA A01 01/16/15 3 EA
BBB 01/11/15 8 EA BBB 01/12/15 3 EA BBB 01/13/15 3 EA BBB 01/14/15 2 EA
Table: OHHAND This table show the quantity of each item we have on our warehouse ================================================================================= ITEM INVENTORY UNIT A01 21 EA BBB 12 EA
So, I want to join both tables and then find out when we run out of stock.
ITEM DATE NEED INVENTORY UNIT INVENTORY - NEED A01 01/11/15 10 21 EA 21 - 10 = 11 A01 01/12/15 5 21 EA 11 - 5 = 6 ==> Notice that 11 comes from the previous calculation 21-10 = 11 A01 01/15/15 8 21 EA 6 - 8 = -2 ==> At this point, I know I will run out of stock on 1/15/13 A01 01/16/15 3 21 EA -2 -3 = -5
BBB 01/11/15 8 12 EA 12 -8 = 4 BBB 01/12/15 3 12 EA 4 - 3 = 1 ==> Notice that 4 comes from the previous calculation 12-8 = 4 BBB 01/13/15 3 12 EA 1 - 3 = -2 ==> At this point, I know I will run out of stock on 1/13/13 BBB 01/14/15 2 12 EA -2 - 12 = -14
Can someone show me how to accomplish the calculation described in the INVENTORY-NEED column above? Or any other way to get the same info.
Thank you
PetterThis message has been edited. Last edited by: <Emily McAllister>,
WebFocus 7703 Windows, All Outputs
January 11, 2016, 07:19 PM
Ricardo Augusto
Hi Petter,
Maintain is used to create forms to insert or update records. BTW, we can do that using FOCUS too.
I will try to resume my suggestion to solve your report:
1-
JOIN CLEAR * JOIN ITEM IN MATNEEDS TO ITEM IN OHHAND AS J1 END
TABLE FILE MATNEEDS SUM NEED INVENTORY COMPUTE STOCK/D8 = INVENTORY - NEED; BY ITEM BY DATE END
2- Add a conditional styling to highlight STOCK column when it is LT 0
3- Read the manual about JOIN, TABLE, DEFINE and COMPUTE. The verbs too, of course.
I hope it helps. Good lucky!
WebFOCUS 8.1.05 / APP Studio
January 12, 2016, 07:44 AM
MartinY
Hi Petter, here a sample :
TABLE FILE GGSALES
SUM BUDUNITS
BY REGION
BY PRODUCT
SUM UNITS
BY REGION
BY PRODUCT
BY DATE
ON TABLE HOLD AS TMP1 FORMAT FOCUS
END
DEFINE FILE TMP1
REMAIN /I8 = IF LAST REGION EQ REGION THEN (IF LAST PRODUCT EQ PRODUCT THEN LAST REMAIN - UNITS ELSE BUDUNITS - UNITS) ELSE BUDUNITS - UNITS;
END
TABLE FILE TMP1
SUM BUDUNITS
UNITS
REMAIN
BY REGION
BY PRODUCT
BY DATE
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
DEFMACRO=OUTOFSTOCK,
MACTYPE=RULE,
WHEN=REMAIN LE 0,
$
TYPE=DATA,
COLUMN=REGION,
COLOR=RED,
MACRO=OUTOFSTOCK,
$
TYPE=DATA,
COLUMN=PRODUCT,
COLOR=RED,
MACRO=OUTOFSTOCK,
$
TYPE=DATA,
COLUMN=DATE,
COLOR=RED,
MACRO=OUTOFSTOCK,
$
TYPE=DATA,
COLUMN=BUDUNITS,
COLOR=RED,
MACRO=OUTOFSTOCK,
$
TYPE=DATA,
COLUMN=UNITS,
COLOR=RED,
MACRO=OUTOFSTOCK,
$
TYPE=DATA,
COLUMN=REMAIN,
COLOR=RED,
MACRO=OUTOFSTOCK,
$
END
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
January 12, 2016, 10:02 AM
Petter
Thank you for the suggestion but it does not work. The STOCK field resulting from the COMPUTE command shows INVENTORY-NEED for each line. It does not take into account that some of the inventory was consumed on the previous day.
It should be as follow: DATE ITEM NEED INVENTORY STOCK 01/11/15 A01 10 21 11 01/12/15 A01 5 21 6 01/15/15 A01 8 21 -2 01/16/15 A01 3 21 -5
On 1/12/15, the stock is 6 EA. It is the result of subtracting he previous day remaining stock (11) and the current day need (5). Same process for the next day and so on.
WebFocus 7703 Windows, All Outputs
January 12, 2016, 10:09 AM
Ricardo Augusto
Use LAST at your COMPUTE like MartinY suggested.
WebFOCUS 8.1.05 / APP Studio
January 12, 2016, 10:30 AM
RSquared
Petter,
Try something like this
quote:
JOIN CLEAR * JOIN ITEM IN MATNEEDS TO ITEM IN OHHAND AS J1 END TABLE FILE MATNEEDS PRINT NEED INVENTORY BY ITEM BY DATE ON TABLE HOLD AS HOLDITEM END DEFINE FILE HOLDITEM INV_ON_HAND/D20=IF ITEM EQ LAST ITEM THEN INVENTORY - LAST NEED ELSE INVENTORY; END TABLE FILE HOLDITEM PRINT NEED INV_ON_HAND COMPUTE INV_LEFT/D20=INV_ON_HAND - NEED; BY ITEM BY DATE
WF 7.6.11 Oracle WebSphere Windows NT-5.2 x86 32bit
January 12, 2016, 10:44 AM
Ricardo Augusto
WebFOCUS 8.1.05 / APP Studio
January 12, 2016, 11:20 AM
Petter
Thank Martin, Ricardo and Guru!!
I used your suggestions and finished my report
WebFocus 7703 Windows, All Outputs
January 12, 2016, 11:25 AM
Ricardo Augusto
Nice to read that. Please update subject to [SOLVED].