Alpha DevCon 2018
Results 1 to 3 of 3

Thread: Email function to loop through invoice items on web server

  1. #1
    Member richardurban's Avatar
    Real Name
    Richard Urban
    Join Date
    Apr 2000
    Location
    Harpers Ferry, WV
    Posts
    431

    Default Email function to loop through invoice items on web server

    I remember reading about an email function to loop through invoice line items that are related to a parent record and send them from an A5 web server in an email, but I cannot find this function in help or on the forum. Is there such a function?

  2. #2
    Member richardurban's Avatar
    Real Name
    Richard Urban
    Join Date
    Apr 2000
    Location
    Harpers Ferry, WV
    Posts
    431

    Default Re: Email function to loop through invoice items on web server

    I used the following code on the A5w page, and it works well:
    Code:
     tbl = table.open("[Pathalias.ADB_path]\web_total.set")
    query.filter = "order_numb = \"" + session.cinvoice_num + "\" "
    query.order = ""
    indx = tbl.query_create()
    
    tbl1 = table.get("cust_ord_web") ' get pointer to parent
    
    tbl2 = table.get("products") ' get pointer to child
    
    
    dim line_items as c
    items = ""
    while .not. tbl.fetch_eof()
    items_i = tbl2.New_code + "   " + tbl1.Quantity+ "- " + alltrim(tbl2.long_name) + "-" +alltrim(tbl2.section)+crlf()
    items = items + Items_i
    
    
    
       tbl.fetch_next()
    end while
    line_items = items
    email_body = stritran(email_body,"[line_items]", line_items)
    items_i includes any fields from tbl1 or tbl2 that you want to include in the confirmation email.
    The email body looks like this:
    Code:
    email_body = <<%text%
    Ordered by: [First] [Last]
    Thank you for shopping at [company]!
    This email confirms your order, # [invoice_number].  Your Customer ID # is [id_number].
    Your grocery order will be delivered on [delivery_day], [delivery_date]
    during period [delivery_time].
    If you need assistance, please call [telephone].
    We look forward to serving you.
    Items Ordered:
    Item #  Quantity  Item Description        Section
    [line_items]
    %text%
    Multiple stritran expressions, as shown above, are used to fill in the email.
    I then use the email_smtp_send method to send the email.
    Last edited by richardurban; 11-29-2009 at 02:42 PM.

  3. #3
    Member EricN's Avatar
    Real Name
    Eric
    Join Date
    May 2007
    Location
    Minnesota
    Posts
    553

    Default Re: Email function to loop through invoice items on web server

    Hello Richard,
    Thank you for sharing this script! I expanded on it a little. This may need a little "cleaning up" but it works great. Here is what I did

    dim vFilter2 as c
    vFilter2 = "Permissions= \"JobSeeker\".and. Emailblast = 'Yes' .and. Bstate<>''.and.Bstate<>'11'"
    eids = table.external_record_content_get("[pathalias.adb_path]\users", "ssn","ssn",vFilter2)

    For each y in eids
    jsemail = alltrim(lookupc("F",alltrim(y),"alltrim(email)","[pathalias.adb_path]\users","ssn"))
    fname = alltrim(lookupc("F",alltrim(y),"alltrim(first_name)","[pathalias.adb_path]\users","ssn"))

    dim vFilter as c
    emstate = alltrim(lookupc("F",alltrim(y),"alltrim(state)","[pathalias.adb_path]\users","ssn"))
    vFilter = "jobsresume= \"Jobs\".and.Poststatus = \"Active\".and.site_state="+quote(emstate)
    uids = table.external_record_content_get("[pathalias.adb_path]\posts", alltrim("postnum"),"company_name+job_title",vFilter)

    For each x in uids

    dim tbl as P
    dim qry as P
    tbl = table.open("[pathalias.adb_path]\posts")
    query.filter = "Postnum="+quote(x)
    query.order = "" '(I already set the order above)
    query.options = ""
    qry = tbl.query_create()
    tbl.fetch_first()
    dim line_items as c
    dim items as c
    while .not. tbl.fetch_eof()
    if tbl.Job_start_date<>ctod("")
    vStart = "<font size=2 face=Arial><strong>Start Date: </strong>"+tbl.Job_start_date+"</font>"
    else
    vStart = ""
    end if
    if tbl.Job_wage<>""
    vWage = "<font size=2 face=Arial><strong> Wage: </strong>"+tbl.Job_wage+"</font>"
    else
    vWage = ""
    end if
    if tbl.Site_phone<>""
    vPhone = "<font size=2 face=Arial><strong> Phone: </strong>"+tbl.Site_phone+"</font>"
    else
    vPhone = ""
    end if
    if tbl.Site_phone_ext<>""
    vPhoneex= "<font size=2 face=Arial><strong> Phone: </strong>"+tbl.Site_phone_ext+"</font>"
    else
    vPhoneex=""
    end if
    vAbb = tbl.Site_state
    items_a = "<strong><font size=3 color=#ff6600 face=Arial>"+w_upper(tbl.Company_name)+"<BR>"+ crlf()
    items_b = tbl.Job_title+"</strong><font size=2 face=arial> "+alltrim(tbl.Site_city)+", "+tbl.Site_state+"</font></font><BR>"+crlf()
    items_c = vStart+vWage+vPhone+vPhoneex +"<BR>"+crlf()
    items_d = "<font size=2 face=Arial>"+substr(tbl.Job_description,1,200)+"</font>" +crlf()
    url = "http://www.socialservicenetwork.com/JobsBrowseGenView.a5w?postnum="+alltrim(tbl.Postnum)+"&ssn="+alltrim(tbl.Ssn)+crlf()
    jlink = "<a href = "+url+"><font size=2 face=Arial>More Details</font></a><BR><BR>"
    items = items + Items_a+items_b+items_c+items_d+jlink
    tbl.fetch_next()

    end while
    line_items = items
    dim email_body as c
    email_body = stritran(email_body,"[line_items]", line_items)
    qry.drop()
    tbl.close()

    next x

    Mailblast = lookup("[pathalias.adb_path]\counts","Recnum= 1","Mailblast")
    dim pm as P
    pm.lrelated = .T.
    pm.from = Mailblast
    pm.from_alias = "Job Updates for "+fname
    pm.bcc = Mailblast
    pm.to = jsemail
    pm.subject = "Your weekly jobs list for "+ vAbb
    pm.qq = file.to_string("[pathalias.adb_vString]\AdmMailBlastJobsWkly.htm")
    pm.html_message = evaluate_string(pm.qq)
    email_send2(pm)

    next y

    The resulting email is in the 1st pic. 2nd pic is the html form created in FrontPage
    Last edited by EricN; 01-29-2010 at 07:02 PM.
    Eric

    Alpha Five Websites
    longlivepuppies.com
    socialservicenetwork.com
    -------------------------------------------------
    socialservicenetwork.org

Similar Threads

  1. Invoice Items PO Number - from invoice header
    By RobPolley in forum Application Server Version 8
    Replies: 4
    Last Post: 11-14-2008, 08:41 PM
  2. Kits-as invoice line items
    By KathyWeeks in forum Alpha Five Version 7
    Replies: 16
    Last Post: 11-08-2006, 01:07 PM
  3. Email profile on web server??
    By robert swanson in forum Web Application Server v7
    Replies: 16
    Last Post: 11-15-2005, 12:17 AM
  4. Qlinker Invoice Items
    By Greg Suhok in forum Alpha Five Version 5
    Replies: 1
    Last Post: 01-17-2004, 10:01 AM
  5. trouble with invoice items
    By Joyce Kinmont in forum Alpha Five Versions 2 and 3
    Replies: 1
    Last Post: 04-14-2001, 05:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •