2. ASPRunnerPro General questions
  3. Tracking Update and add send email to group
This topic is locked

Tracking Update and add send email to group

 		7/22/2008 12:58:52
ASPRunnerPro General questions
W
 wisdom2009 author

Hi,

I have three tables :

1- Chain : have all the data

2- History : Empty table but have the same field as Chain just three more fields added is userid , timeand mode(could be add,delete, update)

3- userlist: has the users informations : userid, password, name and email.
What i would like to do, is when a user login and add/delete or update, an email will be sent to all the group in userlist including who made the change which will be the name from userlist tabel and what are the new informarion, also i would like informations that was update/ deleted to be insert into the history table include only the fields that were updated and provide who updated and time plus mode (per example if they update the mode will be update).
Thank you so much,
I know it is two much, i kept reading all the answers on previews topic but none of them help.
Thank you again.
Hicham
J
 		Jane 7/23/2008

Hi,
use Before record added/updated/deleted events on the Events tab to insert record to the History table and send email to users.

Use Save new data in another table and Send email with new data actions as a sample.
W
 		wisdom2009 author 7/23/2008

Hi Jane,
Thanks for your reply, I just did what you said. when i try to add something new to the database i receive the following error message :.
This is the code i added :
[color="#808080"]'** Insert a record into another table ****

strSQLInsert = "insert into History (Customer_NAME,LINK TO, LOCKBOX_ASSIGNMENT,AGENT_No,AGENT_EXT,NAMING_STANDARDS,DEX_YorN,PO_YorN,DSD_Y_or

_N,STORE# _AmtofDigits,STORE_STAMP_REQUIRED,SEPARATE_CREDIT_INV_Y_or_N,Other_Important_INF

O,STMT@PARENT_MID-LEVEL_only,Stmt_Criteria_PRODUCE_YOrN,METHODE I_ O,CYCLE_CODE,DETAIL_YorN,CURRENT_ACTIVITY_YorN,INVOICE_REQUIRED_YorN,DUNNING,AUT

OW/O_YorN,DSD_FLAG_YorN,UserID,LastTime) values (Customer_NAME,LINK TO, LOCKBOX_ASSIGNMENT,AGENT_No,AGENT_EXT,NAMING_STANDARDS,DEX_YorN,PO_YorN,DSD_Y_or

_N,STORE# _AmtofDigits,STORE_STAMP_REQUIRED,SEPARATE_CREDIT_INV_Y_or_N,Other_Important_INF

O,STMT@PARENT_MID-LEVEL_only,Stmt_Criteria_PRODUCE_YOrN,METHODE I_ O,CYCLE_CODE,DETAIL_YorN,CURRENT_ACTIVITY_YorN,INVOICE_REQUIRED_YorN,DUNNING,AUT

OW/O_YorN,DSD_FLAG_YorN, '" & Session("UserID") & "',now())"

dbConnection.Execute strSQLInsert
BeforeAdd = True
'** Send email with new data ****

' do not forget to setup email parameters like From, SMTP server etc

' on 'Security->User login settings' dialog
Dim dkeys

message =""
dkeys = dict.keys

For n = 0 To dict.Count-1

message = message & dkeys(n) & " : " & dict(dkeys(n)) & vbcrlf

Next
email=rs("Email")

subject="New data record"
sendmail email, subject, message
jfr 7/23/2008

I can't find the the table where the original data needs to come from.

I have added it in bold.
John

********** Insert a record into another table ************

strSQLInsert = "insert into History (Customer_NAME,LINK TO, LOCKBOX_ASSIGNMENT,AGENT_No,AGENT_EXT,NAMING_STANDARDS,DEX_YorN,PO_YorN,DSD_Y_or

_N,STORE# _AmtofDigits,STORE_STAMP_REQUIRED,SEPARATE_CREDIT_INV_Y_or_N,Other_Important_INF

O,STMT@PARENT_MID-LEVEL_only,Stmt_Criteria_PRODUCE_YOrN,METHOD_E_ I_ O,CYCLE_CODE,DETAIL_YorN,CURRENT_ACTIVITY_YorN,INVOICE_REQUIRED_YorN,DUNNING,AUT

OW/O_YorN,DSD_FLAG_YorN,UserID,LastTime) values (Customer_NAME,LINK TO, LOCKBOX_ASSIGNMENT,AGENT_No,AGENT_EXT,NAMING_STANDARDS,DEX_YorN,PO_YorN,DSD_Y_or

_N,STORE# _AmtofDigits,STORE_STAMP_REQUIRED,SEPARATE_CREDIT_INV_Y_or_N,Other_Important_INF

O,STMT@PARENT_MID-LEVEL_only,Stmt_Criteria_PRODUCE_YOrN,METHOD_E_ I_ O,CYCLE_CODE,DETAIL_YorN,CURRENT_ACTIVITY_YorN,INVOICE_REQUIRED_YorN,DUNNING,AUT

OW/O_YorN,DSD_FLAG_YorN, '" & Session("UserID") & "',now())

FROM " & strTableName & " where " & where

dbConnection.Execute strSQLInsert

BeforeAdd = True
'********** Send email with new data ************

' do not forget to setup email parameters like From, SMTP server etc

' on 'Security->User login settings' dialog
Dim dkeys

message =""
dkeys = dict.keys

For n = 0 To dict.Count-1

message = message & dkeys(n) & " : " & dict(dkeys(n)) & vbcrlf

Next



email=rs("Email")

subject="New data record"



sendmail email, subject, message
J
 		Jane 7/24/2008

Use Save new data in another table action as a sample, not Insert a record into another table.

'** Save new data in another table ****

strSQLSave = "INSERT INTO AnotherTable (Field1, Field2) values ("
strSQLSave = strSQLSave & dict("Field1") & ","

strSQLSave = strSQLSave & dict("Field2")
strSQLSave = strSQLSave & ")"

dbConnection.Execute strSQLSave