Working with large datasets in Tradebox Finance Manager

Introduction - Potential Problems

Big datasets have to work harder; that’s a fairly universal rule of working with data. If you’re pushing the boundaries beyond what your software/infrastructure was designed to handle, you increase the likelihood of running into problems such as:

• Slowdown
• Failed posting attempts
• Operations timing out
• Unexpected behaviour
• Data corruption

These hold true of your Sage data and also your Tradebox data.

Contents:

• What's a 'big' dataset?
• What are my options?
• Keeping Tradebox data in check
• Keeping Sage data in check
• Environment

What’s a ‘big’ dataset?

This is where things get a bit vague. On the one hand, solutions providers often give guidance on the advised limits of their systems and what’s ‘safe’ to work with. On the other hand, anything that’s big enough to cause problems in your environment is a problem. So there aren’t really any hard and fast limits and every user would have a slightly different experience with the same volumes due to variations in the settings used, power of the computers, speed of the network etc.

Where you’re working with Sage 50, their advised limits are on page 2 of this article:

The headlines that apply to your Tradebox integration are (on modern versions), Sage recommend keeping your data inside of:

• 1 million splits (a split is shorthand for ‘transaction’ – ledger invoice transaction, bank receipt, supplier payment etc.)
• 50,000 invoices
• 50,000 sales orders
• 20,000 product records
• 2,000 customer records

This is consistent for Sage v21/2015 and later. Unfortunately, handling lots of data isn't something that'll be taken care of by installing a Sage upgrade.

Sage 50 doesn't prevent you from going over these limits. Think of them as a reference point; it's not as though 1,999 customer records runs completely without flaws and then everything breaks when you create two more records. But if you consistently process beyond these limits, you have a greater likelihood of problems.

Working within Tradebox, most of our userbase do up to a few hundred transactions per week through all their channels. Heavy users do a few hundred per day. While we don’t have a hard limit, if you’re doing thousands of orders a day, you should probably be looking for a system that’s built with that volume in mind, which Tradebox is not.

 

What are my options?

If you're running at, or around the above limits then in short you have a bit of a choice to make.
You could either continue with a setup of Tradebox posting into Sage 50, with the knowledge that your system is more liable than the average user to be slow or run into data corruption and that regular data maintenance is a must. Often, bigger users keep Sage 50 for the bookkeeping and accounting side, and add other specialist software for tasks like stock control or CRM.
Or, you could migrate to a system built with larger datasets in mind. Moving to MMD software like Sage 200 often means a bigger price tag, and an investment of time and energy into getting a new system up to speed. We’re aware that running with Tradebox and Sage 50 does have a life cycle; hopefully your business will continue growing in size and scope and if that happens you will at some point need to upgrade your infrastructure to keep up.

We’re sometimes asked if we support larger accounts packages, or if we’re willing to build these integrations or build bespoke software. Unfortunately that’s just not something we have the bandwidth for. Moving to a larger accounts package than Sage 50 does mean you also need an alternative to Tradebox, as Sage 50 and Xero (which has far smaller safe limits than Sage 50) are the only accounts integrations we offer.

 

If you’re wanting to continue with a Tradebox and Sage 50 integration and large volumes, we can offer the following guidance on data maintenance. This guide is designed to inform and educate based on our knowledge and experience; following these steps should be safe but as with all things taking sufficient precautions such as backups and checking data is your responsibility as the user.

 

Keeping Tradebox data in check

Most users don’t really need an ongoing permanent set of historic data in Tradebox. Tradebox is the conduit for passing to Sage, where your records should be more permanent. As such its safe to periodically clear out older orders from Tradebox, which will keep your integration running more quickly and smoothly. This can be achieved by archiving the order history, or clearing it entirely. Note that this alone will have no effect on your Sage data, as removing orders from one program does not remove them from the other.

Before Tradebox imports an order the order ID is checked against the Tradebox database to determine whether or not that order has been imported previously.

The Maintenance > Clear Order History routine will remove older orders from Tradebox's database. This is the best method for freeing up resource within Tradebox and should be performed regularly in large datasets. We also recommend taking a backup of your Tradebox data prior to running this routine.

