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] Dynamic Distribution List
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Dynamic Distribution List
 Login/Join
 
Member
posted
Hello All!

I have been working on a WebFocus program to send Emails to students indicating items that are missing from their application.

I'm using Report Caster. I have a .fex that creates the Email distribution List and a .fex for the Body of the EMail. When I run the program via Report Caster, I get the following error:



No Distribution information returned by Dynamic List. Report not distributed.



Does anybody know what might be causing this error?

This is .fex I created for the email distribution list:

DEFINE FILE FINHOLD
-*VALUE/P9 = FINHOLD.SEG01.SARADAP_PIDM;
OIT_EMAIL/A175 = IF VALUE EQ 4315 THEN 'alma.guardiola@tamiu.edu' ELSE
IF VALUE EQ 10909 THEN 'elizabeth@tamiu.edu' ELSE
IF VALUE EQ 11629 THEN 'rick@tamiu.edu' ELSE
IF VALUE EQ 16582 THEN 'armandina.ramos@tamiu.edu' ELSE
IF VALUE EQ 20200 THEN 'robert.trevino@tamiu.edu' ELSE ' ';
END
TABLE FILE FINHOLD
PRINT
OIT_EMAIL
BY
VALUE
ON TABLE HOLD AS FOCCACHE/FINHOLD1 FORMAT FOCUS
END
-*
TABLE FILE FINHOLD1
SUM
FINHOLD1.SEG01.OIT_EMAIL NOPRINT
BY FINHOLD1.SEG01.VALUE
BY FINHOLD1.SEG01.OIT_EMAIL AS 'DEST'
ON FINHOLD1.SEG01.OIT_EMAIL PAGE-BREAK
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END
-RUN


I'm using the programmers' email addresses to test the email.

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
 
Posts: 16 | Registered: August 08, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Hi Alma

I suspect that the fex to get the distribution list is failing to retrieve any records. Can you run that fex standalone and make sure there are no errors and that data is returned.

Also this may be the root cause as well, change the ON TABLE PCHOLD FORMAT HTML to ON TABLE PCHOLD FORMAT XML in the last section of your code

This message has been edited. Last edited by: FP Mod Chuck,


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1907 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:

Also this may be the root cause as well, change the ON TABLE PCHOLD FORMAT HTML to ON TABLE PCHOLD FORMAT XML in the last section of your code


Must be something such as this when you want to also burst
TABLE FILE USER_NAME_EMAIL
BY USER_ID    AS 'VALUE'
BY USER_EMAIL AS 'DEST'
ON TABLE PCHOLD
END
-RUN


Search for Dynamic Distribution List in Knowledge Base


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2341 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
I ran the report stand alone, and it does produce the records. I made the change to XML and also just PCHOLD by itself,and I get the following error for each PIDM:

