Customers

Introduction

Customers Excelerator is a tool that enables S200 Customers to be added and edited with ease. It offers the following features:

Creation or editing of customer records, either individually or in bulk.
Downloading and editing of existing customer records.
Deletion of existing customer records that have not been used in any transaction(s).
Browsing of Sage data and selection of data to enter on the sheet.
Direct printing of the Sales Ledger Aged Debtors Report from Excel.
You can download, browse, maintain, and create new customers using the sample template in six simple steps.

For more information on how to use Customers Excelerator, please refer to our tutorial on using Customers Excelerator.

Sage Permissions

Info

To use Customers Excelerator, the user must have access to the following features in Sage:

Enter New Customers Account
Amend Customer Account Details
Customer Account Enquiry

Standard Templates

Codis provides the template S200CustomerTemplate.xlsx. You can, of course, amend this template or create your own. (See Designing Templates ).

The S200CustomerTemplate.xlsx includes sheets which allow:

Single - Entry of a single customer.
Multiple - Entry of multiple customers.

Options

Save validated items immediately?

This option determines how Excelerator saves customers from the Save option.

If it is not ticked, Excelerator will validate the entire sheet and save it if all customers are valid.

If it is ticked, Excelerator will validate one customer, save it if valid, and move on to the next customer.

Do not ignore blank ranges?

This option controls how Excelerator treats blank cells in ranges.

If this is not ticked, then blank cells will be ignored. This means:

Existing values will not be overwritten when updating.
Blank is not validated as a valid value. (There are a few values, like some address fields where blank is valid.)
A default value will be generated when saving a new record.

If this is ticked, then blank cells will not be ignored. This means:

Existing values will be overwritten when updating.
Blank is validated as a valid value.
A default value will not be generated when saving a new record.

Maintain contact order as on the sheet?

This option controls whether the contacts are saved sequentially or in the order they are entered.

Allow duplicate codes on the sheet?

Excelerator will validate if the same customer code is entered more than once on the sheet. If this option is ticked, duplicate codes will be allowed.

Transaction size

If this option is set to 0 (the default), Excelerator saves on an all-or-nothing basis. It attempts to save all the customers on the worksheet but saves none if an error is encountered.

This allows the worksheet to be corrected and a save attempted again without saved records being removed.

If set to a positive integer, the Transaction Size is the number of customers saved in one batch. All customers within this batch will be saved if all are valid or if none are. This should only be set to non-zero if you use the "Status from Save, Validation or Delete" range to track which customers have successfully been saved. If you don't include this range, you will be warned:

See Partial Validation and Saving of Data on a Sheet

Only process unsaved?

Used in conjunction with the "Status from..." range. If this option is ticked, invoices with "Saved" in the Status range will be ignored when validating. This can save time when validating larger datasets.

See Partial Validation and Saving of Data on a Sheet

Only process invalidated?

Used in conjunction with the "Status from..." range. If this option is ticked, invoices with "Valid" in the Status range will be ignored when validating. This can save time when validating larger datasets.

See Partial Validation and Saving of Data on a Sheet

Continue save after error?

If this option is ticked, Excelerator will continue trying to save invoices after errors are found on other customers.

See Partial Validation and Saving of Data on a Sheet

Update existing external IDs?

This option applies when Using Excelerator To Import Data and tracking external IDs. If not ticked, if an external ID is found on the import that already exists, there will be a validation failure. Otherwise, the customer data will be treated as an update to the existing customer.

Printing

This feature prints the "Sales Ledger Aged Debtors Report (Detailed)" report.

Entering And Amending Customers

You can create new customers using Excelerator, provided you have the permissions in Sage. Excelerator follows the "Automatic generate customer numbers" ledger settings.

If you do not have this option ticked, then you must always enter a customer's code. If this code exists, then Excelerator will update that customer.

If you have this option ticked, then new customers can be created by leaving the customer's code blank.

A name must always be entered for new customers but does not have to be used if amending existing customers.

Download

The S200 Customers Excelerator download feature lets you download existing Sage customers onto your spreadsheet.

This option functions in a similar way to the customers' browse. It will display a browse, and the selected order or orders will be downloaded onto the worksheet.

An option can be used to choose not to include complete orders. (see Options).

Unlike the browse, you have the following options:

Clear before download

Any customers already entered on the sheet will be cleared.

Append

The customers you are downloading will be added to the sheet after any customers that have already been entered.

Append from current cell

The customers you are downloading will be added to the sheet from the current cell.

Maintaining Contacts

S200 Customer contacts can be maintained using Excelerator.

Excelerator allows up to 10 contacts to be maintained. Each contact has its own set of ranges.

The first contact saved will be set to the default contact for the customer.

Maintaining Contacts using Excelerator

Roles

Up to 5 roles can be viewed in Excelerator. The roles maintained by Excelerator must follow the Sage 200 rules for roles. Each customer account must have a contact with the Account and SendStatementTo role assigned. These will be added by Excelerator for any new contact. Roles 3, 4 and 5 can be maintained from Excelerator. Valid roles must be entered in these ranges.

Roles will not be updated for existing contacts if no role ranges are on the spreadsheet.

Telephone, mobile numbers etc

Sage allows multiple telephone, mobile numbers, emails etc per contact. Excelerator only supports updating the default.

Download Only Ranges

Sage 200 Installs

Delete

Warning

The "Customers Delete" feature enables you to delete all the customers present on the worksheet.

Please note that only the customers that can be deleted according to Sage business rules will be removed.

Before proceeding with the deletion process, please confirm your action by entering "DELETE" in the input box.

Populate

This feature allows you to pull data from Sage for customer codes already entered on your worksheet.

Invalid codes will be skipped, and you will be informed at the end.

Validate

Validate will check that the data on the spreadsheet is compatible with Sage accounting rules. It validates all the data and displays any errors in a new panel. Sage is not updated.

Info

Save also validates the data, but Validate will validate faster than Save. Also, it will attempt to report all errors in the data in one run, whilst Save will stop on the first error it finds.

It can be more efficient to use Validate before Save because of these reasons.

Sage 200 Excelerator CB Payments Validation

Validate lists any issues, the error description, and the line where the error occurred. By clicking next to the error, Excelerator takes you to the row in question.

To use, select Validate from the Ribbon for the relevant module. A panel is displayed listing all rows where Excelerator encountered problems.

Click next to the row to be directed to the row where Excelerator encountered the issue (see above).

A box like this one appears when Validate encounters no errors, and the data is ready to Save to Sage.

Any validation errors will be displayed in the Validation panel, as shown below. Red light lines are errors, yellow are warnings.

Warning

Ticking "Fixed" does not correct an error but acts as a reminder that the error has been corrected.

Once the data conforms to the Sage accounting rules, you can Save to Sage.

Tracking External Customers IDs

If you are using Excelerator to import traders (customers or suppliers) from an external system, you may want to track the ID used in that system.