Saturday, 11 June 2011

Creating a bulk update file

A number of people have seen that Wessex Professional can perform a bulk update of all the Wessex & Frinton moulding records and have asked me if that can be done for other suppliers too?

Well, to quote Evelyn Waugh "Upto a point, Lord Copper."
First of all think about why you want the whole of a supplier's range in your database. With Wessex mouldings you can order just one length on your weekly delivery, what is the minimum quantity you'll need to order from a different supplier? Can you use the "Guest" option for a moulding you don't stock? (This is where you type "GUEST" into the moulding box and the program will ask you the width and the wholesale cost/metre and work out the price.)
Also, if you've added, say, a thousand records you are going to need to keep them upto date.

If you do decide you need to use a bulk update file here's how to create one.
  1. An Excel spreadsheet (it must be .xls, so in Office 2007 onwards choose Save As & Excel 97 - 2003 workbook).
  2. The worksheet must be titled "gen" not "Sheet 1", so right-click the title (at the bottom of the sheet) and choose Rename.
  3. The worksheet must include the following column titles - "Reference" (this is the supplier's number. "Description" ("1/2" Cushion Black" for example). "Price/mtr" (wholesale price in pounds & pence per metre) and "Width" (in Mm.)
  4. The name of the file is saved as the supplier's name in your database & "mouldings" (so you could save the file as "Arqmouldings" or "Arqadiamouldings", depending how you've titled the supplier in your database).
Well, that's the file, what about the data? Most supplier's seem to have a spreadsheet of their mouldings - it's just a question of getting it in the right form. For instance their Width colunm may be in Cm rather than Mm, but it's easy enough to use Excel to make the changes.
Another problem I've come across is a row missing some data. This will cause the update to fail, (if it does simply delete the offending row and try again).

How does this all come together in Wessex Professional? - Choose "Setup" > "Options", then click on the "Database" tab.
Click on "Bulk Update from File" (top right).
In the next screen you will see the Wessex & Frinton logos, below them a box with "other" in it. Replace "other" with the title of your supplier you want to update (eg "Arq" or "Arqadia", the "mouldings" bit will be automatically added).
Select whether you want to add all records or just update the ones already in your database.
If you are not VAT registered tick the "Add VAT to price" option.
Now click "OK". You'll be asked to find the update file, select it and let the program do the rest.

Phew! It's not that bad really, but I would suggest that it's only worth doing the above for mouldings you can easily get hold of.

No comments:

Post a comment