Saturday, 15 February 2014

Updating Mouldings - Do it right!

I'll let you all into a secret - I get more questions and problems concerning updating Mouldings than anything else. So, this post is about doing things the right way.

First a few definitions -
  • Database - this is where the data is stored. It consists of (in our case) 4 interlinked "tables" These "tables" all contain "records". The "Customers", "MountBoard" and "Mouldings" tables all feed into the WorkTickets table. For instance, if a customer changes their address then you only need change it in the "Customers" table and all the associated WorkTicket records use that new address. Clever eh?                                                                                                               NB. The Basic version of Wessex Professional has the same database file, it just only uses the "Mouldings" table.
  • Database file - because our database is relatively small everything is stored in a single file ("v3.mdb"). The file is a Microsoft Access database file and can also be opened by Word or Excel.
  • Update file, contains data for the records in the Mouldings table - this is an Excel file (.xls) and is used to update a supplier in the Mouldings table.
The idea is to update the Mouldings table with the update file, bearing in mind that the mouldings table may have other data in that you want to preserve. So, it's not as simple as wiping the old data and replacing it with new data.

The program has always had in "File" -  "Import Database" and "Export Database" this is to allow the complete database to be copied & backed up, and to allow a backed up database to be re-installed. Unless you are doing either of those actions do not choose those options! If you do and don't heed the warning messages - you're on your own!

The up to date versions of the program also have under "File" - "Update Mouldings...". If you want to update your Wessex or Frinton records this is the option to go for.
(note - in older versions the same screen can be reached by going to "Setup" > "Options" choosing the database tab and clicking on "Bulk update from file".)

Now we just need to make a few decisions
  1. Which supplier we want to update, ie. Wessex or Frinton (or A N Other). Click the appropriate logo.
  2. Whether we just want to update the existing moulding records in the table or add all of the suppliers records to our own. (in my database I have all the Wessex records, because I can order any unstocked mouldings with my weekly delivery, but I only keep records of the Frinton mouldings I have in stock).
  3. Whether to add VAT to the wholesale cost of each moulding record (for those who are not VAT registered).
Click "OK" and you'll be asked to locate the update file (you did remember where you downloaded it to, didn't you?).
Once you've selected the correct file the bar at the bottom of the form will show you the progress. This is relatively sedate because the program first of all selects the existing mouldings and updates them individually. Then (if you've selected that option) it adds any new records that are in the fie but are not in your records. Finally, it looks to see if your records contain any mouldings that are not in the update file (if there are then they are marked, in the Notes section, "discontinued" with the date).

Note - any new records added are not marked as "Current". If you do stock them then you'll need to edit the individual record.
For some extra reading - have a look at this link , enjoy!

No comments:

Post a comment