Question Abl: Send Email With Query Results Concatenated In Body

[AlexTheDroog]

I had the following query working with the "first" result showing up. The issue is there can be multiple rows returned. I was trying to concatenate each row (just 1 field) into a comma separated character variable and put it in the email body. The code complies but I never get an email. This code is fired in a BMP in Epicor 9.

define query email
for ttRMAHead FIELDS(Company RMANum),
SerialNo  FIELDS(SerialNumber RMANum).
Define variable SN as character no-undo.
OPEN QUERY email for each ttRMAHead  no-lock
    where ttRMAHead.Company = cur-comp,
each SerialNo   no-lock 
    outer-join where ( ttRMAHead.Company = SerialNo.Company and  ttRMAHead.RMANum = SerialNo.RMANum).
SN = SN + SerialNo.SerialNumber + ", ".
repeat:
GET NEXT email NO-LOCK.
    IF NOT AVAILABLE SerialNo THEN LEAVE.
if available SerialNo then do:
    define variable vFrom as character no-undo.
    define variable vTo as character no-undo.
    define variable vCC as character no-undo.
    define variable vSubject as character no-undo.
    define variable vBody as character no-undo.
    define variable hEmailEx as handle no-undo.
                  
    run Bpm/BpmEmail.p persistent set hEmailEx.

    assign vFrom = 'RAM-Delete@xxx.com'.
          vTo = 'me@xxx.com'.
          vSubject = vSubject + 'RMA ' + string(ttRMAHead.RMANum) + ' containing Serial Numbers was deleted. '.
          vBody = vBody +  'RMA ' + string(ttRMAHead.RMANum) + ' containing SN: ' + string(SN) + ' was deleted. The ADBook needs to be reviewed and possiably corrected.'.

            run SendEmail in hEmailEx (
            false,
            CUR-COMP,
            vFrom,
            vTo,
            vCC,
            vSubject,
            vBody,
            ""
            ).

            if valid-handle(hEmailEx) then delete procedure hEmailEx.
            leave.

            end.  
end.

 

[Stefan]

Please put code tags around code to make it easier to read.

When you use outer-join, if an inner record is not available, it's values will be unknown. Anything concatenated with unknown is also unknown - so your SN is becoming ? which is then wiping out vbody entirely.

See ABL Dojo for an example.

What really helps is to use substitute instead of simple concatenation, it will put an actual question mark '?' in your string instead of wiping it out to unknown:

SN = substitute( "&1&2,", SN, SerialNo.SerialNumber ).
vBody =  substitute(
            "&1RMA &2 containing SN: &3 was deleted. The ADBook needs to be reviewed and possiably corrected.",
            vBody,
            ttRMAHead.RMANum,
            SN
         ).

 

[AlexTheDroog]

So I was able to get it to show 2 SerilNumbers with your help. I think there is an issue with the loop. It only returns the 1st 2 SerialNumbers added. I think its exiting the loop after the second iteration or the SN variable is not concatenating and overwriting the data.

define query email for ttRMAHead FIELDS(Company RMANum),SerialNo  FIELDS(SerialNumber RMANum).
Define variable SN as character no-undo.
OPEN QUERY email
for each ttRMAHead  no-lock where ttRMAHead.Company = cur-comp,
each SerialNo outer-join where ( ttRMAHead.Company = SerialNo.Company and  ttRMAHead.RMANum = SerialNo.RMANum) no-lock.
get first email.
repeat while available SerialNo:
SN = SN + SerialNo.SerialNumber + ", ".
GET NEXT email.
/*SN = SN + SerialNo.SerialNumber + ", ".*/
/*GET LAST email NO-LOCK.*/
/*SN = SN + SerialNo.SerialNumber + ", ".*/
    /*IF NOT AVAILABLE SerialNo THEN LEAVE.*/
