building a billing system with nintex forms and...
TRANSCRIPT
Building a Billing System with Nintex Forms and Workflow
Erin Glenn, SharePoint Solutions Architect
Goodwill of Central and Coastal Virginia
How We Built Applications On SharePoint
4
CodePlex SharePoint List Adapters (Extract, Load, Transform)
Data Entry SharePoint List Forms/Datasheet View
InfoPath Forms
Process SP Designer Workflows
Presentation Web Part Pages
List Views SSRS
Data Integrity Validations
Relational Database
Transactional Data Normalized Structure
Views
SQL
X
Why Use Nintex?
• Will be migrating to SP2016
• CodePlex List Adapters do not work on SP2016
• InfoPath Support/End of Life
– Issues with IE integration breaking
– Support pain with Office 2016 installs
– Mobile accessibility
• Complex workflow needs
– Send/receive data to/from SQL
• Amount of support available
5
Billing System Needs Description
System to capture time spent with program participants who are funded via funding sources and provide automated reporting for billing and metrics. (Sources are invoiced on a monthly basis and are based on authorized and hours worked.)
6
Former State
• Started as multiple spreadsheets to track case load, hours worked, and requested/authorized hours for month
• Billing reports were manually created as spreadsheets (not linked to other spreadsheets)
7
Issues
• Data entry
– Typos
– Accuracy
– Formulas (non-locked cells in spreadsheets)
• Validations (manual spot-checks)
• No metrics
8
Solution
• Secured site for team
• Tabbed app-like design
• Custom SQL database
– Stored procedures
– Data validations in procedures
– History tables
– Auditing on some tables
• Nintex Forms & Workflows
– Mobile form for time entries
• SSRS Reports 9
SharePoint Lists
Lists • 14 Data Lists • 3 Temporary “Form” Lists • 4 Lookup Lists (for choices fields) • Characteristics:
• Views to check data used in workflow or to edit data (only in some lists)
• IRM to delete items within 90 days
Libraries (not shown) 1 Documents 1 Pages 1 SSRS Reports 1 Scripts (for tabs) 1 Images
SQL ERD
Audits • Trigger driven (database function) • ~6 Tables
History • Code via stored procedure
(application function) • ~10 Tables
Application Interface: Tab Using Native SharePoint Web Parts
• Native SharePoint List View web part • Instructions on page
Application Interface: Tab Using Native SharePoint Web Parts
• Native SharePoint List View web part • Instructions on page • “Manager Actions” uses Audience on web part
Application Interface: Tab Using Nintex Web Part
•Nintex Controls • Panels • SQL Request • SharePoint List View
• Instructions
Application Interface: Tab Using Nintex Web Part
•Nintex Controls • Panels • SQL Request • SharePoint List View
• Instructions
Application Interface: Tab Using Nintex Web Part
•Nintex Controls • Panels • SQL Request • Instructions
• SQL Server Reporting Services • Billing and Metrics reports mimic previous Excel spreadsheets • Reports are in folders with permissions, so you only see the reports which you have
access
Application Interface: Tab Using Native SharePoint Web Parts
Application Interface: Item Page with Nintex Controls
• Native SharePoint Item Page & Ribbon
• Nintex Controls • SQL Request • Panels • Rules
• Instructions
Nintex Form Example Deep Dive
SharePoint List lookup; 2 panels on top of each other with visibility set based on SharePoint group membership. Hidden fields take value from this field (SQL ID of Participant) and puts into list field.
Fields only appear if “Select Action” value is “Add New”. Validation rules on each field for empty values. “Start Date” validation rule where date cannot be less than Current ES Start Date.
Button control with JavaScript to open new page.
Fields only appear if “Select Action” value is “Edit Current”. “Start Date” validation rule where date cannot be less than Current Enrollment Start Date.
Controls which fields appear in which panels (including Save/Cancel buttons in panels). Only appears after participant is chosen in “Select Participant” panel. Value stored in list column for workflow action.
Only displays data using SQL Controls after participant is chosen in “Select Participant” panel.
Nintex Workflow Example
Need to find the SQL ID of the Program Enrollment record of the participant and store in Workflow variable.
Updates list item with Program Enrollment SQL ID.
Determine if action is on new item or edit of existing item.
Participant Employment Specialist Assignment New Assignment
Action Set
• Log custom message (such as variable value) to history list after each action.
• History list has IRM policy to delete items after 90 days.
Nintex Workflow Example
Update list item field, CreatedFrom , to “New Employment Specialist”.
Run stored procedure to create new assignment record in SQL with data from list item. Outputs SQLID of new record and stores in variable. Captures any errors in workflow variable.
Action Set to determine if CreatedFrom field is empty. (This field gets auto-populated from other workflows that create records from other lists.)
Participant Employment Specialist Assignment New Assignment
Declare @OutPartESEnrollmentID int EXEC USP_CreateSEESAssignment @ParticipantEnrollmentID = '{WorkflowVariable:int_PartProgEnrollSQLID}' ,@ESAssignmentStartDate = '{ItemProperty:Start_x0020_Date}' ,@SpecialistADAccount = '{ItemProperty:Employment_x0020_Specialist}' ,@ADUser = '{Common:Initiator}' ,@EmpSpecialistAssignmentID = @OutPartESEnrollmentID OUTPUT Select @OutPartESEnrollmentID
Nintex Workflow Example
Sends email to administrator with error message and list item details.
If no error, updates list item with SQL ID of new assignment record.
Action Set to determine if a SQL error was thrown. (If variable, “msg_SQLError”, is not empty.) (Custom SQL errors are written into stored procedures.)
Stops workflow at this point if error occurred in SQL.
Participant Employment Specialist Assignment New Assignment
Nintex Workflow Example
Updates Participant record in Participants list with new Employment Specialist name. (This is used to filter “Select Participant” pick list to “Current User” filter.
Participant Employment Specialist Assignment New Assignment
Nintex Workflow Example
Need to find the current Employment Specialist and store in workflow variable.
Participant Employment Specialist Assignment Edit Current Assignment
Action Set – Determines if EditEmpSpecialist field in list is empty.
Sets “CurrentES” variable to “EditES” variable, which will be used in stored procedure.
Nintex Workflow Example
Sets workflow variable “EditESValue” to EditEmpSpecialist field in list.
Participant Employment Specialist Assignment Edit Current Assignment
Action Set – Determines if EditEmpSpecialist field in list is not empty.
Nintex Workflow Example
Need to find the current Employment Specialist’s Start Date and store in workflow variable.
Participant Employment Specialist Assignment Edit Current Assignment
Action Set – Determines if EditStartDate field in list is less than 1/1/80 (empty).
Sets “EditESStartDate” variable to “CurrentESStartDate” variable, which will be used in stored procedure.
Nintex Workflow Example Participant Employment Specialist Assignment Edit Current Assignment
Action Set – Determines if EditStartDate field in list is greater than 1/1/80 (not empty).
Sets EditESStartDate variable to “EditStartDate” from list, which will be used in stored procedure.
Nintex Workflow Example Participant Employment Specialist Assignment Edit Current Assignment
Run stored procedure to edit existing assignment record in SQL with data from list item. Captures any errors in workflow variable.
Sends email to administrator with error message and list item details.
Action Set to determine if a SQL error was thrown. (If variable, “msg_SQLError”, is not empty.)
Stops workflow at this point if error occurred in SQL. EXEC USP_EditSEEmpSpecialistAssignment
@EmpSpecialistAssignmentID = '{ItemProperty:ProgramESEnrollmentSQLID}' ,@EmpSpecialistADAccount = '{WorkflowVariable:EditESValue}' ,@EmpSpecialistStartDate = '{WorkflowVariable:dt_EditESStartDate}' ,@ADUser = '{Common:Initiator}'
Nintex Workflow Example Participant Employment Specialist Assignment Edit Current Assignment
Updates Participant record in Participants list with new Employment Specialist name. (This is used to filter “Select Participant” pick list to “Current User” filter.
Time Entries – Web Version
• SQL Controls • Repeating Table (workflow parses data and creates individual records in “Time
Entries” list) • SharePoint List View to allow edits to existing records
• IRM policy to delete items older than previous month. (DeleteDate field calculated and set via workflow.)
Time Entries – Mobile App
• 1 entry per form • Required separate list and workflow • No filter on Participant list by current user (sees all)
Want to know more?
• Need to catch me?
• Following me on Twitter?
– @erinbglenn
• Reading my blog?
– www.erinbglenn.com
• A member of the Richmond SharePoint User Group?
– www.richmondsharepoint.org
• A member of the Richmond Nintex User Group?
– www.meetup.com/Nintex-RVA/
• Let’s connect…
– www.linkedin.com/in/ebglenn