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>,

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!

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

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.

Use LAST at your COMPUTE like MartinY suggested.

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

Thank Martin, Ricardo and Guru!!

I used your suggestions and finished my report

Nice to read that.
Please update subject to [SOLVED].

Have a nice work-day.