Let's Get Social!
Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Stock Out day
 Go New Search Notify Tools Reply
 [SOLVED]Stock Out day
Member
 posted January 11, 2016 04:36 PM
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

Petter

This message has been edited. Last edited by: <Emily McAllister>,

WebFocus 7703
Windows, All Outputs

 Posts: 11 | Registered: November 09, 2012 IP
Guru
 posted January 11, 2016 07:19 PM Hide Post
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

 Posts: 272 | Location: Brazil | Registered: October 31, 2006 IP
Virtuoso
 posted January 12, 2016 07:44 AM Hide Post
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 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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007

 Posts: 2232 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013 IP
Member
 posted January 12, 2016 10:02 AM Hide Post
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.

DATE ITEM NEED INVENTORY STOCK
01/11/15 A01 10 21 11
01/12/15 A01 5 21 16
01/15/15 A01 8 21 13
01/16/15 A01 3 21 18

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

 Posts: 11 | Registered: November 09, 2012 IP
Guru
 posted January 12, 2016 10:09 AM Hide Post
Use LAST at your COMPUTE like MartinY suggested.

WebFOCUS 8.1.05 / APP Studio

 Posts: 272 | Location: Brazil | Registered: October 31, 2006 IP
Guru
 posted January 12, 2016 10:30 AM Hide Post
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

 Posts: 398 | Registered: February 04, 2008 IP
Guru
 posted January 12, 2016 10:44 AM Hide Post

WebFOCUS 8.1.05 / APP Studio

 Posts: 272 | Location: Brazil | Registered: October 31, 2006 IP
Member
 posted January 12, 2016 11:20 AM Hide Post
Thank Martin, Ricardo and Guru!!

I used your suggestions and finished my report

WebFocus 7703
Windows, All Outputs

 Posts: 11 | Registered: November 09, 2012 IP
Guru
 posted January 12, 2016 11:25 AM Hide Post