First: I'm a newbie with zero experience and about as little training as you can get. Training cost money so therefore is not an option with some companies...

I have a report where I'm trying to get dates greater than 22 months old to highlight in yellow and greater than 24 months old to highlight in red.

Also, I have a report that I know the "NEED DATE" in the shop and a projected lead time from the vendor. I want to show a date needed to order (Need date - # days lead time - 7 days for processing req and PO).

Again, I know this is probably InfoAssist 101 but when you get zero training, you waste a lot of time on things like this.

Thanks!

This message has been edited. Last edited by: Pops,

Pops,

Can you post your date's format; i.e., YYMD, MDYY, etc; and maybe a few examples of what you're trying to accomplish? Are the dates greater than 22 and 24 months using today's date as a reference? The "date needed to order" could be calculated by creating a "define" field and using a function called DATEADD. Hope this helps some.

Convert your date format first using HDATE(YOURDATE, 'YYMD') then try DATEADD again. For instance:
NewDate/YYMD (or MDYY) = HDATE(YOURDATE, 'YYMD');
DateCalc/YYMD = DATEADD(NewDate,'D', 7);

Today = &YYMD
Date22M/YYMD = DATEADD(Today, 'M', -22)

Let me know. Thanks

Luiz

Luiz, I did the conversion using HDATE, to establish the MDYY settings. I should have been more clear in my statment above. The System Date is YYMMDDHHMMSS but I use MDYY.

I thought maybe that was the reason for the error.

Date22M shows up fine, I just cannot get the "condiditional formatting" to work.

DEFMACRO=Condition_1, COLOR=RGB(255 255 0), STYLE=BOLD, BACKCOLOR=RGB(255 0 0), WHEN=N6 LT MONTHS22, $

Yet every date shows up with the special format not just the dates that are LT MONTHS22, I'll attempt to link a sample of the report (at least I did that right!!!)

Pops,

I've had some issues with the traffic lights, and have a case opened with IB for that. Let me run a quick test, and see if I can duplicate your issue. I'll keep you posted. Thanks

Try the following:
a)Make your first condition LT M22 background red
b)Add a second condition GE M22 background color of your choice.
c) Save it and run

Let me know if works. Thanks

Same report as above

Can you do print screens of your traffic light setup, and e-mail it to me? Thanks

ldeassis09@gmail.com

Couldn't email, came back as undeliverable...





DEFMACRO=Condition_1, COLOR=RGB(255 255 0), STYLE=BOLD, BACKCOLOR=RGB(255 0 0), WHEN=N6 LT MONTHS22, $
DEFMACRO=Condition_2, COLOR=RGB(32 0 32), STYLE=BOLD, BACKCOLOR=RGB(255 255 0), WHEN=N6 GE MONTHS22, $

This message has been edited. Last edited by: Pops,

Are both dates in the same format? I've tested the issue and was not able to duplicate.

Hi,
Perhaps also post the "TABLE FILE" syntax that is being generated (excluding the stylesheet syntax).
Perhaps we can spot something there.

This is the issue with "condidional formatting". I left the second Traffic Light Condition you had me add....


-*COMPONENT=Define_ITEM
DEFINE FILE ITEM
INVABBR/A6=IF J001.INVT.INVT_ABBRV_CD EQ 'BTAINV' THEN J001.INVT.INVT_ABBRV_CD ELSE IF J001.INVT.INVT_ABBRV_CD EQ 'BTAHNV' THEN J001.INVT.INVT_ABBRV_CD ELSE 'JOB' ;
CURRENT_DATE/MDYY=&MDYY ;
MONTHS22/MDYY=DATEADD ( CURRENT_DATE , 'M' , -22 ) ;
MONTHS24/MDYY=DATEADD ( CURRENT_DATE , 'M' , -24 ) ;
LAST_ADJ_DATE/MDYY=HDATE ( LAST_ADJ_DT , 'YYMD' ) ;
LA_STATUS/A15=IF LAST_ADJ_DATE LE DATEADD ( LAST_ADJ_DATE , 'Y' , -2 ) THEN 'SLOW' ELSE 'ACTIVE' ;
END
TABLE FILE ITEM
SUM MONTHS22 AS 'Back 22 Months'
MONTHS24 AS 'Back 24 Months' NOPRINT
J001.INVT.LAST_RECPT_DT/HMDYY AS 'Last Receipt'
J001.INVT.LAST_ISS_DT/HMDYY AS 'Last Issue'
J001.INVT.LAST_ADJ_DT/HMDYY AS 'Last Adjustment'
J001.INVT.ON_HAND_QTY/P16.4C
LST.J001.INVT.MATL_CST_AMT/P16.2M AS 'Cost'
COMPUTE Value/D12MC=J001.INVT.ON_HAND_QTY * J001.INVT.MATL_CST_AMT ;
BY ITEM.ITEM.ITEM_ID UNDER-LINE
BY ITEM.ITEM.ITEM_DESC
BY INVABBR
WHERE ITEM.ITEM.COMM_CD LIKE 'TUBE%';
WHERE ITEM.ITEM.COMPANY_ID EQ '02';
WHERE ITEM.ITEM.ITEM_ID LIKE '06-2000-2%';
HEADING
"INVENTORY AGING AS OF ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET EXPANDABLE OFF
ON TABLE SET EXPANDBYROW OFF
ON TABLE SET DROPBLNKLINE OFF
ON TABLE SET STYLE *
INCLUDE=IBFS:/CFG/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
DEFMACRO=Condition_1, COLOR=RGB(255 255 0), STYLE=BOLD, BACKCOLOR=RGB(255 0 0), WHEN=N6 LT MONTHS22, $
DEFMACRO=Condition_2, COLOR=RGB(32 0 32), STYLE=BOLD, BACKCOLOR=RGB(255 255 0), WHEN=N6 GE MONTHS22, $
TYPE=HEADING, LINE=1, JUSTIFY=CENTER, $
TYPE=HEADING, LINE=1, ITEM=1, OBJECT=TEXT, SIZE=12, STYLE=BOLD, $
TYPE=HEADING, LINE=1, ITEM=1, OBJECT=FIELD, SIZE=12, STYLE=BOLD, $
TYPE=DATA, BACKCOLOR=(RGB(255 255 255) NONE), $
TYPE=DATA, COLUMN=N7, JUSTIFY=CENTER, $
TYPE=DATA, COLUMN=N6, MACRO=Condition_1, $
TYPE=DATA, COLUMN=N6, MACRO=Condition_2, $
ENDSTYLE
END

Release: WebFOCUS 8
Windows
HTML, PDF, EXCEL

Hi,

I think the issue that you are encountering may be because of the "dynamic redefine" of the receipt date's format, and/or that this format does not match the MONTHS22 format
i.e.
J001.INVT.LAST_RECPT_DT/HMDYY AS 'Last Receipt'
and
MONTHS22/MDYY=DATEADD ( CURRENT_DATE , 'M' , -22 ) ;

So, if you leave MONTHS22 with a format of MDYY, then I would suggest you create a DEFINE for a new field e.g. LASTRECPTDT, which converts your LAST_RECPT_DT field to an MDYY format as well.

The "conditional formatting" seems to be working.




I do have to use INFOASSIST to set it up, get an error message and then edit the code to remove the ' from before and after MONTH22 and MONTH24.

Thanks.

One down, one to go...

Using the same idea for the problems with this issue, I was able to do a similar HDATE and the DATEADD function works.

Many thanks to each of you for your help!