Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Stock Out day
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]Stock Out day
 Login/Join
 
Member
posted
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, 2012Reply With QuoteReport This Post
Guru
posted 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, 2006Reply With QuoteReport This Post
Virtuoso
posted 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 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.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, 2013Reply With QuoteReport This Post
Member
posted 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, 2012Reply With QuoteReport This Post
Guru
posted Hide Post
Use LAST at your COMPUTE like MartinY suggested.


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
Guru
posted 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, 2008Reply With QuoteReport This Post
Guru
posted Hide Post
Good One


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted 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, 2012Reply With QuoteReport This Post
Guru
posted Hide Post
Nice to read that.
Please update subject to [SOLVED].

Have a nice work-day.


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Stock Out day

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.