Skip to main content
BlogSage 100Tips and Tricks

Sage 100 Data Quality Tools

By August 13, 2020No Comments


Katie Ford
Sage 100 Consultant
SWK Technologies, Inc.

Ensuring Data Quality with the Built-in Tools of Sage 100

Your Sage 100 (and Sage 100cloud) software has multiple native data quality management tools to help you manage all those disparate  siloes and reconcile inaccuracies. In this blog, I’ll walk you through a few of these to help better manage your Customer and Vendor fields in your ERP database.

Data is the connecting factor within your business; customers are contacted based upon it, orders are invoiced, payments made, bonuses decided, and strategic goals measured. Have you ever thought about your relationship with all those factors? How much time do you spend ensuring that your data is providing accurate results?

To truly be managed, data quality must be a part of your organization’s culture. Within the organization, Sales and Marketing teams focus on data quality more often that on the operational side of business, but poor data quality can lead to over purchases of inventory, returned or stalled delivery of products, lost efficiencies and higher costs.

Years ago, in a Dale Carnegie training, I learned that “A person’s name is to that person, the sweetest, most important sound in any language.” Being responsible for my organization’s data quality at the time, this rang true to me, and it frames the way I approach data quality.

Sage Data Quality Case Study

Let’s walk through a quick use case scenario to get a better understanding of where data quality comes into play. Imagine you are a decision maker for a Sawyer Industries, and you’ve just become a customer of another large organization after months of negotiation with their Sales team. However, you have some concerns about their product’s quality record.

You receive your first shipment after a few delays in shipping and the packing slip & invoice read like this:

slawyEr INdustrieas
1234 Main Street
New customer give best packgein
Topkea, KS

Your concerns about the product inside are increased because the simple task of spelling your company name correctly, using address fields from non-address information and lack of a zip code tell you that the company does not pay attention to detail.

Obviously, this could hurt your business relationship, so the customer service representative quickly looks in Sage 100 and sees in Customer Maintenance that the order was placed under the wrong company – there was one, spelled correctly, with no orders. A classic duplicate situation.


Ex #1: Not the right company



Ex #2: Right company, no orders


Sage 100 offers a few tools to help you manage Customers and Vendors.

  • Accounts Receivable -> Utilities -> Remove Inactive Customers
  • Accounts Payable -> Utilities -> Remove Inactive Vendors

Each Customer Record on the 2. Additional tab has a status field. It is best practice to keep this field updated as it allows for lookup filtering and better data quality overall.

On the schedule you decide – many do quarterly or yearly, and you can use the Remove Inactive Customers tool to permanently delete these customers from your Sage 100 Company. Inactive customer records can be deleted only if there is no outstanding activity for that customer. If there is activity, such as pending orders, you must wait until all open invoices for the customer are paid and purged before you proceed with the record deletion.

Within Accounts Payable, the tool works the same way On the 2. Additional tab in Vendor Maintenance, you can select Inactive for the Vendor Status. With Vendors, Inactive records can be deleted only if there is no outstanding activity for that vendor. If there is activity, such as pending orders, you must wait until all open invoices for the vendor are paid and purged before you proceed with the record deletion.

Delete or Change Customers/Vendors tools

  • Accounts Receivable -> Utilities -> Delete and Change Customers
  • Accounts Payable -> Utilities -> Delete and Change Vendors

Once you have removed all the inactive customers and vendors with no open activities or history, it’s time to tackle the duplicate customers or vendors. Merging them is a multistep process.

First, you must decide which customer record is staying (I’ll refer to this as the Master Record) and which record is to be absorbed by it (Duplicate Record). One approach is to add a User Defined Field on the Customer (Vendor) Maintenance record to house the New Vendor number on the Duplicate record. As duplicates are identified this field can be filled out.

Quarterly you can use the Visual Integrator tool to export a spreadsheet with the Duplicate and the Master customer numbers and then import into the Delete and Change Vendors tool with a separate Visual Integrator job.



Please note, you must make a copy of your company in Sage or backup your Sage system before you get started, and reach out to your Sage 100 Consultant if you have any questions!

This process can take a long time to complete, it might be best to start on a Friday night and monitor until completed or run it in smaller batches nightly. Everyone will need to be out of the system while it processes.

Importance of Data Quality Controls

Data quality is important for your customer experience, for your processing time within the system, and better ROI for integrations and automation projects. By creating Data Quality as an expectation within your business, you will reduce headaches and error. Your Sage 100 solution will help you build the foundation for this quality standard by providing the tools to identify mistakes and reconcile inaccuracies promptly.

Contact SWK Technologies for More Sage 100 Tips and Tricks

SWK is a top Sage partner and can provide you with a library full of tips, tricks and best practice along with persistent support for your implementation. Reach out to us today and get in contact with one of our Sage 100 consultants to learn how to get the best value from your software investment.

Contact SWK Technologies now for answers to all of your questions and support for your Sage 100 instance.

Talk to the Sage 100 Support Experts

Close Menu
SWK Technologies