No Report for null with burst value of (pidm#)

Any suggestions?


WebFOCUS 8
Windows, All Outputs
 
Posts: 16 | Registered: August 08, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
If your PIDM# is the bursting value and you don't have any assigned recipient for Null PIDM#, then RC is telling you that some records can't be sent to somebody.
You may need to either remove the Null PIDM# from the report data, change their value to a valid (default) one that can then be distributed to someone, or assign a recipient for Null value (but not sure that this is a valid option).


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2341 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
The PIDM is the Bursting Value. I shouldn't have any Null records. I'm only working with 5 PIDMS.

Here the Email Distribution List .fex:

DEFINE FILE FINHOLD
-*VALUE/P9 = FINHOLD.SEG01.SARADAP_PIDM;
OIT_EMAIL/A175 = IF VALUE EQ 4315 THEN 'alma.guardiola@tamiu.edu' ELSE
IF VALUE EQ 10909 THEN 'elizabeth@tamiu.edu' ELSE
IF VALUE EQ 11629 THEN 'rick@tamiu.edu' ELSE
IF VALUE EQ 16582 THEN 'armandina.ramos@tamiu.edu' ELSE
IF VALUE EQ 20200 THEN 'robert.trevino@tamiu.edu' ELSE ' ';
END
TABLE FILE FINHOLD
PRINT
OIT_EMAIL
BY
VALUE
ON TABLE HOLD AS FOCCACHE/FINHOLD1 FORMAT FOCUS
END
-*
TABLE FILE FINHOLD1
SUM
FINHOLD1.SEG01.OIT_EMAIL NOPRINT
BY FINHOLD1.SEG01.VALUE
BY FINHOLD1.SEG01.OIT_EMAIL AS 'DEST'
WHERE FINHOLD1.SEG01.VALUE EQ 4315 OR 10909 OR 11629 OR 16582 OR 20200
ON FINHOLD1.SEG01.OIT_EMAIL PAGE-BREAK
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XML
END
-RUN


And Here's the EMail Body .fex:


TABLE FILE FINHOLD
PRINT
FINHOLD.SEG01.STVADMR_DESC AS ''
BY FINHOLD.SEG01.VALUE NOPRINT
-*BY FINHOLD.SEG01.STVADMR_DESC AS ''
WHERE FINHOLD.SEG01.VALUE EQ 4315 OR 10909 OR 11629 OR 16582 OR 20200
ON FINHOLD.SEG01.VALUE SUBHEAD
"Dear " "
"We are delighted to learn of your interest in "Your application for "to complete your admissions file:"
" "
ON FINHOLD.SEG01.VALUE SUBFOOT
" "
"You have been issued a unique TAMIU ID which is posted above and begins with an "secure and write this number on any future correspondence. Use this TAMIU ID, along with your date of birth,"
"to check your admissions status online at " "
"Official transcript(s) and exam scores must be mailed directly from the records facility of the granting "
"institution to our office in order to be considered official. Please visit the Admissions Office webpage to access"
"forms at "hesitate to contact us at " "
"Dust'em devils!"
"Office of Admissions"
ON FINHOLD.SEG01.VALUE PAGE-BREAK
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML


Can you see what I'm doing wrong? Thanks!


WebFOCUS 8
Windows, All Outputs
 
Posts: 16 | Registered: August 08, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
DEFINE FILE FINHOLD-*VALUE/P9 = FINHOLD.SEG01.SARADAP_PIDM;OIT_EMAIL/A175 = IF VALUE EQ 4315 THEN 'alma.guardiola@tamiu.edu' ELSE IF VALUE EQ 10909 THEN 'elizabeth@tamiu.edu' ELSE IF VALUE EQ 11629 THEN 'rick@tamiu.edu' ELSE IF VALUE EQ 16582 THEN 'armandina.ramos@tamiu.edu' ELSE IF VALUE EQ 20200 THEN 'robert.trevino@tamiu.edu' ELSE ' ';END

First thing that I can see is that you are creating a Null (ELSE ' ') entry in the distribution file.

Your distribution file generation can be resolved with this :
DEFINE FILE FINHOLD
OIT_EMAIL/A175 = IF VALUE EQ 4315  THEN 'alma.guardiola@tamiu.edu'
            ELSE IF VALUE EQ 10909 THEN 'elizabeth@tamiu.edu'
            ELSE IF VALUE EQ 11629 THEN 'rick@tamiu.edu'
            ELSE IF VALUE EQ 16582 THEN 'armandina.ramos@tamiu.edu'
            ELSE IF VALUE EQ 20200 THEN 'robert.trevino@tamiu.edu'
            ELSE ' ';
END
TABLE FILE FINHOLD
BY VALUE 
BY OIT_EMAIL AS 'DEST'
WHERE VALUE EQ 4315 OR 10909 OR 11629 OR 16582 OR 20200;
ON TABLE PCHOLD FORMAT XML
END
-RUN


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2341 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
I made the changes and still get the same error. I also tried putting my email address for the ' ', and it still game me the same error. It seems distribution list is not being created with the correct values. When I run it stand alone, it lists the correct students.


WebFOCUS 8
Windows, All Outputs
 
Posts: 16 | Registered: August 08, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
ON TABLE PCHOLD FORMAT XML

First try changing the above with : ON TABLE PCHOLD
Second, the fex that generates the distribution list must not be part of the report fex but must be called by RC as a dynamic distribution list.
Third, it seems that you are using the bursting feature, so you need the VALUE also in the report data to have RC able to match the distribution with the data.

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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2341 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
I changed it to PCHOLD. The Distribution List .fex is being called by RC. Value is defined the report(no printed though). I tried running it without the distribution list and used my email address and it works. As soon as I use the distribution list, problems arise.


WebFOCUS 8
Windows, All Outputs
 
Posts: 16 | Registered: August 08, 2016Reply With QuoteReport This Post
Gold member
posted Hide Post
Hi Alma,
We had to build our distribution file dynamically just as you plan. To do that, we added the .fex code that builds the distribution file in the 'Pre-processing Procedure' section in the RC job. With regards to whether the .fex building your file ran or not, check the log. It should show if there were any issues.


WebFOCUS 8.0.09
App Studio 8009
Linux Kernel-2.6
DBMS: Oracle 11g
all output (Excel, HTML, AHTML, PDF)
 
Posts: 90 | Location: Oklahoma City, Oklahoma | Registered: July 01, 2010Reply With QuoteReport This Post
Member
posted Hide Post
SeyedG,

What did you use for Distributions?


WebFOCUS 8
Windows, All Outputs
 
Posts: 16 | Registered: August 08, 2016Reply With QuoteReport This Post
Gold member
posted Hide Post
We used name to be the burst value. In this case for you, student name will be your burst value. Your distribution text file must have the following format:

email1,student name1, $
email2,student name2, $
email3,student name3, $
.......................
 
Posts: 90 | Location: Oklahoma City, Oklahoma | Registered: July 01, 2010Reply With QuoteReport This Post
Member
posted Hide Post
We're using the PIDM for the Burst Value. In report caster, under Distributions tab, what did you use for TYPE of distribution?


WebFOCUS 8
Windows, All Outputs
 
Posts: 16 | Registered: August 08, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
The fex that creates the Dynamic Distribution list has to exist on the Reporting Server and it doesn't matter if the VALUE is a text, a name or an ID until it could be matched with the data in the report.

-1- So, in my case I have the following has the fex for the Dist List
SET ASNAMES = ON
TABLE FILE USER_NAME_EMAIL
BY USER_NAME  AS 'VALUE'
BY USER_EMAIL AS 'DEST'
ON TABLE PCHOLD
END
-RUN

The .mas looks like below :
FILENAME=USER_NAME_EMAIL, SUFFIX=FOC     ,
 DATASET=<app>/user_name_email.foc, $
  SEGMENT=SEG01, SEGTYPE=S4, $
    FIELDNAME=USER_NAME, ALIAS=E01, USAGE=A50V, $
    FIELDNAME=USER_EMAIL, ALIAS=E02, USAGE=A50V, $

And the data as below :
USER_NAME       USER_EMAIL
to myself       to_myself@mail.mail
to other        to_other@mail.mail


-2- I have the following as the report fex
DEFINE FILE CAR
USER_NAME /A50V = IF COUNTRY EQ 'ENGLAND' THEN 'to myself' ELSE 'to other';
END
TABLE FILE CAR
PRINT RETAIL_COST
BY USER_NAME NOPRINT
BY COUNTRY
BY CAR
END
-RUN


The RC job is calling the above (step -2-) as the task fex and have the BURST option on.
Also, the distribution method is "Dynamic Distribution List" which call the fex at step -1-
As result that ENGLAND report is sent to "to myself" and other country to "to other".


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2341 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
Ok,

I created the .mas as follows:
ILENAME=ADM_MIL, SUFFIX=FOC ,
DATASET=/WF/Webfocus8/EDASERVERazzerrod_admissions/adm_mil_emails_dist_list.fex, $
SEGMENT=SEG01, SEGTYPE=S4, $
FIELDNAME=PIDM, ALIAS=E01, USAGE=A9, $
FIELDNAME=EMAIL_ADDRESS, ALIAS=E02, USAGE=A175V, $

Modified the distribution list .fex as:
DEFINE FILE FINHOLD
EMAIL_ADDRESS/A175 = IF PIDM EQ '000004315' THEN 'alma.guardiola@tamiu.edu'
ELSE IF PIDM EQ '000010909' THEN 'elizabeth@tamiu.edu'
-* ELSE IF PIDM EQ '000011629' THEN 'rick@tamiu.edu'
ELSE IF PIDM EQ '000016582' THEN 'armandina.ramos@tamiu.edu'
ELSE IF PIDM EQ '000020200' THEN 'robert.trevino@tamiu.edu'
ELSE ' ';
END
TABLE FILE FINHOLD
PRINT
PIDM AS 'VALUE'
BY
EMAIL_ADDRESS AS 'DEST'
ON TABLE PCHOLD AS adm_mil
END
TABLE FILE ADM_MIL
PRINT
EMAIL_ADDRESS AS 'DEST'
BY
PIDM AS 'VALUE'
ON TABLE PCHOLD
END
-RUN


Now, I get this error message:

(FOC32518) ACCESS DENIED FOR THE FILE: ADM_FIL
prod_admissions/adm_mil_emails_dist_list: Unable to retrieve data for the distribution list


WebFOCUS 8
Windows, All Outputs
 
Posts: 16 | Registered: August 08, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
ON TABLE PCHOLD AS adm_mil

No need to have the above, only : "ON TABLE PCHOLD" and you don't need to declare a .mas. Everything will be done in memory on execution and you don't need two steps.

DEFINE FILE FINHOLD
EMAIL_ADDRESS/A175 = IF PIDM EQ '000004315' THEN 'alma.guardiola@tamiu.edu'
        ELSE IF PIDM EQ '000010909' THEN 'elizabeth@tamiu.edu'
	ELSE IF PIDM EQ '000016582' THEN 'armandina.ramos@tamiu.edu'
	ELSE IF PIDM EQ '000020200' THEN 'robert.trevino@tamiu.edu'
	ELSE ' ';
END
TABLE FILE FINHOLD
PRINT EMAIL_ADDRESS AS 'DEST'
BY PIDM AS 'VALUE'
WHERE PIDM EQ '000004315' OR '000010909' OR '000016582' OR '000020200';
ON TABLE PCHOLD
END
-RUN


We're back to the same recommendation as previously mentioned...


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2341 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Master
posted Hide Post
Try this for your table request.

TABLE FILE FINHOLD
PRINT
OIT_EMAIL AS 'DEST'
BY VALUE
WHERE VALUE IS NOT MISSING
ON TABLE PCHOLD
ON TABLE SET ASNAMES ON
END  




Scott

 
Posts: 865 | Registered: May 24, 2004Reply With QuoteReport This Post
Member
posted Hide Post
Thank you Everyone! It finally worked. I applied the change TexasStingray suggested and it worked! Thank you !!!


WebFOCUS 8
Windows, All Outputs
 
Posts: 16 | Registered: August 08, 2016Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Dynamic Distribution List

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