Because the Clear Order History routine removes the historic orders from Tradebox entirely, you also lose the ability to run reporting from them within Tradebox (not from within Sage). If you use any of Tradebox's built-in reporting, the alternative option is Maintenance > Archive Order History. Rather than removing orders, this writes orders older than your selected date to an archive table; removing them from the duplicate check and therefore speeding up the program. Note that this is a less thorough option than removing the old data entirely, so in some cases won't resolve problems.

With either routine, if orders from before the date chosen with either option are presented to Tradebox again it is likely these would be imported again and cause duplication in Sage; as such it is recommended that you do not choose a date from within the last 60 days. If you run CSV channels to import into Sage, check the date range your normal CSV file contains before setting your Clear date.

After clearing or archiving data, it’s important to run some routines to compress the database and reclaim disk space. Go to Support > Repair & Compact, then once this has completed, click on Cleanse Data Folder.

 

Tradebox One

Tradebox One is a full rewrite of Tradebox Finance Manager. While there are significant improvements in many areas, it's no better or worse at integrating high volumes into Sage, as the Sage data itself is the limiting factor.

 

Keeping Sage data in check

Preparation

As your bookkeeping tool, Sage should be a permanent record of the money through your business; what you’re doing now is relevant today but might suddenly be relevant again in five years if you need to check something for an audit. What users often don’t realise is that you’re able to keep a permanent record of data while also removing it from live tables; stopping things from bogging down with years upon years of heavy data.

Before deleting anything from your live data, you should:

• Set aside some time. These routines can be very slow; if you've got a lot of data, backing it up or clearing it down means digging through all of it in one go. It's worth it; you'll regain that time in improved performance for your everyday processing.
• Take a backup. In fact, take a couple. Once you've taken a backup, it's just a file like any other that can be copied and moved around. Store one somewhere off-site; be it a memory stick out of the office (fire or flood won’t discriminate between your hard drive and your removable storage) or in a cloud storage system like Google Drive or Dropbox. Backup your Sage via File > Backup.
• Archive your company data. While the backup is a permanent record, if you’ve ever restored one you’ll know it’s slow and inconvenient; other users need to remain logged out while you load up an entire company worth of records to check one old thing and then load back to today.
  Thankfully, Sage's archive feature gets round much of this problem; the tool takes a copy of your entire Sage dataset as it stands today, and saves a read-only copy in a separate folder. Why is this useful? Because once the archive's created it only takes a couple of seconds to swap between the live company and the archive. So you can delete historic data to keep the live company running smoothly, but still have easy access to that invoice from a couple of years ago when you have a query.
• To archive your Sage data, go to Tools > Period End > Archive Company
• Archives are non-destructive – a copy of the data is saved but nothing is removed
• On older versions of Sage, you could only archive at year end. In modern versions, you can archive any time so long as no other users are logged in
• To open an archive, go to File > Open > Company Archive. Choose the archive you want to open and log in (logins remain as they were at the time, relevant if you’ve changed your logons or passwords since).
• Once in the archive, everything is read-only; you can view, search, print, email, drill down and run reports. You just can’t change anything...which makes sense given that we're talking about data from months or years ago.
• To go back to normal processing once you’re done working in the archive, go to File > Open > Company Data and log in to your live data as normal.
• Include archives in your backups. The routines you're going to be running will remove data from your live Sage company, so you'll need to keep backup copies of the archives to protect your processing history.
  • In File > Backup > Advanced Options, you’ll see which files are included for backup. Data Files refers to your normal Sage company for live processing. Company Archives are the archives; while you don’t need to include them in every backup, you should make sure you’ve got copies periodically. Incidentally the same holds true for the other options in this screen like customised layouts and reports – an All Files backup once per quarter and after any major routines like year end is best practice. Remember, if it’s not in the backup, it won’t be there if you lose all your data and have to restore.
  • After taking an All Files backup, remember to go back into Advanced Options and change it back the next time you backup - taking Data Files only is best for day-to-day as it makes the backup and restore processes much faster.
  • The same holds true for moving Sage data. If you relocate Sage to a new PC or a different server, as well as taking the live data, you should also take the archives. Opening an archive is quick and easy, but if the data for the archive isn't present because you've only relocated the live data, you might be stuck. A bad habit when moving Sage data is to move only the ACCDATA folder which contains the live accounts data; where you have archives this isn't sufficient.

 

