As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at firstname.lastname@example.org and provide your corporate email address, company, and name.
So if I understand you correctly, it's not the date that's the problem. The question really is, how to get a value into a column title? The only way I can think of doing that is to out the value in an & var.
TABLE FILE file PRINT LASTDAY ON TABLE SAVE AS LASTDAY END -RUN -READ LASTDAY &LASTDAY.A10.
hmm. well, there's no reason to print it out to a file and reread it. you can calculate your date in dialog manager, as an &var, using the systemvar for today: &YYMD for example as a starting point, if you need one. and then use it directly in your fex PRINT CAR AS '&MYVAR' note, that if you make your date nice and pretty "July 27, 2004", that comma will force your colhead to wrap to 2 lines; But...is your date actually a field or a DEFINE'd field in your actual database?
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003
That's not gong to help you calculate the last day of the prior month however. Calculating dates like that in DM is just not worth the headache. The minimal overhead of TABLE FILE, HOLD, and -READ is worth it.
The date does exist in the database (not a defined field), but it's in the wrong format (YYMD instead of MtrDYY). I couldn't get it to work when I created a defined field that reformatted the existing date field. How do you get a defined field value into a column title?
Here's what I'm trying to do in a nutshell:
Prompt user for a beginning date and an end date in YYMD format. Pass those dates to the database to retrieve the right data. That works fine.
But then I want to take those 2 dates, reformat them to be in MtrDYY (Month spelled out, day, 4 digit year) and then use those reformatted dates as column titles.
I tried the -READ idea, but it didn't work. I kept getting a date conversion error.
Any more ideas would be greatly appreciated before I rip all my hair out!!
Posts: 68 | Location: Springfield MA | Registered: May 07, 2003
Something like this: Sorry if it didn't align well.
July 20, 2004 July 21, 2004 July 22, 2004 MAJ ------------------------------------------------------------------ AAPA 0 1 0 AE 0 4 0 ASTU 1 0 2 BIOL 2 1 1 BRCT 0 0 1 BSAD 0 0 1 CENG 1 0 1 CET 0 0 1 CHEM 1 0 0 Came from SET STYLEMODE = FIXED DEFINE FILE MMAPTBL_UNO_PROD PRETTYDATE/MtrDYY = MM515; END TABLE FILE MMAPTBL_UNO_PROD WHERE MM515 IS-FROM '&DATE1' TO '&DATE2' COUNT STU_ID AS '' BY MAJOR_1 AS 'MAJ' ACROSS PRETTYDATE AS '' END
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004
-READ shouldn't be doing any type of date conversion. As long as you use SAVE or HOLD FORMAT ALPHA followed by a -RUN and -READ into an alpha (-READ filename &var.An.) then there shouldn't be a problem. Can you post your code?
Ok, I see the problem now. When you SAVE or HOLD the MtrDYY date, it actual saves as YYMD. Try this.
SET PAGE=NOPAGE DEFINE FILE PORTREV BEGDATE1/YYMD = &BEG_DATE; BEGDATE2/MtrDYY = BEGDATE1; ENDDATE1/YYMD = &END_DATE; ENDDATE2/MtrDYY = ENDDATE1; END TABLE FILE PORTREV PRINT BEGDATE2 AS '' ENDDATE2 AS '' IF RECORDLIMIT EQ 1 ON TABLE HOLD AS MYDATE FORMAT WP END -RUN -READ MYDATE -READ MYDATE &NEWBEG.A20. &NEWEND.A20. -TYPE &NEWBEG &NEWEND
Here is some code with the car file and the results. &H1 has a date with a translated month that I used as the column name for country in the car table request below.
DEFINE FILE CAR FRMDT/MDY WITH COUNTRY='072704'; FRMDTA/A8MDYY=FRMDT; MTH/A2=EDIT(FRMDTA,'99'); DAYS/A2=EDIT(FRMDTA,'$$99'); YRS/A4=EDIT(FRMDTA,'$$$$9999'); MTHT/A3=DECODE MTH(01 JAN 02 FEB 03 MAR 04 APR 05 MAY 06 JUN 07 JUL 08 AUG 09 SEP 10 OCT 11 NOV 12 DEC ELSE ' '); OUTDATE/A13=MTHT|' '|DAYS|' '|YRS; END TABLEF FILE CAR PRINT OUTDATE IF RECORDLIMIT EQ 1 ON TABLE HOLD AS HOLD1 FORMAT ALPHA END -RUN -SET &H1=' '; -READ HOLD1 &H1
TABLEF FILE CAR PRINT COUNTRY AS '&H1' END
JUL 27 2004 ------------- ENGLAND FRANCE ITALY JAPAN W GERMANY
Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004
The date in my example was hardcoded in the define but it could be a easily be a date that was prompted for from a form and then inserted in the define.
The code was developed in mainframe Focus 7.11. I pasted the exact same code in an RPC and executed it in WF 4.36 and it also worked there too. Can you be more specific about the error message your getting?
Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004
Jen, this code below works, and the -READ works nicely but dates don't seem to get saved in the reformatted logoc;
DEFINE FILE PORTREV BEGDATE1/YYMD WITH ASSET_CLASS= &BEG_DATE; BEGDATE2/MtrDYY WITH ASSET_CLASS= BEGDATE1; ENDDATE1/YYMD WITH ASSET_CLASS= &END_DATE; ENDDATE2/MtrDYY WITH ASSET_CLASS= ENDDATE1; END TABLE FILE PORTREV PRINT BEGDATE2 ENDDATE2 IF RECORDLIMIT EQ 1 ON TABLE SAVE FORMAT ALPHA END -RUN -READ SAVE &NEWBEG.A8. &NEWEND.A8. -TYPE Newbeg is: &NEWBEG NewEnd is: &NEWEND -RUN
Here's the echo; Newbeg is: 02282002 NewEnd is: 02282002
Note: when the file gets saved, it doesn't save the reformatted smart date, it saves the 8 character date!!!
Posts: 428 | Location: Springfield, MA | Registered: May 07, 2003
To handle all of the various date formats in headings and for downloads to Excel that our customers wanted, I created a focus file called CALENDAR (many years ago).
It can be JOINed to based on a real Focus date, then you can print out (BY, ACROSS, whatever) whichever date format you like.
If you contact me offline, I can send you the .mas and the .fex that builds the .foc file. We're currently in a unix environment--don't know whether modifications might be needed for other environments.
I'm at email@example.com.
Posts: 124 | Location: Lebanon, New Hampshire | Registered: April 24, 2003