Hello,

I need to create hundreds of tasks in Report caster and each task has its own distribution list. I am looking into using Dynamic list.

I created a fex file using a stored procedure from SQL to select these two columns...ShipperCode (Burst Value) and email address (DEST).

Can someone help me with my code. I am getting this error in my log.

...
THE ON FIELD IS NOT THE FIRST BY FIELD
Task finished.
Dynamically creating distribution information
No distribution information returned by Dynamic List. Report not distributed.
...

I created a SYNONYM called TESTDYNAMICEMAIL.

FILENAME=TESTDYNAMICEMAIL, SUFFIX=FOC , $
SEGMENT=SEG01, SEGTYPE=S1, $
FIELDNAME=FOCLIST, ALIAS=E01, USAGE=I5, $
FIELDNAME=burstValue, ALIAS=E02, USAGE=A8,
MISSING=ON, $
FIELDNAME=emailAddress, ALIAS=E03, USAGE=A80,
MISSING=ON, $


...and here's my .fex file

SET ASNAMES=ON
TABLE FILE TESTDYNAMICEMAIL
PRINT
TESTDYNAMICEMAIL.SEG01.burstValue AS 'VALUE'
TESTDYNAMICEMAIL.SEG01.emailAddress AS 'DEST'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

I appreciate any input.

Thanks,
Winnie

This message has been edited. Last edited by: <Kathryn Henning>,

I don't think you want format html or you don't want column headings. You also need to format the file itself. I think you need a $ or comma between each. Check the documentation.

SET ASNAMES=ON

TABLE FILE CAR
PRINT
COUNTRY AS 'DEST'
CAR AS 'VALUE'
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS HLDEMAILDIST
END

Thanks, Matt and Alex...

I changed my code to this, but I am still getting the error

...
THE ON FIELD IS NOT THE FIRST BY FIELD
Task finished.
Dynamically creating distribution information
No distribution information returned by Dynamic List. Report not distributed.
...


-*code
SET ASNAMES=ON
TABLE FILE TESTDYNAMICEMAIL
PRINT
TESTDYNAMICEMAIL.SEG01.burstValue AS 'VALUE'
TESTDYNAMICEMAIL.SEG01.emailAddress AS 'DEST'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS EMAILLIST
END


When I run the fex file, I am getting result.

Thanks!

Winnie,

How does your distribution file look?

I have a file that looks like this with no headings. You also need the commas and $ at each end.

User1@test.com,A,$
User2@test.com,B,$
User3@test.com,C,$

MattC,

I'm getting the data from the database and it's stored in the HOLD file...

Here's what it looks like...I only have one row in there for testing purposes...
VALUE DEST
RPCLOH username@test.com

Am I supposed to put comma and $ on my result set?

Yes, I would define it prior and concatenate into a single column with

“email address’ , ‘ bust value’, $


DEFINE FILE
DESTVALUE/AXX = DEST | ‘,’ | VALUE |’.’|’$’;
END

TABLE FILE
PRINT DESTVALUE
END

Winnie, Order counts. For a dynamic list the order expected is DEST then VALUE.

Hello,

Try ON TABLE PCHOLD instead of HOLD.

As the message says there is no "BY" field in your fex. The burst function takes the first BY field for the distribution

Thank you everyone for the input...I was able to make it work by creating a SYNONYM first and then using PCHOLD instead HOLD.

1st .fex file:
ENGINE SQLSYB SET DEFAULT_CONNECTION MARTENHOT
-*SUMMARY
SQL SQLSYB
EX mastersys..RCDynamicEmailListWF
;

APP HOLD SYBASE

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TESTDYNAMICEMAIL FORMAT FOCUS
END

2nd .fex file:

SET ASNAMES=ON
TABLE FILE TESTDYNAMICEMAIL
PRINT
TESTDYNAMICEMAIL.SEG01.burstValue AS 'VALUE'
TESTDYNAMICEMAIL.SEG01.emailAddress AS 'DEST'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS EMAILLIST
END


TABLE FILE EMAILLIST
BY LOWEST EMAILLIST.EMAILLIS.DEST AS ''
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD
END

Regards,