Approvals and Email Notifications

The Architect Manager Administrator can set up approval workflows within SDA. With this functionality, the SDA Administrator can:

• Require quote file approvals by role (sales engineer, VP, etc.) based on workflow rules that are set (e.g., margin less than X%, solution with more than Y users).
• Set passwords by person and role that enable quote files to be approved.
• Send email notifications containing a link to the quote file, along with any reasons why a quote needs to be approved.
• Lock quotes (from further modification) following approval.
• Hide costs within quote files that can be unlocked and shown via the entry of a password.

There are two components required when setting up the Approvals functionality:

• Entry of parameters and passwords (by person and role) within the Approvals dialog found within Architect Manager under File/Setup.
• Programming of workflow rules and approved formulas within one or more of SDA’s tabs (e.g., MiscProd, Base Info, etc.).

To approve quotes, users simply need to click Architect, “Approve quote”, then enter their password to approve the currently-open quote file.

If file locking is enabled, users can unlock a quote file (which also clears out any approvals) by clicking Architect, Unlock quote.

Require password to show cost – Select this option to hide costs in quote files by default.  Costs can be shown in a quote file by selecting the Show cost menu option under the Architect menu, then entering a password.

Users can only hide costs if ALL pricing categories (aka “PriceCats”) are not cost-based (i.e., Basis is NOT equal to Cost), as cost must be present in a quote file in order to calculate sell pricing for pricing categories where Basis = Cost.

Use the “Add Record” button to add passwords by person (“Username”) and Role.  Each password must be unique; you cannot enter the same password twice within the Approvals table.  When entering Role, make sure to enter it consistently for each role so that your Excel formulas can reference the various roles properly.  For example, if you enter “Sales Engineer”, “SE” and “Sales Eng” as entries for individuals who are sales engineers, then you will also need to program your Excel formulas to reference all three versions rather than a single version.

Lock quotes on approval – Select this option to lock quote files from further user modification once the quote has been approved.  Checking this box will also make the Unlock quote menu option appear in the Architect menu.  Users can unlock quote files by selecting that menu option, which will not only unlock the quote file but also remove all approvals from the quote file, meaning that they will need to re-obtain the necessary approvals for the quote, which will in turn re-lock the quote file.

Programming SDA Tabs

When quote files are approved, information about each approver is stamped into a hidden named range called ApprovedBy that you can reference in the formulas on your Excel tabs.  If there are multiple approvers, information will be stamped into multiple rows of the ApprovedBy named range, with one row for each approver.  Column 1 of the ApprovedBy named range contains the Role for each approver, Column 2 contains the name of the approver, and column 3 contains the date/time when the quote file was approved.  For example, if a sales engineer and VP approved a quote file, the following data might be in the ApprovedBy named range…

You can reference the information in the ApprovedBy named range to determine if a quote has been approved by a certain level (i.e., “Role”), as well as to display the name of the approver and the date/time when the quote was approved.  For example, you can…

• Use =IF(COUNTIF(ApprovedBy,”VP”)>0,”Yes”,”No”) to determine if one or more VP-level individuals have approved the quote  and, if so, to put “Yes” in the cell.
• Use =VLOOKUP(“VP”,ApprovedBy,2,FALSE) to return the name of the approving VP from the second column of the ApprovedBy table.
• Use =TEXT(VLOOKUP(“VP”,ApprovedBy,3,FALSE),”m/d/yy h:mm AM/PM”) to return the date/time that the VP approved the quote from the third column of the ApprovedBy table, formatted with a nice date/time format.

You can then tie the approval formulas with workflow rules that you set up.  For example, you can set up a workflow rule that prevents a user from generating an output (using the OutputStatus named range) if the job margin is below a certain level UNLESS the quote is approved by a VP.  See the Using OutputStatus to Control Output Workflow help topic for more information on how to set up workflow rules.

Please note that you will need to add the ApprovedBy named range (with three columns in it) to the Temp Rangenames or Scratch tab of your raw tab so that you don’t get #REF errors while working on the raw tab.

Automating Email Notifications for Approval

You can trigger email notifications to be sent by defining a single-cell named range called NeedsApprovalBy and programming the cell to list the role (e.g., VP, SE) required to approve the quote file.  If this cell is non-blank when a quote file is saved, SDA will prompt the user to select the person (or people) assigned to the role from which approval is required as recipient(s) for the notification email.

If you have multiple levels of approvers, you should program the NeedsApprovalBy cell to display the lower-level approvers before the higher-level approvers.

SDA will auto-prompt the user to send a notification email when the role listed in the NeedsApprovalBy cell has changed; it will not continue to auto-prompt once the notification email has been sent to that role.  However, the user can “manually” send a notification email via the Architect menu.  In addition, the user can clear out all approvals by selecting the “Unlock quote and clear approvals” option under the Architect menu.

A link to the quote file will be inserted into the notification email if the quote file has been saved to a location other than the user’s local drive.  For a quote file saved to the user’s local drive, the user can simply attach the quote file to the notification email.

If you define an ApprovalReasons named range, SDA will copy the text of all non-blank cells within the first column of that range into the notification email.  If you need only to show a single reason within the notification email, then you can define the ApprovalReasons named range as a single-cell range.  However, if you wish to show multiple approval reasons all at once – perhaps for multiple levels of approvers – you can define the ApprovalReasons named range as a multi-cell range.

If you define EmailBody and/or EmailSubject single-cell named ranges, SDA will use the content in EmailSubject as the email’s subject, and will insert the content in EmailBody in between the link to or mention of the quote file and the ApprovalReasons content.

Below is an example of a table that you might set up to track required approvals at multiple levels, in this case the SE (sales engineer) and VP (vice president) levels…

In addition to the formula shown in the formula bar for cell L39 – which is the NeedsApprovalBy named range – below are the formulas from the “SE” row above…

• In the Required column: =IF(AND(L41<>””,K41=””),”Yes”,””)
• In the Approved column: =IF(COUNTIF(ApprovedBy,I41)>0,”Yes”,””)
• In the Approval Reason column: =IF(TotalSellPrice>25000,”Price exceeds $25k”,””)

Return to Architect Manager User Guide

_________________________________________________________________________________________________________________________________________

ADDITIONAL RESOURCES

MORE APPROVAL INFORMATION

• Programming SDA Tabs (Approvals)
• Automating Email Notifications for Approvals

WEBINARS/VIDEOS

• Approvals and Email Notifications