Removing data from Sage

Once you’ve given yourself a sufficient safety net, you can start removing old data:

1) Audit trail transactions. In Tools > Period End > you'll find the Clear Audit Trail option which removes transactions prior to a certain date. This doesn't mean you're wiping your old account balances; anything that needs to reflect an older balance will still do so by bouncing down to a new brought-forward figure.

Any date prior to your current financial year is fair game; most people keep current year to date and last full financial year. If you're a very heavy user (e.g. you're pushing through 300,000 audit trail transactions per year or more), consider clearing up until your last year end date instead...it's not ideal as you'll lose figures on some reports that have a prior year comparison but if we're talking about those sorts of volumes that's probably the lesser of two evils compared with having your system struggle with day-to-day processing.
Any transactions that Sage thinks you still have anything left to run processing on (unpaid, unallocated, not VAT reconciled, not bank reconciled) will not disappear. It's often good to run the routine and then check how much old stuff is left over - if old transactions aren't clearing then Sage's support team can help you out with some reports that highlight why and what to do. Don't assume the routine isn't working if it doesn't clear all the data, there will be a reason and often an easy fix.

2) Invoices. An invoice in Sage is purely a document. It’s functional in Sage only at one point in time – when you update ledgers (‘post’ the invoice). At this point, the invoice transaction (SI) is created on the customer account and nominal ledger, and if it's a product invoice the stock adjustment happens.
Once an invoice has been posted, the invoice record in the invoice list isn't needed. Deleting the invoice does not delete or otherwise impact on the transactions associated with it. This is why you might have noticed that Sage are perfectly happy for you to have a million transactions but only recommend 50,000 invoices; the invoice list should be kept for relatively recent records. Anything older is only useful if you need to print or email an old copy, or run any reports using the Reports button within the Invoices and Credits screen; all of which can be done from your company archive anyway, so keep the invoice list recent. Trust us, your Tradebox download will be faster and smoother with a small Sage invoice list.
To delete invoices, go to the Invoices and Credits screen (called the invoice list in older versions) > create a filter to show only before or between a certain date range > click Swap to select all> Delete. This process can struggle a bit, so it’s best to do it in batches; if deleting 3 months of invoices takes 30 seconds, great, do more in the next batch. If it takes an hour of staring at a progress bar to get there, try doing one month in the next batch. Be aware that unposted invoices will also be deleted if you've selected them, so make sure you're happy to lose anything you can see on the screen after applying the filter, but before hitting swap and delete.

3) Sales Orders. The same principle applies as with invoices; a Sales Order is only functional up until the point where it’s despatched and invoice(s) created. As with invoices, filter the list > swap > delete. 

4) Customers. Open the customer list > Swap to select all > Delete. It’s safe to ‘try’ to delete the entire customer list; only accounts that have no outstanding transaction and no historic activity before your clear audit trail date will be removed; if you’re following our other recommendations that means you haven’t dealt with them either since your last year end, or the year before. Bearing in mind that Sage's lowest recommended limit is for customers (2,000 records), if it’s been that long since a customer's bought from you they shouldn’t really be in your live data (if you need ongoing CRM for a lot of customers, Sage 50 probably isn't the best solution for that). As with everything else, their details and history are in your archive if you need them.
If you've got tens of thousands of customers, it'll take a pretty long time to do anything with the entire list. In this case, apply a filter to break the list into smaller batches before Swap and Delete.

5) Purchase orders and suppliers. Tradebox doesn't interact with these. But if you're running routines to clear things up anyway, follow same routine you did for invoices in the purchase order list, and follow the same routine you did for customers in the supplier list.

