Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.
@ command if structure getting unmanageable
Hello All,
I have this @ command If structure that started out small and has since been added onto and onto. Now I'm getting an error the second time around on the approval button.( This is the logic of an approval button on a request form) There are potentially 3 approvers needed. 3rd if its an admin request. the first pass through works fine but the second approval button bush gives a "sendto" not populated error. This if structure is geting very tedious. There has to be another way. Please help !
Here is the if structure:
@If(HBXsec_host_type = "Administrator" & HBXsec_acceptance = "Acknowledgement not received" & HBXsec_super_dt != "";@Prompt([Ok];"Acknowled
@Do(
@If(HBXsec_super_dt = "";
@Do(
@SetField("HBXsec_super_dt
@MailSend(HBXsec_manager;"
HBXsec_manager_dt = "";
@Do(
@SetField("HBXsec_manager_
@If(@Contains(HBXsec_role;
@MailSend("RDC Scheduling Administrators";"";"";"A New HBX Security Access Request has been created and requires ZEKE and or ZEBB attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );"");
@If(@Contains(HBXsec_role;
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created and requires Cisco VPN attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );"");
@If(HBXsec_host_type = "Administrator";
@MailSend(RDCsec_spcl_app;
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created for your Review and Processing.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] )));
HBXsec_spcl_dt = "" & HBXsec_host_type = "Administrator";
@Do(
@SetField("HBXsec_spcl_dt"
@If(@Contains(HBXsec_role;
@MailSend("RDC Scheduling Administrators";"";"";"A New HBX Security Access Request has been created and requires ZEKE and or ZEBB attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );"");
@If(@Contains(HBXsec_role;
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created and requires Cisco VPN attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );"");
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created and received Approval and is Pending Data Security Processing. ";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Promote Request Doc Link => "; [IncludeDoclink] ));"");
@If(HBXsec_role = "LSS RDC Account";
@MailSend("Karen G Throckmorton/USA/CSC";"";"
@Command([FileSave]);
@Command([FileCloseWindow]
Thank You !
Paul
I have this @ command If structure that started out small and has since been added onto and onto. Now I'm getting an error the second time around on the approval button.( This is the logic of an approval button on a request form) There are potentially 3 approvers needed. 3rd if its an admin request. the first pass through works fine but the second approval button bush gives a "sendto" not populated error. This if structure is geting very tedious. There has to be another way. Please help !
Here is the if structure:
@If(HBXsec_host_type = "Administrator" & HBXsec_acceptance = "Acknowledgement not received" & HBXsec_super_dt != "";@Prompt([Ok];"Acknowled
@Do(
@If(HBXsec_super_dt = "";
@Do(
@SetField("HBXsec_super_dt
@MailSend(HBXsec_manager;"
HBXsec_manager_dt = "";
@Do(
@SetField("HBXsec_manager_
@If(@Contains(HBXsec_role;
@MailSend("RDC Scheduling Administrators";"";"";"A New HBX Security Access Request has been created and requires ZEKE and or ZEBB attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );"");
@If(@Contains(HBXsec_role;
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created and requires Cisco VPN attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );"");
@If(HBXsec_host_type = "Administrator";
@MailSend(RDCsec_spcl_app;
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created for your Review and Processing.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] )));
HBXsec_spcl_dt = "" & HBXsec_host_type = "Administrator";
@Do(
@SetField("HBXsec_spcl_dt"
@If(@Contains(HBXsec_role;
@MailSend("RDC Scheduling Administrators";"";"";"A New HBX Security Access Request has been created and requires ZEKE and or ZEBB attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );"");
@If(@Contains(HBXsec_role;
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created and requires Cisco VPN attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );"");
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created and received Approval and is Pending Data Security Processing. ";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Promote Request Doc Link => "; [IncludeDoclink] ));"");
@If(HBXsec_role = "LSS RDC Account";
@MailSend("Karen G Throckmorton/USA/CSC";"";"
@Command([FileSave]);
@Command([FileCloseWindow]
Thank You !
Paul
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Wow. Maybe that has reformatted for pasting here but you might find it easier to put carriage returns in the @if statement for instance like you would in other languages (it does not matter if you split across line). Doing that does tend to help show where errors are, though @formula is always interesting to work with!
Best I could do with working out quickly re-formatting is below. I haven't worked out where the two trailing )) come into it yet, was this the whole @formula?
You could bring this into a LotusScript version, or simplify it here for viewing purposes by putting those @mailsend subject lines into variables at the top of the script. or re-using them by standardising them, e.g.
then you can replace a longer line like:
with
I haven't got time to debug it through at the moment, maybe someone else will but making it clearer what is going by formatting the code does help a lot IMO.
Steve
Best I could do with working out quickly re-formatting is below. I haven't worked out where the two trailing )) come into it yet, was this the whole @formula?
You could bring this into a LotusScript version, or simplify it here for viewing purposes by putting those @mailsend subject lines into variables at the top of the script. or re-using them by standardising them, e.g.
subject1:="A New HBX Security Access Request has been created and requires ";
doclink1:="Please click on the doclink listed here and review the request."+ @NewLine + @NewLine + "Request Doc Link => ";
then you can replace a longer line like:
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created and requires Cisco VPN attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );
with
@MailSend("RDC Data Security";"";"";subject1 + "Cisco VPN attention.";"";doclink1 ; [IncludeDoclink] );
I haven't got time to debug it through at the moment, maybe someone else will but making it clearer what is going by formatting the code does help a lot IMO.
Steve
@If(HBXsec_host_type = "Administrator" & HBXsec_acceptance = "Acknowledgement not received" & HBXsec_super_dt != "";
@Prompt([Ok];"Acknowledgement Not Received"; "Approving Manager MUST acknowledge that he or she accepts the defined terms of the access for the User. Please Read the Terms of the access agreement and Select the Accept option. The APPROVER takes responsibility for the users acceptance of the terms")
;
@Do(
@If(HBXsec_super_dt = "";
@Do(
@SetField("HBXsec_super_dt";@Today);
@MailSend(HBXsec_manager;"";"";"A New HBX Security Access Request has been created and is pending Manager Approval.";"";"Please click on the doclink listed here and review the request. Choose either Approve or Reject to complete your action for this request. " + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] )
);
HBXsec_manager_dt = "";
@Do(
@SetField("HBXsec_manager_dt";@Today);
@If(@Contains(HBXsec_role;"ZEKE / ZEBB");
@MailSend("RDC Scheduling Administrators";"";"";"A New HBX Security Access Request has been created and requires ZEKE and or ZEBB attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );
"");
@If(@Contains(HBXsec_role;"Cisco VPN");
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created and requires Cisco VPN attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );
"");
@If(HBXsec_host_type = "Administrator";
@MailSend(RDCsec_spcl_app;"";"";"A New HBX Security Access Request has been created and is requiring special admin Approval. ";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Promote Request Doc Link => "; [IncludeDoclink] );
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created for your Review and Processing.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] )
)
);
HBXsec_spcl_dt = "" & HBXsec_host_type = "Administrator";
@Do(
@SetField("HBXsec_spcl_dt"; @Today);
@If(@Contains(HBXsec_role;"ZEKE / ZEBB");
@MailSend("RDC Scheduling Administrators";"";"";"A New HBX Security Access Request has been created and requires ZEKE and or ZEBB attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );
"");
@If(@Contains(HBXsec_role;"Cisco VPN");
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created and requires Cisco VPN attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );
"");
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created and received Approval and is Pending Data Security Processing. ";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Promote Request Doc Link => "; [IncludeDoclink] )
);
"");
@If(HBXsec_role = "LSS RDC Account";
@MailSend("Karen G Throckmorton/USA/CSC";"";"";"FYI... A New HBX Security Access Request has been created and is requesting LSS RDC Account userid type.";"";"Please click on the doclink listed here to view the request. " + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );
"");
@Command([FileSave]);
@Command([FileCloseWindow])
)
)
definitely a good idea trying to streamline the structure. I'm also thinking about 3 sections within it that self contains approval for each time through including the save and close. That way I have 3 separate if structures.
Yes, would agree. Along with using @failure and @return(@prompt([OK]...) instead.
If you can write out in pseudo-code or structured out in words what you are WANTING it to do can help build your @formula if needed
Steve
If you can write out in pseudo-code or structured out in words what you are WANTING it to do can help build your @formula if needed
Steve
what its looking to do in words is this. Each request form requires approvals. The approval button logic is used for all 3 approvers. therefore each time through the logic checks the approval date field. So first time the supervisor approval date is blank means the supervisor hasn't approved yet. He hit ts the approve button. The logic updates the approval field and sends an email to the manager. Then the manager opens the link and hits approve. The logic checks the manager approval date field and updates it if blank and then sends an email to a third approver. but only if administrator authority is being requested. Then the 3rd approver opens and hits approve. The logic checks that approval date field and then populates it and sends a final email to the data security group. also once the manager approval is done, an email also goes to other groups if those options are selected in the form to notify them that work is requesred from that group too.
OK. You may actualy find it easier and less confusing to make 3-4 buttons. Set the hide when formulas based on the criteria you say (i.e. the dates completed etc.) and then only the relevant button will appear to the user -- they could look exactly the same or be called differently as you wish.
Really busy at the moment but will review later if I can.
Steve
Really busy at the moment but will review later if I can.
Steve
I actually thought about that... but once I split the 3 passes through the approval flow the logic seems simplier. Here is the updated logic with the 3 approval separated.
@If(HBXsec_host_type = "Administrator" & HBXsec_acceptance = "Acknowledgement not received" & HBXsec_super_dt != "";@Prompt([Ok];"Acknowled
@Do(
*-------------------------
@If(HBXsec_super_dt = "";
@Do(
@SetField("HBXsec_super_dt
@MailSend(HBXsec_manager;"
@Command([FileSave]);
@Command([FileCloseWindow]
*-------------------------
@If(HBXsec_manager_dt = "";
@Do(
@SetField("HBXsec_manager_
@If(@Contains(HBXsec_role;
@MailSend("RDC Scheduling Administrators";"";"";"A New HBX Security Access Request has been created and requires ZEKE and or ZEBB attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );"");
@If(@Contains(HBXsec_role;
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created and requires Cisco VPN attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );"");
@If(HBXsec_role = "LSS RDC Account";
@MailSend("Karen G Throckmorton/USA/CSC";"";"
@If(HBXsec_host_type = "Administrator";
@MailSend(RDCsec_spcl_app;
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created for your Review and Processing.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] ));
@Command([FileSave]);
@Command([FileCloseWindow]
*-------------------------
@If(HBXsec_spcl_dt = "" & HBXsec_host_type = "Administrator";
@Do(
@SetField("HBXsec_spcl_dt"
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created and received Approval and is Pending Data Security Processing. ";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Promote Request Doc Link => "; [IncludeDoclink]);
@Command([FileSave]);
@Command([FileCloseWindow]
@If(HBXsec_host_type = "Administrator" & HBXsec_acceptance = "Acknowledgement not received" & HBXsec_super_dt != "";@Prompt([Ok];"Acknowled
@Do(
*-------------------------
@If(HBXsec_super_dt = "";
@Do(
@SetField("HBXsec_super_dt
@MailSend(HBXsec_manager;"
@Command([FileSave]);
@Command([FileCloseWindow]
*-------------------------
@If(HBXsec_manager_dt = "";
@Do(
@SetField("HBXsec_manager_
@If(@Contains(HBXsec_role;
@MailSend("RDC Scheduling Administrators";"";"";"A New HBX Security Access Request has been created and requires ZEKE and or ZEBB attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );"");
@If(@Contains(HBXsec_role;
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created and requires Cisco VPN attention.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] );"");
@If(HBXsec_role = "LSS RDC Account";
@MailSend("Karen G Throckmorton/USA/CSC";"";"
@If(HBXsec_host_type = "Administrator";
@MailSend(RDCsec_spcl_app;
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created for your Review and Processing.";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Request Doc Link => "; [IncludeDoclink] ));
@Command([FileSave]);
@Command([FileCloseWindow]
*-------------------------
@If(HBXsec_spcl_dt = "" & HBXsec_host_type = "Administrator";
@Do(
@SetField("HBXsec_spcl_dt"
@MailSend("RDC Data Security";"";"";"A New HBX Security Access Request has been created and received Approval and is Pending Data Security Processing. ";"";"Please click on the doclink listed here and review the request." + @NewLine + @NewLine + "Promote Request Doc Link => "; [IncludeDoclink]);
@Command([FileSave]);
@Command([FileCloseWindow]
To simplify more you could put one FileSave/FileCloseWindow at the end and instead bring the check that should just prompt to stop at the top.
@If(HBXsec_host_type = "Administrator" & HBXsec_acceptance = "Acknowledgement not received" & HBXsec_super_dt != "";@Failure("Approving Manager MUST acknowledge that he or she accepts the defined terms of the access for the User. Please Read the Terms of the access agreement and Select the Accept option. The APPROVER takes responsibility for the users acceptance of the terms");"");
Have you got that working now then or still need some help with the logic?
@If(HBXsec_host_type = "Administrator" & HBXsec_acceptance = "Acknowledgement not received" & HBXsec_super_dt != "";@Failure("Approving Manager MUST acknowledge that he or she accepts the defined terms of the access for the User. Please Read the Terms of the access agreement and Select the Accept option. The APPROVER takes responsibility for the users acceptance of the terms");"");
Have you got that working now then or still need some help with the logic?
It may well be that you need to change the order too, put 3 then 2 then 1 as otherwise it may trigger the others too.
I believe I have solved the problem. With the sendto not defined error anyways. The logic from this button is a clone from another form and I still had one of the field names from the old form in the email address, thus the address was not found on the form giving the error. I did do some fine tuning on the logic and it is manageable now. Thank You !! your recommendations were helpful. Much appreciated.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial