Confirm

Expand all | Collapse all

Enquiry Email Alerts

  • 1.  Enquiry Email Alerts

    Posted 12 days ago
    I am trying to set up an Enquiry Alert Type where when a specific Enquiry Type has its Status Code changed it sends out an email.

    I can get the Alert Type to notify a specific Action Officer, User etc but what I want to do is send an Email to multiple Users/Officers using the Data Source option.

    I initialy created a Data Source, as suggested in help, to send an email to myself which works ok but when I added another User/Officer to the Data Source SQL no alert emails where sent at all?

    Is it possible to send Alert emails to multiple Users/Officers and if so has anyone managed to set it up using a Data Source?

    The SQL I used to sent an Alert email to myself is as follows:-

    SELECT
    Central_Enquiry.Enquiry_Number,
    user_login.first_name,
    user_login.surname,
    user_login.login_name,
    user_login.email_address
    FROM
    Central_Enquiry,
    user_login,
    user_menu
    WHERE
    user_login.login_name = user_menu.login_name and
    user_login.login_name = 'MLANGLER'

    ------------------------------
    Martin Langler
    EAST RIDING OF YORKSHIRE COUNCIL
    BEVERLEY
    ------------------------------


  • 2.  RE: Enquiry Email Alerts

    Posted 11 days ago
      |   view attached
    SELECT
    central_enquiry.enquiry_number,
    user_login.first_name,
    user_login.surname,
    user_login.login_name,
    user_login.email_address,
    'Enquiry Number : ' || central_enquiry.enquiry_number ||
    ' , has had a Status Change' as Message_Text1 ,

    'Status is now : ' || central_enquiry.enq_status_code ||
    '. This is just for information as a TEST - PLEASE IGNORE.' as Message_Text2 ,

    'Further information can be added here if required' as Message_Text3

    FROM central_enquiry, user_login, user_menu

    WHERE central_enquiry.enquiry_number = '2146194' AND
    user_login.login_name = user_menu.login_name AND
    user_login.user_login_name IN
    ( 'CHESW00001', 'CHESW00002', 'CHESW00028' ) -- this is list of uses to get email

    GROUP BY
    central_enquiry.enquiry_number,
    user_login.first_name,
    user_login.surname,
    user_login.login_name,
    user_login.email_address,

    'Enquiry Number : ' || central_enquiry.enquiry_number ||
    ' , has had a Status Change',

    'Status is now : ' || central_enquiry.enq_status_code ||
    '. This is just for information as a TEST - PLEASE IGNORE.',

    'Further information can be added here if required'

    ------------------------------
    Barry Jones
    Cheshire West and Cheshire
    Winsford
    ------------------------------

    Attachment(s)

    docx
    Enquiry Alert.docx   158K 1 version


  • 3.  RE: Enquiry Email Alerts

    Posted 11 days ago
    nice bit of script but I guess it is written for Oracle rather than MS Sql as I cheekily tried to pinch it but it didn't work :-)

    ------------------------------
    Chas Black
    Systems Administrator
    Nottingham City Council
    Northampton
    ------------------------------



  • 4.  RE: Enquiry Email Alerts

    Posted 11 days ago
    Try this – I *think* it is just the + instead of ||

    Don't forget to have a look at the attached document to see some screen shots.

    Barry

    SELECT
    central_enquiry.enquiry_number,
    user_login.first_name,
    user_login.surname,
    user_login.login_name,
    user_login.email_address,
    'Enquiry Number : ' + central_enquiry.enquiry_number +
    ' , has had a Status Change' as Message_Text1 ,

    'Status is now : ' + central_enquiry.enq_status_code +
    '. This is just for information as a TEST - PLEASE IGNORE.' as Message_Text2 ,

    'Further information can be added here if required' as Message_Text3

    FROM central_enquiry, user_login, user_menu

    WHERE central_enquiry.enquiry_number = '2146194' AND
    user_login.login_name = user_menu.login_name AND
    user_login.user_login_name IN
    ( 'CHESW00001', 'CHESW00002', 'CHESW00028' ) -- this is list of uses to get email

    GROUP BY
    central_enquiry.enquiry_number,
    user_login.first_name,
    user_login.surname,
    user_login.login_name,
    user_login.email_address,

    'Enquiry Number : ' + central_enquiry.enquiry_number +
    ' , has had a Status Change',

    'Status is now : ' + central_enquiry.enq_status_code +
    '. This is just for information as a TEST - PLEASE IGNORE.',

    'Further information can be added here if required'

    ------------------------------
    Barry Jones
    Cheshire West and Cheshire
    Winsford
    ------------------------------



  • 5.  RE: Enquiry Email Alerts

    Posted 6 days ago
    Hi Barry

    Just to let you know that the SQL was spot on thank you.

    I have tweaked it a little to give me even more info to put on the email as well.

    Regards

    Martin Langler
    ICT Applications Administrator
    Tel
    :   (01482) 395325
    Web:
     
    www.eastriding.gov.uk
    Twitter:  
    www.twitter.com/East_Riding
    Facebook:  
    www.facebook.com/eastridingcouncil