Topic
  • 7 replies
  • Latest Post - ‏2013-08-07T05:46:17Z by CQAdmin
63EG_Barbara_Iszard
63EG_Barbara_Iszard
3 Posts

Pinned topic ClearQuest Email Notification Rule update

‏2013-07-30T18:31:59Z |

I'm trying to write a SQL Select statement for a new Email Notification rule which will send an auto-email to the current user -- ie. the user that modified the ticket.

Although it's true I could create a new field which would be updated every time a record is updated and email to the email address in that field, I'd rather not add to the overhead.

I'm having trouble figuring this out. Or even if it's possible.  I do have select statements in place for other email rules which will send out an email to people in a particular email group. An example would look like this:

SQL("SELECT  T2.email
   FROM  parent_child_links T1, groups T3, users T2
   WHERE T1.parent_dbid = T2.dbid
     AND T1.child_dbid = T3.dbid
     AND T3.name = '<usergroupname>'")

Any help would be appreciated.

  • DonaldN
    DonaldN
    277 Posts

    Re: ClearQuest Email Notification Rule update

    ‏2013-07-31T00:06:56Z  

    Since you are dealing with the "current user", is there any reason that you do not use the "session" object to retrieve such information? You can use GetUserEmail. Other available APIs include GetUserFullName, GetUserLoginName, GetUserMiscInfo and GetUserPhone.

    http://pic.dhe.ibm.com/infocenter/cqhelp/v7r1m2/topic/com.ibm.rational.clearquest.apiref.doc/topics/r_session_getuseremail.htm

  • 63EG_Barbara_Iszard
    63EG_Barbara_Iszard
    3 Posts

    Re: ClearQuest Email Notification Rule update

    ‏2013-07-31T14:18:41Z  
    • DonaldN
    • ‏2013-07-31T00:06:56Z

    Since you are dealing with the "current user", is there any reason that you do not use the "session" object to retrieve such information? You can use GetUserEmail. Other available APIs include GetUserFullName, GetUserLoginName, GetUserMiscInfo and GetUserPhone.

    http://pic.dhe.ibm.com/infocenter/cqhelp/v7r1m2/topic/com.ibm.rational.clearquest.apiref.doc/topics/r_session_getuseremail.htm

    Thanks for your input Donald.

    It would be great if I could use those commands but unfortunately when it comes to entering information into the 'To' box for the Email Notification Rule package it must be one line per addressee or addressee list (if referring to a group of addressees).

    What you're suggesting would be at least 2 lines to compute who the addressee would be.

    For example, I could type something like:

                 $owner.email

                 $submitter.email

    and I'd be fine. It would send emails to the Owner and Submitter.

    Or the SQL statement that I'd noted earlier would work because although it's broken up into multiple lines for readability it's really one long command and could be one line if I wanted to and the interpreter would be fine with that. I could even add the SQL Statement on the line below $submitter.email and that would work OK.

    I can see where the variable $userLoginName will return the login ID of the person modifying the record and thus triggering the new email rule.  I've been trying to create a SQL Select statement that will use that variable to send an email address to that person but have so far been unsuccessful.

    Make sense?  Or possibly I'm all bamboozled.

     

  • pdubovitsky
    pdubovitsky
    376 Posts

    Re: ClearQuest Email Notification Rule update

    ‏2013-07-31T14:56:12Z  

    Thanks for your input Donald.

    It would be great if I could use those commands but unfortunately when it comes to entering information into the 'To' box for the Email Notification Rule package it must be one line per addressee or addressee list (if referring to a group of addressees).

    What you're suggesting would be at least 2 lines to compute who the addressee would be.

    For example, I could type something like:

                 $owner.email

                 $submitter.email

    and I'd be fine. It would send emails to the Owner and Submitter.

    Or the SQL statement that I'd noted earlier would work because although it's broken up into multiple lines for readability it's really one long command and could be one line if I wanted to and the interpreter would be fine with that. I could even add the SQL Statement on the line below $submitter.email and that would work OK.

    I can see where the variable $userLoginName will return the login ID of the person modifying the record and thus triggering the new email rule.  I've been trying to create a SQL Select statement that will use that variable to send an email address to that person but have so far been unsuccessful.

    Make sense?  Or possibly I'm all bamboozled.

     

    You can define a function, for example, GetUserEmail

    In order to do it, you need to submit a new udb_property record with "GetUserEmail" in the name field, and "return $session->GetUserEmail();" in the value field. Then, you can use this function in the "To:" field of the email notification rule:
    GetUserEmail()

    BTW, you can get email notification package assistance on the package web site: http://cqadmin.org 

    Pavel

  • 63EG_Barbara_Iszard
    63EG_Barbara_Iszard
    3 Posts

    Re: ClearQuest Email Notification Rule update

    ‏2013-07-31T19:53:20Z  

    You can define a function, for example, GetUserEmail

    In order to do it, you need to submit a new udb_property record with "GetUserEmail" in the name field, and "return $session->GetUserEmail();" in the value field. Then, you can use this function in the "To:" field of the email notification rule:
    GetUserEmail()

    BTW, you can get email notification package assistance on the package web site: http://cqadmin.org 

    Pavel

    Well there you go.

    Thanks so much for your detailed explaination.  Super helpful.

    In my prior CQ Admin experiance we used the regular cq mail rules package and didn't have any udb_property record type. I'm now having to deal with a different, existing setup and still learning what some would call elementary stuff.  I didn't know enough to know to even ask about setting something up that way.

  • DonaldN
    DonaldN
    277 Posts

    Re: ClearQuest Email Notification Rule update

    ‏2013-08-01T00:04:18Z  

    Thanks for your input Donald.

    It would be great if I could use those commands but unfortunately when it comes to entering information into the 'To' box for the Email Notification Rule package it must be one line per addressee or addressee list (if referring to a group of addressees).

    What you're suggesting would be at least 2 lines to compute who the addressee would be.

    For example, I could type something like:

                 $owner.email

                 $submitter.email

    and I'd be fine. It would send emails to the Owner and Submitter.

    Or the SQL statement that I'd noted earlier would work because although it's broken up into multiple lines for readability it's really one long command and could be one line if I wanted to and the interpreter would be fine with that. I could even add the SQL Statement on the line below $submitter.email and that would work OK.

    I can see where the variable $userLoginName will return the login ID of the person modifying the record and thus triggering the new email rule.  I've been trying to create a SQL Select statement that will use that variable to send an email address to that person but have so far been unsuccessful.

    Make sense?  Or possibly I'm all bamboozled.

     

    Right, I did not follow your thought properly. Now I realize that you are setting up Email Rules. Normally when the built-in function does not provide the satisfactory result, I will opt for a script-only solution, hence my earlier suggestion. You can find more details here.

    http://pic.dhe.ibm.com/infocenter/cqhelp/v7r1m2/topic/com.ibm.rational.clearquest.schema.ec.doc/topics/t_using_act_hook_em.htm

    http://pic.dhe.ibm.com/infocenter/cqhelp/v7r1m2/topic/com.ibm.rational.clearquest.apiref.doc/topics/r_examples_actnotifction.htm

  • pdubovitsky
    pdubovitsky
    376 Posts

    Re: ClearQuest Email Notification Rule update

    ‏2013-08-01T00:54:20Z  
    • DonaldN
    • ‏2013-08-01T00:04:18Z

    Right, I did not follow your thought properly. Now I realize that you are setting up Email Rules. Normally when the built-in function does not provide the satisfactory result, I will opt for a script-only solution, hence my earlier suggestion. You can find more details here.

    http://pic.dhe.ibm.com/infocenter/cqhelp/v7r1m2/topic/com.ibm.rational.clearquest.schema.ec.doc/topics/t_using_act_hook_em.htm

    http://pic.dhe.ibm.com/infocenter/cqhelp/v7r1m2/topic/com.ibm.rational.clearquest.apiref.doc/topics/r_examples_actnotifction.htm

    "Normally when the built-in function does not provide the satisfactory result, I will opt for a script-only solution"

    In my opinion, there is a better option -  EmailNotification package. It gives you ultimate flexibility in implementing any possible notification scenario.

    Pavel

    Updated on 2013-08-01T00:56:36Z at 2013-08-01T00:56:36Z by pdubovitsky
  • CQAdmin
    CQAdmin
    103 Posts

    Re: ClearQuest Email Notification Rule update

    ‏2013-08-07T05:46:17Z  

    One good advantage of using  EmailNotification package is - if you want to modify your email rules/settings/parameters etc., do it on-the-fly. In case of using hook scripting, you have to create schema versions every time you want to modify.

    :-)