Reporting from Report Caster Tables

January 28, 2012, 09:58 PM

Rick Man

I am trying to get list of email address that are used in the schedules.
1. Is there any documentation as to what is in the tables and what is the data structure, like a entity diagram.
2. What I've figured out is that there are 2 places to pull them from BOTADDR, for address book lists, and BOTACCES, for Reports Library Access Lists. But what I can't figure out how to get are the single addresses from the BOTSCHED file.

I think this is a key piece of code but it is not working. SKEDBOOK prints the NODATA character but it is not null, at lease this statement doesn't think it is.

EMAIL/A50 = IF SKEDBOOK IS MISSING THEN 'A' ELSE 'B';

Here's some not very elegant code:

-GOTO ADDRBOOK
-*-GOTO ACCESSLIST
-*
-*********************************
-* ADDRESS BOOK lIST
-*********************************
-ADDRBOOK
-PROMPT '&BY1.(JOB,ADDRESS_BOOK,USER).SELECT HOW YOU WANT THE DATA REPORTED BY'.;
DEFINE FILE BOTSCHED
SKEDBOOK/A50=SUBSTV(800, DISTLIST, 1, 50, SKEDBOOK);
END
TABLE FILE BOTSCHED
SUM *
BY SKEDBOOK
ON TABLE HOLD AS HOLD_BOTSCHED
END
-*-EXIT
-*
DEFINE FILE BOTDEST
BNAME/A50 = BOOKNAME;
END
TABLE FILE BOTDEST
PRINT *
BY BNAME
-*WHERE BOOKNAME EQ 'Athletic Report 3'
ON TABLE HOLD AS HOLD_BOTDEST
END
-*-EXIT
SET ALL=PASS
JOIN SKEDBOOK  IN HOLD_BOTSCHED TO ALL BNAME IN HOLD_BOTDEST AS J1
-*
DEFINE FILE HOLD_BOTSCHED
-*EMAIL/A50 = DESTFN;
EMAIL/A50 = IF SKEDBOOK IS MISSING THEN 'A' ELSE 'B';
END
TABLE FILE HOLD_BOTSCHED
PRINT
DESTFN BNAME DISTLIST SKEDBOOK
-IF &BY1 = 'ADDRESS_BOOK' THEN GOTO BY1ADDRBOOK ELSE IF &BY1 = 'USER' THEN GOTO BY1MEMBER;
-BYJOB
BY JOBDESC  AS 'JOB'
BY SKEDBOOK AS 'ADDRESS BOOK'
BY EMAIL    AS 'EMAIL'
-GOTO BYDONE1
-BY1ADDRBOOK
BY SKEDBOOK    AS 'ADDRESS BOOK'
BY DESTFN      AS 'USER'
-GOTO BYDONE1
-BY1MEMBER
BY DESTFN      AS 'USER'
BY SKEDBOOK AS 'ADDRESS BOOK'
-BYDONE1
-*WHERE DESTFN IS NOT MISSING
-*ON TABLE PCHOLD FORMAT EXL2K
END
-EXIT
-*********************************
-* ACCESS lIST
-*********************************
-ACCESSLIST
-*
-SET &ECHO=ALL;
-PROMPT '&BY2.(JOB,ACCESS_LIST,USER).SELECT HOW YOU WANT THE DATA REPORTED BY'.;
JOIN SCHEDULEID IN BOTSCHED TO ALL SCHEDULEID IN BOTCAT AS J1
JOIN ACCESSNAME IN BOTSCHED TO ALL ACCESSNAME IN BOTACCES AS J2
JOIN BOTACCES.ACCESSNAME IN BOTSCHED TO ALL ACCESSNAME IN BOTLIST AS J3
SET ALL=ON
DEFINE FILE BOTSCHED
SKEDBOOK/A64=SUBSTV(800, DISTLIST, 1, 50, SKEDBOOK);
END
TABLE FILE BOTSCHED
SUM
MEMBER NOPRINT
-IF &BY2 = 'ACCESS_LIST' THEN GOTO BY2ACCESNAME ELSE IF &BY2 = 'USER' THEN GOTO BY2MEMBER;
-BY2JOB
BY JOBDESC    AS 'JOB'
BY ACCESSNAME AS 'ACCESS LIST'
BY MEMBER     AS 'USER'
-GOTO BYDONE2
-BY2ACCESNAME
BY ACCESSNAME AS 'ACCESS LIST'
BY MEMBER     AS 'USER'
-GOTO BYDONE2
-BY2MEMBER
BY MEMBER     AS 'USER'
BY ACCESSNAME AS 'ACCESS LIST'
-BYDONE2
WHERE MEMBER IS NOT MISSING
-*WHERE SCHEDULEID EQ 'S1564u802d01'
ON TABLE PCHOLD FORMAT EXL2K
END