6) Products. Old product records can be deleted from Sage if they have no stock on hand, no current allocations, aren't part of a bill of materials and have no historic activity. The first two points shouldn't really apply to old products anyway but run a stocktake to adjust out old stock if needed. If you use BOM's in Sage, you can run the product explosion reports to check what's part of a BOM and amend their records accordingly.

To clear historic stock activity, go to Tools > Period End > Clear Stock. This is similar to the clear audit trail routine, but for stock activity rather than financials. Once you've cleared the activity of your old products, you can delete them. In products highlight a range of product records/highlight all/use a filter and then click Swap to highlight > click Delete. As with customers, it's safe to 'try' to delete all product records; any that have current activity won't be deleted. Newer versions of Sage offer a list of suggested product deletions after you run Clear Stock, which will give the same effect.

 

After removing data

After removing any significant volume of data in Sage, go to File > Maintenance > Recovery Tools (this will force a backup, data files only is fine) > Reindex and Compress All.
Once that’s done, go to File > Maintenance > Check Data. Removing accounts can cause warnings or comments, check if they need to be actioned at https://my.sage.co.uk/public/mydata.aspx

 

Preventing future buildup

While there's not much you can do to process fewer transactions or invoices, running these routines periodically is often better than waiting until things are out of control.

One option that can help is to have Tradebox posting all sales into generic customer accounts rather than creating individual customer records for each sale. This Tradebox One support article covers the advantages and disadvantages of the customer choice, which also apply in Tradebox Finance Manager. In Finance Manager, this setting is within each sales channel, in the Posting Accounts screen; if Auto Generate Records is enabled, an individual customer account will be created in Sage for every new online buyer. Keep Auto Generate Records unticked to process all sales through the default customer account, which should help your Sage's performance significantly.

 

Environment

Data is only some of the battle. Your environment will also have a big impact on how well your systems handle volume; this means both the power of the hardware you’re using, and other factors like your network speed.

Network

Running any program across a network will be slower than running with the database and relevant files stored locally on your PC, as requests and transfer of data will be impacted by your network speed.

You can check the setup of your Tradebox by going to Support and looking to the Folders. The Program Folder should always be installed locally within a directory on your computer's C:\ drive. If it's installed on another drive and you're having issues, try uninstalling and reinstalling to the boot drive.
The Data and Logs folders always sit alongside each other. The location of these folders will let you know whether Tradebox is running purely from your PC (data folder on local drive e.g. C:\) or across a network (data folder has either a mapped network drive e.g. S:\tradebox, or a path starting with a double backslash e.g. \\server\tradebox or \\192.168\tradebox). 

If your Tradebox is currently running across a network and you are having problems, it may be worth relocating the database to your computer to see if it improves performance. If you don't need to access Tradebox from any other PC's on your network, there's no benefit to running across your network (though we would recommend storing your Tradebox backup files on a network location or off-site). It's also worth checking the location of the In/Out/Shipped folders in the Sales Channel Details, keep them on the same machine that your Tradebox data is stored on.

Multi-user Sage always involves at least one of the users accessing the data across a network. Again, this will always be slower than using local data. Often networking is necessary, but if you are a heavy user on the data side it’s worth exploring your options, including installing and running Tradebox from the same machine that holds the Sage data, and making sure this is the most powerful machine available to you.

If you do need to remain in a network environment for either program, we would always recommend a wired connection as this will always be faster and more stable than running via wi-fi.

PC Spec

Sage's system requirements are available here. Any machine capable of running Sage should also be capable of running Tradebox. Bear in mind that those specifications are purely a minimum and if you're a heavy user you should look to be well beyond that.

A faster processor has more raw computational power at its disposal. More RAM (memory) allows your PC to multitask more efficiently. For storage, SSD's (solid state drives) can read and write data much faster than traditional mechanical hard drives (HDD; where your files are stored and also where Windows and your programs are installed).

If you suspect your PC or server would benefit from an upgrade, you should talk it through with an IT professional. In our own internal benchmarks, extracting 1,000 product records from local Sage data had as varied results as taking 20 minutes on a couple-of-years-old relatively underpowered machine (that still met minimum requirements and we were still using for everyday processing), to 2 minutes on a brand new high spec machine running on an SSD.