Problems updating other suppliers.

A couple of framers have contacted me recently with problems trying to update mouldings from a supplier's list.
At first glance everything looked good.
The file was ".xls".
The headings were all correct.
There were no blanks in the list.
The worksheet was titled "gen"

Looking more closely I noticed that one of the files contained apostrophes in the description (why?).

While the other had the width as a decimal. (The last column in the screenshot).

Both of these things will cause the update to fail.

The solution?
For now you will need to edit the data from within Excel.
In the first instance (the apostrophes), in Excel, select the column, click on "Find & Select", then "Replace" and follow the prompts.

For the second (the Width as a decimal number), select the column, click on "Number". It will be shown as "General", change this to "Number". Then click twice on "Decrease Decimal" (see below)

The numbers will change to integers (ie. no decimal point).
Save the file, and all should be well.

The next version of the the program (look out for version 4.4.7 in the next few weeks) will handle all this automatically.

It's not working - heeeelp!

Of course I only get to hear about problems with the pricing program (WPP4), and I use the points raised to help improve it.
Very occasionally someone finds a program error and that is easy(ish), because then it's just a question of putting things right.
Most times the problem is something like - "It was working fine 'til yesterday, but now it's giving me an error (xyz) and I can't do (abc)."
The majority of these problems are usually related to the database, most often an unexpected value has been saved and the program doesn't know what to do with it. For instance - it can't read a number that it is expecting (Someone managed to insert 2 decimal points in the same number once!) The same goes for the Labels/Values file (putting an unnecessary current symbol is a good one).
When I can track these down, I write code that checks the value, so that an incorrect one cannot be saved. In fact about 30% of the program code is made up of these error-checking routines. Below is one that checks if the Moulding ID entered is a valid one.

The generic error message of the program has 3 suggestions. (Try to continue, restart the Program, contact Wessex Pictures).
If you are getting one of these errors then -

• Don't panic
• Make sure you have backed up the Database and Labels/Values file AND you have put them where they cannot get confused with similar files. (The database file is always called "V3.mdb" whether it contains your data or not)
• The error is likely to be on the record you were working on when the message appeared, so can you get to that and correct it, or at least see the problem?
• Do you have an earlier backup of the database you could import to check if the error is still there?
• If the above doesn't work can you open the Database in Microsoft Access to see the problem and correct it? (Make sure the result is saved as "V3.mdb" not "V3.accdb")

Another solution is to load the program onto a different computer, check that everything works, then import your database. If there are no errors then the problem must lie with the original computer's configuration (ie. not a Wessex Pricing Program fault).

One of the challenges of programming is that the program you write has to work on a system that can be configured in many different ways. Very occasionally I've come across a problem I just couldn't get to the bottom of. A few years ago someone found that the program worked fine, except that it wouldn't print anything, I still don't know why. In the end it was put onto different computer and the problem went away, luckily it was the only time the problem has ever arisen.

What, of course, I haven't said is that computer errors can be immensely frustrating, testing your patience to the limit. So, put that hammer away, have a cup of tea and then tackle the problem!

An interesting problem

The pricing program for PC (WPP4) has been behaving itself nicely. I've been working on a couple of minor improvements, including being able to create a file on mouldings that can be imported into the new Android App.
That was until a framer rang me up with a problem concerning the Invoice number.

The problem -
The invoice number can be increased in "Setup" > "Options". The idea was that you may not want to start with invoice number "1". However, this framer had a (quite reasonable) scenario of changing the invoice number to reflect her financial year, for example "1314001". She saved the new invoice number value and then tried to save an invoice - this produced an error and the invoice couldn't be saved. The problem was made worse because invoice number couldn't now be decreased in the "Options" form.

The reason for the error goes back to the original design of the database some 6 or more years ago. Each column in the database has a data type, for instance the"DateCollected" column in the table "WorkTickets" is datatype "date". Well the datatype of the "InvNum" column in "WorkTickets" is "integer". All well and good you think (well, I did anyway). But type "integer" in this form of database turns out to be "int16", a number made up of 2 bytes which has a maximum value of 32,787. A much better choice would have been "long integer" ("int32"). This number is made up of 4 bytes and has a maximum value of 2,147,483,647.

The Solution -
Once the data type has been set up in the design of the database it is very difficult to change. Certainly trying to change it programatically would be likely to throw up more problems than it solved. So, if you need to use numbers larger than 32,000 for your invoices here is what to do, "Export" your database and open this copy in Microsoft Access.
You will see a list of Tables at the left hand side. Choose "WorkTickets", the "WorkTicket" table and its data is now shown in the main pane. We want to get to the data types, so go to "Views" at the top left and choose "Design View". Now the Field (Column) names and their datatypes are displayed. Click on "InvNum" (second one down) and at the bottom of the screen its Field Properties are shown. The first one is "Field Size" as "Integer". Click on that line and from the drop-down box select "Long Integer" instead. Save the changes and close Access.
Now all that remains is to "Import" the modified database file back into the program. This will throw up a warning saying the database is corrupted, continue to import? (This is because we've changed the structure of the file.) Click "Yes" and the new database will be imported anyway.
You will now be able to use large numbers for your invoices.
Phew!