Defining Global Variables
Help for defining global variables in the customer invoicing template for Excel
Global variables to be applied to each invoice order are defined in the 'Control' sheet. Some of these variables feed directly through to each invoice, while others are set as defaults but can be changed for each individual invoice.
The 'Company Details' section should be completed with details of the company which is issuing the invoice. This information flows directly through to each invoice created.
The 'Invoice Details' section sets default information for each invoice for which certain items can subsequently be changed directly in the invoice. This increases the efficiency of invoice creation by allowing invoices to be pre-filled with commonly used information. The Invoice Detail parameters are:
- Currency. This appears in each invoice directly.
- Tax rate. This feeds through directly to each invoice and is applied to taxable invoice items. Individual invoice items can be taxable or non-taxable. The total tax payable is calculated automatically.
- Default Shipping Method. Selecting the input for this item initiates a data validation drop down menu for selection from the 'Shipping Methods' section. Each new invoice is preloaded with the default shipping method.
- Default Terms. Selecting the input for each default term initiates a data validation drop down menu for selection from the 'Predefined Terms of Sale' section. These terms are preloaded into each new invoice created but can be changed within the invoice.
- Default Note. Selecting the input for this item initiates a data validation drop down menu for selection from the 'Predefined Notes' section. Each new invoice is preloaded with the default invoice note.
The 'Predefined Terms of Sale' section allows the update or adding of terms which can be selected as default terms or selected individually for each invoice created. The list of predefined terms can be managed by clicking on the 'Manage' button in the top right corner of this section. Selecting an existing term displays its contents in the text area of the form provided with the option to either 'Update' or 'Delete' the term. Entering new text directly in the text area allows the adding of a new predefined term.
The 'Predefined Notes' section allows the update or adding of notes which can be selected as the default note or selected individually for each invoice created. The list of predefined notes can be managed by clicking on the 'Manage' button in the top right corner of this section. Selecting an existing note displays its contents in the text area of the form provided with the option to either 'Update' or 'Delete' the note. Entering new text directly in the text area allows the adding of a new predefined note.
The 'Shipping Methods' section allows the setting of shipping methods with corresponding shipping costs which can be selected as the default shipping method or selected individually for each invoice created. 7 shipping methods and costs can be defined here; however each invoice may have a custom shipping cost applied to override this list.
The 'Control Panel' represents a menu to manage and quickly access parts of the invoicing system. Menu items in the Control Panel are:
- Customer Invoice Management. This option selects the 'Invoice' sheet to create, load, save or delete an invoice.
- Import Customer or Product Lists. This option launches a form to enable the importing of customer or products lists from text file such as a CSV file. This enables integration with existing customer management or inventory management systems provided that these systems are able to output a text file. The form enables the selection of either customer or product lists and the ability to browse to the text file required to be imported. The field separating character of the source text file can be chosen as comma, tab or any other specified character. Upon reading the first line of the import file, the left box is populated with the first row of data or titles. Destination fields can then be mapped to the source data by adding them in the order required to match them with the system. Fields can be ignored in the source data by choosing the '[Ignore]' option. Destination fields can be used more than once to map multiple source fields to one destination field. This is particularly useful for source data with higher granularity such as importing from Outlook contacts where the First name and Last name are mapped to the Contact name. When a list is imported, a check is made to verify whether or not the product or customer already exists. If it does exist, the record is updated rather than added. A message box appears at the end of the process to indicate how many records were added and how many were updated.
- Add New Customer. This option allows the adding and saving of a new customer into the system. The new customer can then be selected when creating invoices. It should also be noted that customers can be entered manually into the invoice and then saved in the same manner from the 'Invoice' sheet. A check is made whether the same contact name and organization exists and provides an option to update the existing record if this is the case.
- Add New Product. This option allows the adding and saving of a new product into the system. The new product can then be selected when creating invoices. A check is made whether the same inventory code already exists and provides an option to update the existing record if this is the case. An link is also displayed in this form to define product attributes which can be added to invoice item descriptions.
- Delete Products or Customers. This option launches a form to enable the removal of saved products or customers. It is not recommended to delete products or customers for which invoices have already been created with their details, as subsequent loading of these invoices will omit the product or customer detail deleted. Nevertheless, options are provided here to remove deleted product information from saved invoices and completely remove saved invoices for deleted customers, in order to minimize or eliminate the occurrence of missing information in loaded invoices.
- Export Products/Customers/Orders. This option launches a form to allow for the exporting of customer, product, or order lists to either Excel or any character delimited text file formats. This enables information to be exported for further analysis or reintegrated into existing systems. The row count of Customer and Product text file formats matches the Excel 97-2003 counterpart as the maximum number of Products or Customers is limited to 65,000 rows available in an Excel worksheet. The Orders sheet uses multiple column blocks to increase this limit 1,300,000 records; therefore to export a continuous list of Orders, the delimited text file method should be used in cases of more than 65,000 orders.
- Run Sales Reports. This option selects the 'Reports' sheet to run analysis of orders by customer and by product.
Screenshots: Defining Global Variables (1) Defining Global Variables (2)
){kind=link}
){kind=link}