Basic Script to Send E-mail Based On Sequence

[tbfilemaker]

John(1), Sue(2) and Kate(3) need to be notified that a Purchase Request needs authorization. John must sign it first, then Sue and lastly Kate. If John(1) already singned, then Sue(2) needs to be notified and so on.

I have a calculation that marks who needs to be notified next, I call it "Action Calc". I will be running this script every 30 minutes.

I need a script that will notify the person next in line, but I can't quite get it to work. So far I have this:

If ["Action Calc=1"]

Send Mail ["Hi , please approve PR."]

End If

If ["Action Calc=2"]

Send Mail ["Hi , please approve PR."]

End If

If ["Action Calc=3"]

Send Mail ["Hi , please approve PR."]

End If

Currently this calculation e-mails John(1), Sue(2) and Kate(3). If my criteria is met I need it to only e-mail John. And then once John signs it, the script will run again and should e-mail Sue and so on.

Any help would be greatly appreciated.

Thanks in advance!

[bcooney]

When and how is Action Calc changed or set?

[tbfilemaker]

The "Action Calc" is based on a check box that is checked when a signature is needed. In this case when John needs to sign it is set to "1", once it is signed, the "Action Calc" is set back to "0". This works the same for the following individuals.

[Fitch]

In your If statement:

If ["Action Calc=1"]

...is it literally written in quotes like that? If so, it will always evaluate true. Try it without quotes:

If [Action Calc=1]

[bcooney]

How does Action Calc ever equal 2 or 3?

I would add three flag fields:

flag_Sig1Req_c = if isempty (Sig1DT), 1)

flag_Sig2Req_c = if flag_Sig1Req =1 and not isempty(Sig1DT)

flag_Sig3Req_c = if flag_Sig2Req =1 and not isempty (Sig2DT)

and I'd add three date fields:

Sig1DT

Sig2DT

Sig3DT

then a calc (Action Calc) that looks at these flags to determine which email to send. The script that sends the email would set the Sig1DT (Sig2DT and Sig3DT) dates to the get (currentdate) respectively.

[Fitch]

It seems to me an efficient approach would be: rather than use 1, 2, or 3 to flag the next action, use the actual ID of the person who needs to sign. E.g.

If [Action Calc=0]

Exit

End If

Send Mail ["Hi " & & " , please approve PR."]

[tbfilemaker]

I apologize, I forgot to mention there are three different Action Calc fields, sorry about that.

They are set by the approver level. If John(1), Sue(2) and Kate(3) need to approve the PR, those 3 fields will get set to:

Action Calc 1=1

Action Calc 2=2

Action Calc 3=3

Once the approver digitally signs the PR the Action Calc is set back to 0.

I have this part figured out. Where I am running into trouble is in the script that triggers the e-mail.

So what I am looking for is:

If Action Calc 1 = 1, then send e-mail to John, if it is 0 move on to next step

If Action Calc 2=2, then send e-mail to Sue, if it is 0 move on to next step

If Action Calc 3=3, then send e-mail to Kate, if it is 0 exit script.

I hope this clears things up, thanks for your patience and help, it is really appreciated.

[Fitch]

The script in your original post will do what you want, except you need to change the field names to match your most recent post. Also put an Exit script before the End If steps.

But it seems like you need an additional field to flag whether you've already sent the email or not. Unless you want the recipient to get an email every 30 minutes until they sign off. (Or you could just set the flag field back to zero in this script instead of when they sign.)

If [Action Calc1=1 and Email1 sent <>1] // using a flag field

Send Mail ["Hi , please approve PR."]

Exit script

End If

If [Action Calc2=2]

Send Mail ["Hi , please approve PR."]

Set field [Action Calc2 ; 0] // resetting the action field

Exit script

End If

If [Action Calc3=3]

Send Mail ["Hi , please approve PR."]

Exit script // this guy is going to keep getting mail every 30 min.

End If

PS: I don't see why you're setting the fields to 1,2,3. Why not just set them all to 1 or zero? You're just using them as boolean (true or false), right?

[tbfilemaker]

Thanks for all the suggestions here, I am now implementing this into my solution.

Fitch, I am using 1,2 & 3 as flags in order to send the e-mail in the right sequence. I have John(1), Sue(2) and Kate(3). Sometimes only John(1) needs to sign and not the others (and so on). So I have 1,2 & 3 set based on the signatures required. Also, thanks for mentioning setting the flag for when the e-mail has already been sent, I implemented that and it is working great.

Thanks for all your help.