if available SerialNo then do:
    define variable vFrom as character no-undo.
    define variable vTo as character no-undo.
    define variable vCC as character no-undo.
    define variable vSubject as character no-undo.
    define variable vBody as character no-undo.
    define variable hEmailEx as handle no-undo.
                  
    run Bpm/BpmEmail.p persistent set hEmailEx.

    assign vFrom = 'RAM-Delete@colt.com'.
           vTo = 'mgaritta@colt.com'.
           vSubject = vSubject + 'RMA ' + string(ttRMAHead.RMANum) + ' containing Serial Numbers was deleted. '.
          /* vBody = vBody +  'RMA ' + string(ttRMAHead.RMANum) + ' containing SN: ' + string(SN) + ' was deleted. The ADBook needs to be reviewed and possiably corrected.'.*/
SN = substitute( "&1&2,", SN, SerialNo.SerialNumber ).
/*SN = substitute( "&1,", SN).*/

vBody =substitute("&1RMA &2 containing SN: &3 was deleted. The ADBook needs to be reviewed and possiably corrected.",vBody,ttRMAHead.RMANum,SN).

            run SendEmail in hEmailEx (
            false,
            CUR-COMP,
            vFrom,
            vTo,
            vCC,
            vSubject,
            vBody,
            ""
            ).

            if valid-handle(hEmailEx) then delete procedure hEmailEx.
            leave.

            end.
end.

 

[Stefan]

Yes, these is an issue with the loop.

The query will iterate once for every combined row. So an RMA with 10 serial nos will result in 10 rows. You want to collect data until you are on the last row. On the last row you can then send your email and reset your collected data. You can use break by and last-of to achieve this.

See the following snippet for an example:

ABL Dojo

 

[AlexTheDroog]

So I finally got it working. It was just one thing after another. I didn't realize the Character was limiting to 8 as well as the fact I could not get the complete concatenation until Ending the block.....in any event this is what ended up working. Thanks for your help. Hopefully this will be all I need.

define query email for ttRMAHead FIELDS(Company RMANum),SerialNo  FIELDS(SerialNumber RMANum) scrolling.
Define  variable SN as CHARACTER NO-UNDO FORMAT "X(100)".
Define  variable RMA as CHARACTER NO-UNDO FORMAT "X(100)".

OPEN QUERY email
for each ttRMAHead  no-lock where ttRMAHead.Company = cur-comp,
each SerialNo where ( ttRMAHead.Company = SerialNo.Company and  ttRMAHead.RMANum = SerialNo.RMANum ) no-lock. /*break by ttRMAHead.RMANum*/

get first email.
repeat while available SerialNo:
SN = substitute( "&1&2,", SN, SerialNo.SerialNumber ).
RMA = substitute( "&1", ttRMAHead.RMANum ).
GET NEXT email.
END.

/*{lib\PublishInfoMsg.i &Infomsg = rma}.*/

define variable vFrom as character no-undo.
    define variable vTo as character no-undo.
    define variable vCC as character no-undo.
    define variable vSubject as character no-undo.
    define variable vBody as character no-undo.
    define variable hEmailEx as handle no-undo.
                    
    run Bpm/BpmEmail.p persistent set hEmailEx.

    assign vFrom = 'RAM-Delete@colt.com'.
               vTo = 'm@colt.com'.
               vSubject = vSubject + 'RMA ' + RMA + ' containing Serial Numbers was deleted. '.
                 vBody =substitute("&1RMA &2 containing SN: &3 was deleted. The ADBook needs to be reviewed and possiably corrected.",vBody,RMA,SN).
            run SendEmail in hEmailEx (false,CUR-COMP,vFrom,vTo,vCC,vSubject,vBody,"").

            if valid-handle(hEmailEx) then delete procedure hEmailEx.
            leave.

 

[Stefan]

Format is only a display format - ie when you DISPLAY the character. Internally characters are always variable length (max approx 32000). So if you remove the formats from your two variables, nothing will change.