Jon Nuss

National Nursery Products Commission Processor Introduction The National Nursery Products (NNP) Commission Processor was developed to combine all commission-related processes into a unified software system that integrates with Dynamics accounting. It provides sales tracking, commission pay-ins and payouts, and allows for the import and export of spreadsheets from sales staff, customers, and suppliers. The client had outgrown their poorly performing Access database and struggled with disparate spreadsheets, reporting tools, and the need for double and triple data entry. The "Commission Processor" was created as the solution. The NNP Commission Processor goes beyond commission management, handling invoices, payments, customers, sales staff, suppliers, reporting, and multi-level commission handling. Technology The NNP Commission Processor runs on the Microsoft .NET Framework (DNFW 4.8) in conjunction with SQL Server. It is a Windows Forms desktop application written in VB.NET, with no third-party library dependencies except for SAP Crystal Reports. The application operates within an Active Directory domain environment. Features Fully integrated SAP Crystal Reports for reporting Complete CRM for customers, suppliers, and sales staff Multi-tiered commission calculations based on internal rules Invoice-centric approach for simplicity and intuitiveness Advanced payment fractioning algorithm for flexible payment splits Microsoft Dynamics Accounting integration High-speed, asynchronous, and transactional operations with redundant calculations Custom ORM utilizing SQL Stored Procedures and ADO.NET bulk operations Data import and export capabilities at all levels Debug mode with logging for troubleshooting and intuitive error messages Customizable field mapping for different spreadsheet imports Operations Accounts Customers Main Form This form allows the entry of customers, with one or more locations and contacts per location. It can be accessed directly from the menu or via the Invoice Main Form. Top section: Select an existing customer from the dropdown or enter a new customer in the BusinessName field Customer type can be designated as either Independent or Chain Record buttons (Add, Update, Cancel, Delete) are enabled/disabled based on the operation "Export As CSV" button exports the customer, all locations, and contacts via a popup dialog Location(s) section: Manage multiple locations for a given customer Select an existing location from the dropdown or enter a new one in the LocationName field Assign a Sales Associate (SA) to a location using the "Ship To SA" dropdown "Cust# Synonyms" button opens a dialog for managing aliases (used for mapping external import fields) Notes section for details about the displayed location Contact(s) section: Manage multiple contacts for a given location Notes section for details about the displayed contact Customers Synonyms Form Sales Associates Main Form This form allows the entry of Sales Associates (SA) information and assignment of Sales Regions. Record buttons (Add, Update, Cancel, Delete) are enabled/disabled based on the operation Regions section allows the assignment of regions for the SA "Edit Regions" button opens the SA Regions Form (described below) Notes section for details about the displayed SA Sales Associates Regions Form This form is used to assign commission rates for a particular Sales Region. If a Sales Associate makes a sale in a particular region, the commission splits defined here are applied (although they can be overridden). Suppliers Main Form This form allows the entry of suppliers, with one or more locations and contacts per location. It can be accessed directly from the menu or via the Invoice Main Form. Top section: Select an existing supplier from the dropdown or enter a new supplier in the BusinessName field Supplier type can be designated as either Standard or Special Record buttons (Add, Update, Cancel, Delete) are enabled/disabled based on the operation "Export As CSV" button exports the supplier, all locations, and contacts via a popup dialog Location(s) section: Manage multiple locations for a given supplier Select an existing location from the dropdown or enter a new one in the LocationName field "Inv Comm Overrides/Cust" button opens the "Invoice Commission Overrides By Customer" window "Comm Splits Overrides/Cust" button opens the "SA Commission Overrides By Customer" window Notes section for details about the displayed location Contact(s) section: Manage multiple contacts for a given location Notes section for details about the displayed contact Suppliers Invoice Overrides Form This form allows an Invoice Commission (percentage) to be entered for a particular customer, which is reflected in the Invoices Main Form. Suppliers Sales Associate Overrides Form This form allows the override of commission splits for a selected customer for the current supplier. Sales Associates can take on different roles (e.g., "Order Writer" and "Ship To") for the same order, or a single SA can have both roles. The "Other" role is reserved for special cases. Invoices Invoices Search Form This form is used for searching invoices based on extensive criteria and filtering options. The more options selected, the more refined the search output in the table below. The "Invoice Search Results" tab displays a quick summary of common fields, while the "Verbose Search Results" tab shows all fields returned from the query, typically used for exporting data. Search results can be exported or printed, and clicking on any record opens the Invoices Main Form for that record. Invoices Main Form This is the central form of the application, where invoice data can be bulk-inserted from a spreadsheet with hundreds of records or entered manually. The top of the form displays the selected invoice and any correction dates. Supplier Section: Allows supplier selection (changes not permitted once saved or bulk imported) "Add/Edit" button opens the Suppliers Main Form for adding or editing supplier information Invoice monetary data is entered here, and the commission basis is computed (see Commission Split Section) Customer Section: Allows customer selection (one supplier per customer location) Includes the Purchase Order # field Order Writer Section: Selects the Sales Associate with the role of Order Writer and their order ID Payment Section: Displays payment information (read-only) "Add/Edit" button launches the Payment To Invoice window Invoice Commission Section: Enters the Invoice Commission rate along with the calculation method Computes the total invoice commission, which is then split among the Sales Associates and the corporate share Commission Split Section: Determines the commission split amounts for all Sales Associates and the corporate share "Source" dropdown is usually set to "Matrix," but also includes a "Manual" mode for manual rate entry Commission split rates are computed depending on the Source setting, combining rules and overrides to determine the correct values Payment To Invoice Form This form applies portions of payments to the current invoice. It allows all or part of a payment to be applied and can span different payments from the same supplier. A dropdown list of available payments is displayed. Payments can be applied until the balance reaches zero, at which point the invoice is marked as paid and can be transferred to Dynamics. Invoice Bulk Import Form This form bulk imports invoices from an Excel spreadsheet associated with a particular supplier. Since large suppliers often have differing field names from what NNP uses, a Mapping Table is needed. Top Section: "Select File" button opens the file selection dialog for a spreadsheet "Mapping Table" dropdown allows selection of the appropriate field mapping "Edit Field Mappings" button opens the Bulk Import Field Mapping form Action Section: "Preview Import" button runs a test of the import process to verify no issues with the spreadsheet data "Process Import" button begins the import process if everything looks good Indicators display progress for current records processed, remaining records, and row errors "Export As CSV" button allows exporting records to CSV format Results Section: Displays the Raw Data, Preview Data, and Processing Errors in a sortable data grid Invoice Bulk Import Field Mapping Form This form assigns in-house fields to the equivalent columns in a supplier's spreadsheet. Mappings can be added, updated, or deleted. If a mapping entry field is blank (on the right-hand side), the importer will use the default value (on the left-hand side). Payments Payments Search Form This form is used for searching payments based on extensive criteria and filtering options. The more options selected, the more refined the search output in the table below. The "Payment Search Results" tab displays a quick summary of common fields, while the "Verbose Search Results" tab shows all fields returned from the query, typically used for exporting data. Search results can be exported or printed, and clicking on any record opens the Payments Entry Form for that record. Payments Entry Form This form is for entering payments into the system. Payments are only received from suppliers, so a supplier name and location must be selected after entering payment data in order to save the record. Once the payment has been fully allocated for commissions, the record becomes read-only and can no longer be changed. The payments dropdown selections only contain the results of the Payments Search Form. Payment to Invoice Bulk Import Form This form bulk imports payments from an Excel spreadsheet associated with a particular supplier. Since large suppliers often have differing field names from what NNP uses, a Mapping Table is needed. Top Section: "Select File" button opens the file selection dialog for a spreadsheet "Mapping Table" dropdown allows selection of the appropriate field mapping "Edit Field Mappings" button opens the Bulk Import Field Mapping form Action Section: "Preview Import" button runs a test of the import process to verify no issues with the spreadsheet data "Process Import" button begins the import process if everything looks good Indicators display progress for current records processed, remaining records, and row errors "Export As CSV" button allows exporting records to CSV format Results Section: Displays the Raw Data, Preview Data, and Processing Errors in a sortable data grid Payment to Invoice Bulk Import Field Mapping Form This form assigns in-house fields to the equivalent columns in a supplier's spreadsheet. Mappings can be added, updated, or deleted. If a mapping entry field is blank (on the right-hand side), the importer will use the default value (on the left-hand side). Reports This area of the program provides embedded Crystal Reports accessible via the Reports Menu. Reports can be printed and exported as CSV from the top or printed as PDF from the CR window. All report forms have two tabs: Preview Report tab: Displays the finished report Preview Raw Data tab: Displays all raw data from the database used to create the report Below are some examples of the many reports available: Processing Dynamics Export Form This form is used for previewing and exporting invoice data to the Dynamics accounting system. Top Section: "Preview" button initiates the process of creating appropriately formatted data in the box at the bottom. If any errors occur, they are displayed in the same box below, allowing for invoice correction. "Save" & "Save As" buttons open a file save dialog to allow saving the CSV document to the default file path "Mark All Exported" button marks all invoices in the list as Exported in the database "UnMark Selected Export" button unmarks a selected invoice as Exported. This is useful if any problems arose with that particular invoice (e.g., an error occurred). Grid section: Displays a sortable grid of all completed invoices marked Ready For Export Bottom section: Displays a preview of the CSV text file created Other Sales Associate Commissions Matrix Form This form is the basis of the commission matrix, allowing the setting of default commission splits. The splits are based on whether the Sales Associate has the role of an Order Writer (OW) or Ship To (SH) and whether the customer is an independent (IND) or a chain store (CHA). These settings can be overridden in different sections of the software, depending on sales territories, customers, their locations, or manual calculations in the Invoice Form. Postal Codes Form This form allows the addition or editing of new ZIP codes and their respective city or town, as USPS codes can change often. Security The NNP Commission Processor utilizes Windows Active Directory for authentication and authorization across various sections. All transaction records are logged by user and timestamp.