email notification for values > 60

[bufhal2]

Hello; I hope someone can offer some assistance..
I need help with the following:
A section of my database contains fields (evalterm_1, through evalterm_5) Currently the fields do not effect the email.
I need to change the code so when the fields are > 60, a specail email notification must be sent. If < 60, just the ordinary email notification (below is the code for the ordinary email that is sent regardless of what is stored in the fields.

Here is the email code:

@PostedCommand([EditDocument]; "1");
@MailSend(salesmgr;Author;coordinator; @Name([CN];@UserName)+" has submitted a request to extend an eval for "+Company+". "+@Name([CN];salesmgr) +" please review and approve or reject extension";"";"";[IncludeDoclink] );
FIELD sentman_3 := "Eval extension request was sent by "+@Name([CN];@UserName)+" on " +@Text(@Today)+" for review";
FIELD Status_1 := "Sent to Manager";
FIELD ext1 := @Today;
@PostedCommand([FileSave]);
@PostedCommand([FileCloseWindow])

Can the current code be edited to send to just the salesmgr and one other person if the values add up to > 60 and just do what it does now if the value < 60?

If anyone can help me , it would be greatly appreciated...

 

[pmonett]

You can branch out of a formula on a condition :

@if(evalterm_1>60;@return("");"");

So, you can make a new button that will only execute if your fields are >60.

Pascal.

 

[bufhal2]

Thanks for your reply, Pascal.
I am not evry experiienced at Formula and Lotusscript and appreciate your help.

So in your suggestion, the user would have to click a seperate button?
I need the fields to be automatically calculated so when they exceed 60, an email will be sent to salesmgr and one other person.

If the values are < 60, it will just go through the normal code (above).

 

[pmonett]

I would suggest creating two agents, one for the normal send, one for the >60 send.

Then a formula should be put in the QuerySave event of the form. The formula should check if send conditions are true, and activate one of the two agents, then set a flag so as not to do it again (unless you want to send a mail every time the doc is saved).

The agent code you can easily derive from what you already have. The QuerySave code should look something like this :

@if(mailflag="";"";@return(@true);
tempval := @if(evalterm_1 > 60 & evalterm_2 > 60 & evalterm_3 > 60 & evalterm_4 > 60 & evalterm_5 > 60;1;0);
@if(
    tempval > 0;
    @command([RunAgent];"Notification60more");
    @command([RunAgent];"Notification60less")
);
FIELD mailflag := "Y";
@return(@true)

Something along that line should do what you want automatically. Of course, this code example assumes that the mail is to be sent as soon as the document is saved, and only once. If that is not the case, you'll have to modify it.

Have fun !

Pascal.

 

[bufhal2]

Thanks Pascal;
I am trying this without the agents and I know the start of the code should be something similar to this:
@If(
Condition1; @Mailsend<list1 message1>;
@mailsend<other list and message);

How could I include a Dblookup to get the sum of the fields, evalterm_1 throught evalterm_5.
Sorry I am so unclear on how the entire code should be put together.

 

[pmonett]

If I'm not mistaken, you're checking values on the document that you are controlling - therefor you have no need of a @DbLookup. All you need to do is this :

EvalSum := evalterm_1 + evalterm_2 + evalterm_3 + evalterm_4 + evalterm_5;
@If(EvalSum > 60; . . .

By assigning values to temporary variables, you can, in practice, code the equivalent of branches in other, more powerful languages.
Generally, in formula language you can simply create temp variables for every case of data you may need, then write the conditions that take into account the required variables following which case you are in.
To be specific, what you can do here is create a variable to store the names of the <60 condition, and another variable for the names of the >60 condition.
You can create temp subject variables for each condition too.
Then, in you condition code, you can put the right parameters in the @Mailsend following the result of the EvalSum check.

Simple, but hardly elegant. Gets the job done, though.

Pascal.

 

[bufhal2]

Hi Pascal;
thanks again. I keep getting an error for right parenthisis and I also do not think I have this quite right, but here goes:

###this first part is to mail to salesmgr and bufhal if the sum of the 5 fields is > 60 ###

EvalSum := evalterm_1 + evalterm_2 + evalterm_3 + evalterm_4 + evalterm_5;
@If(EvalSum > 60; @MailSend(salesmgr;"bufhal";@Name([CN];@UserName)+" has submitted an Eval Request for "+Company+". "+@Name([CN];salesmgr)+" please review and approve or reject";"";"";[IncludeDoclink] );


###this section(together with the first, will send the email to all within it IF the sum of the fields is < 60 ###

@PostedCommand([EditDocument]; "1");
@If(EvalSum < 60; @MailSend(salesmgr;Author;coordinator; @Name([CN];@UserName)+" has submitted an Eval Request for "+Company+". "+@Name([CN];salesmgr)+" please review and approve or reject";"";"";[IncludeDoclink] );
FIELD sentman := "Eval request was sent by "+@Name([CN];@UserName)+" on " +@Text(@Today)+" for review";
FIELD Status := "Sent to Manager";
FIELD Finalstatus := "New";
@PostedCommand([FileSave]);
@PostedCommand([FileCloseWindow])

I know this is close, please show me how to get this working. Your help is greatly appreciated-this code will be referred to as part of my learning curve.
Thank you, Pascal.

 

[pmonett]

Sorry for the delay, but I've been a bit overloaded these past few days.

Anyway, your code issue is that you haven't finished your last @IF statement. Right now, the field assignments you make are part of the ELSE section of the @IF. If that is where they are supposed to be (which I doubt), then you need to enclose them in a @Do statement.

If the field assignments are part of the mail send (more logical), then you need to change the code to something more like this :

WkSubjectLine := @Name([CN];@UserName)+" has submitted an Eval Request for "+Company+". "+@Name([CN];salesmgr)+" please review and approve or reject";
WkFieldValue := "Eval request was sent by  "+@Name([CN];@UserName)+" on " +@Text(@Today)+"  for review";
@If(EvalSum < 60;
   @do(
      @MailSend(salesmgr;Author;coordinator;WkSubjectLine;"";"";[IncludeDoclink] );
      FIELD sentman := WkFieldValue;
      FIELD Status := "Sent to Manager";
      FIELD Finalstatus := "New");
      "");
@PostedCommand([FileSave]);
@PostedCommand([FileCloseWindow])

That way, the long text stuff is placed in temporary variables until you need them, making the @If and @Do statements easier to read.

Pascal.

 

[bufhal2]

Hello;
Thank you for your past help-I am back on this project after being pulled away. I hope some on can help me with the button code below.
The users have informed me how they want this to work. If
any of the Eval Extension Periods 2-5 have a value > than 30, the email will be sent to salesmgr then sent to "bufhal".

Here are the selections on the form:

Eval Extension (If needed):
====================================================================
Original Due Date: 02/05/2006
Eval Extension Period 1: 80 days
Eval Extension Period 2: 0 days
Eval Extension Period 3: 0 days
Eval Extension Period 4: 0 days
Eval Extension Period 5: 0 days
New Due Date with Extensions: 04/26/2006

Here is what I have so far:

@PostedCommand([EditDocument]; "1");
@MailSend(salesmgr;Author;bufhal; @Name([CN];@UserName)+" has submitted a request to extend an eval for "+Company+". "+@Name([CN];salesmgr) +" please review and approve or reject extension";"";"";[IncludeDoclink] );
FIELD sentman_3 := "Eval extension request was sent by "+@Name([CN];@UserName)+" on " +@Text(@Today)+" for review";
FIELD Status_1 := "Sent to Manager";
FIELD ext1 := @Today;
@PostedCommand([FileSave]);
@PostedCommand([FileCloseWindow])

Can anyone help me get this button code to do the job--it is close but not quite right..

Thank you in advance.

 

[bufhal2]

This is the other section

@If(evalterm_2;evalterm_3;evalterm_4;evalterm_5;>30@MailSend(salesmgr;"bufhal";Author;coordinator; @Name([CN];@Username);

 

[bufhal2]

Hello;
Hope someone can take a look and see where I am wrong. This is where I am at trying to accomplish me previous post.

@PostedCommand([EditDocument]; "1");
EvalSum := evalterm_1 | evalterm_2 | evalterm_3 | evalterm_4 | evalterm_5;
@If(EvalSum >= 30;@do(@MailSend(salesmgr;"Bufhal"; @Name([CN];@UserName)+" has submitted a request to extend an eval for "+Company+". "+@Name([CN];salesmgr) +" please review and approve or reject extension";"";"";[IncludeDoclink] );
Else;
@MailSend(salesmgr;Author;coordinator; @Name([CN];@UserName)+" has submitted a request to extend an eval for "+Company+". "+@Name([CN];salesmgr) +" please review and approve or reject extension";"";"";[IncludeDoclink] );
FIELD sentman_3 := "Eval extension request was sent by "+@Name([CN];@UserName)+" on " +@Text(@Today)+" for review");
FIELD Status_1 := "Sent to Manager";
FIELD ext1 := @Today;
@PostedCommand([FileSave]);
@PostedCommand([FileCloseWindow]));

If someone can see where I am wrong, please let me know.
Any help is greatly appreciated.

 

[pmonett]

One problem I see is here :

EvalSum := evalterm_1 |  evalterm_2 | evalterm_3 | evalterm_4 | evalterm_5;

I think you want the max value on that. What you are doing is using the OR operator, but without a conditional expression. I would use @max() for that, like this :

EvalSum := @max(evalterm_1:evalterm_2:evalterm_3:evalterm_4:evalterm_5)

This way, you'll know that EvalSum contains the highest value of the five, and your test should work better.

The next point I would have is that it seems that you have an error in the @if. Specifically, I don't see the use of @do as correct.
If I point out the bracket assignments, it looks like this :

@If(
   EvalSum >= 30;
   @do[COLOR=red][b]([/b][/color]@MailSend[COLOR=blue][b]([/b][/color]salesmgr;"Bufhal"; @Name[b]([/b][CN];@UserName[b])[/b]+" has submitted a request to extend an eval for "+Company+". "+@Name[COLOR=green][b]([/b][/color][CN];salesmgr[COLOR=green][b])[/b][/color] +" please review and approve or reject extension";"";"";[IncludeDoclink][COLOR=blue][b])[/b][/color];
[COLOR=red]Else;[/color] [COLOR=dark grey]<-- meaningless[/color]
@MailSend[COLOR=cyan][b]([/b][/color]salesmgr;Author;coordinator; @Name[b]([/b][CN];@UserName[b])[/b]+" has submitted a request to extend an eval for "+Company+". "+@Name[COLOR=green][b]([/b][/color][CN];salesmgr[COLOR=green][b])[/b][/color] +" please review and approve or reject extension";"";"";[IncludeDoclink][COLOR=cyan][b])[/b][/color];
FIELD sentman_3 := "Eval extension request was sent by  "+@Name[b]([/b][CN];@UserName[b])[/b]+" on " +@Text[COLOR=yellow][b]([/b][/color]@Today[COLOR=yellow][b])[/b][/color]+"  for review"[COLOR=red][b])[/b][/color];
FIELD Status_1 := "Sent to Manager";
FIELD ext1 := @Today;
@PostedCommand[b]([/b][FileSave][b])[/b];
@PostedCommand[b]([/b][FileCloseWindow][b])[/b]
);

Seeing what you have written, I think I can propose this alternative :

@PostedCommand([EditDocument]; "1");
EvalSum := @max(evalterm_1:evalterm_2:evalterm_3:evalterm_4:evalterm_5);
Recipients := @If(EvalSum >= 30;salesmgr:"Bufhal";salesmgr:Author:coordinator);
Subject := @Name([CN];@UserName)+" has submitted a request to extend an eval for "+Company+". "+@Name([CN];salesmgr) +" please review and approve or reject extension";
@MailSend(recipients;"";"";subject;"";"";[IncludeDoclink]);
FIELD sentman_3 := "Eval extension request was sent by  "+@Name([CN];@UserName)+" on " +@Text(@Today)+"  for review";
FIELD Status_1 := "Sent to Manager";
FIELD ext1 := @Today;
@PostedCommand([FileSave]);
@PostedCommand([FileCloseWindow])

As you can see, this is slightly more readable thanks to the use of temporary variables and restricting the @if to simply defining the list of recipients.

Pascal.

 

[bufhal2]

Thank you for your help Pascal;
I created an agent from your earlier suggestion only I am having a hard time testing this due to our environment.
I want it the agent to send the notification to salesmgr and then bufhal when any of the evals(2-5) are 30. The only thing I know at this point is that there are no errors in the programmer's pane ;-))With your obvious expertise, Pascal, can you tell if this agent will run, ....or tell me if I am way off base? I really appreciate your help.
Here is the agent code:


sendflag := @If(evalterm_2 = 30; "1"; evalterm_3 = 30; "1"; evalterm_4 = 30; "1"; evalterm_5 = 30; "1"; "");

@If(sendflag = "1"; @MailSend(salesmgr;"Bufhal";Author;coordinator; @Name([CN];@UserName)+" has submitted a request to extend an eval for "+Company+". "+@Name([CN];salesmgr) +" please review and approve or reject extension";"";"";[IncludeDoclink]);
FIELD sentman_3 := "Eval extension request was sent by "+@Name([CN];@UserName)+" on " +@Text(@Today)+" for review";
FIELD Status_1 := "Sent to Manager";
FIELD ext1 := @Today);
SELECT @All

The agent option is "shared"
runtime is "on event" and "after documents are created or modified"

Does this make sense.
Thank you again...

 

[pmonett]

I don't see why it shouldn't run.

One remark, though : is there any chance that the eval fields get a value above 30 ? If yes, your equal signs will skip that fact and no message will be sent.

Pascal.