1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Question Abl: Send Email With Query Results Concatenated In Body

Discussion in 'Development' started by AlexTheDroog, May 16, 2017.

  1. AlexTheDroog

    AlexTheDroog New Member

    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.
    Code (progress):
    1.  
    2. define query email
    3. for ttRMAHead FIELDS(Company RMANum),
    4. SerialNo  FIELDS(SerialNumber RMANum).
    5. Define variable SN as character no-undo.
    6. OPEN QUERY email for each ttRMAHead  no-lock
    7.     where ttRMAHead.Company = cur-comp,
    8. each SerialNo   no-lock
    9.     outer-join where ( ttRMAHead.Company = SerialNo.Company and  ttRMAHead.RMANum = SerialNo.RMANum).
    10. SN = SN + SerialNo.SerialNumber + ", ".
    11. repeat:
    12. GET NEXT email NO-LOCK.
    13.     IF NOT AVAILABLE SerialNo THEN LEAVE.
    14. if available SerialNo then do:
    15.     define variable vFrom as character no-undo.
    16.     define variable vTo as character no-undo.
    17.     define variable vCC as character no-undo.
    18.     define variable vSubject as character no-undo.
    19.     define variable vBody as character no-undo.
    20.     define variable hEmailEx as handle no-undo.
    21.                  
    22.     run Bpm/BpmEmail.p persistent set hEmailEx.
    23.  
    24.     assign vFrom = 'RAM-Delete@xxx.com'.
    25.           vTo = 'me@xxx.com'.
    26.           vSubject = vSubject + 'RMA ' + string(ttRMAHead.RMANum) + ' containing Serial Numbers was deleted. '.
    27.           vBody = vBody +  'RMA ' + string(ttRMAHead.RMANum) + ' containing SN: ' + string(SN) + ' was deleted. The ADBook needs to be reviewed and possiably corrected.'.
    28.  
    29.             run SendEmail in hEmailEx (
    30.             false,
    31.             CUR-COMP,
    32.             vFrom,
    33.             vTo,
    34.             vCC,
    35.             vSubject,
    36.             vBody,
    37.             ""
    38.             ).
    39.  
    40.             if valid-handle(hEmailEx) then delete procedure hEmailEx.
    41.             leave.
    42.  
    43.             end.  
    44. end.
     
    Last edited by a moderator: May 17, 2017 at 4:43 AM
  2.  
  3. Stefan

    Stefan Active Member

    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:

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

    AlexTheDroog New Member

    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.

    Code (progress):
    1.  
    2. define query email for ttRMAHead FIELDS(Company RMANum),SerialNo  FIELDS(SerialNumber RMANum).
    3. Define variable SN as character no-undo.
    4. OPEN QUERY email
    5. for each ttRMAHead  no-lock where ttRMAHead.Company = cur-comp,
    6. each SerialNo outer-join where ( ttRMAHead.Company = SerialNo.Company and  ttRMAHead.RMANum = SerialNo.RMANum) no-lock.
    7. get first email.
    8. repeat while available SerialNo:
    9. SN = SN + SerialNo.SerialNumber + ", ".
    10. GET NEXT email.
    11. /*SN = SN + SerialNo.SerialNumber + ", ".*/
    12. /*GET LAST email NO-LOCK.*/
    13. /*SN = SN + SerialNo.SerialNumber + ", ".*/
    14.     /*IF NOT AVAILABLE SerialNo THEN LEAVE.*/
    15. if available SerialNo then do:
    16.     define variable vFrom as character no-undo.
    17.     define variable vTo as character no-undo.
    18.     define variable vCC as character no-undo.
    19.     define variable vSubject as character no-undo.
    20.     define variable vBody as character no-undo.
    21.     define variable hEmailEx as handle no-undo.
    22.                  
    23.     run Bpm/BpmEmail.p persistent set hEmailEx.
    24.  
    25.     assign vFrom = 'RAM-Delete@colt.com'.
    26.            vTo = 'mgaritta@colt.com'.
    27.            vSubject = vSubject + 'RMA ' + string(ttRMAHead.RMANum) + ' containing Serial Numbers was deleted. '.
    28.           /* vBody = vBody +  'RMA ' + string(ttRMAHead.RMANum) + ' containing SN: ' + string(SN) + ' was deleted. The ADBook needs to be reviewed and possiably corrected.'.*/
    29. SN = substitute( "&1&2,", SN, SerialNo.SerialNumber ).
    30. /*SN = substitute( "&1,", SN).*/
    31.  
    32. vBody =substitute("&1RMA &2 containing SN: &3 was deleted. The ADBook needs to be reviewed and possiably corrected.",vBody,ttRMAHead.RMANum,SN).
    33.  
    34.             run SendEmail in hEmailEx (
    35.             false,
    36.             CUR-COMP,
    37.             vFrom,
    38.             vTo,
    39.             vCC,
    40.             vSubject,
    41.             vBody,
    42.             ""
    43.             ).
    44.  
    45.             if valid-handle(hEmailEx) then delete procedure hEmailEx.
    46.             leave.
    47.  
    48.             end.
    49. end.
    50.  
     
  5. Stefan

    Stefan Active Member

    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
     

Share This Page