Tuesday, 10 March 2009

Oh, there's a Default Value option?

Yes, there's a default value option in the field properties bit of database table design.

So use it already!

There's all sorts of reasons and situation when you might use this feature. For starters, you might have a common value that many times will need to go into the box. Here's an example - let's say it's a stock control system being designed by someone who is completely hypothetically called "Steph"...

The field here is one where the user can say whether or not a bill has been paid.

The chances are that when the stock is booked in the bill won't have been paid - a bill is sent afterwards (the designer knows this because she spoke the the client - she knows the client's needs...). So a No is by far the most common value to go into the field when the record is created.

So, we use a default value of No!

You can also see some simple validation and whathaveyou. Of course, an alternative might have been to have used a Yes/No Data Type rather than a Text field...

Now, the same database also needs to enter the Date In of the stock being stored. Here again we can use a default value - the chances are that most of the time the value will be the current date.
Again, the designer knows this because she spoke to the client about it and found out. For sure. Can I stress that this step is really, really important in all of this? Thanks.
Here's the field properties:

We can use Date() to get the current date in that cell.
Note the validation here.

It's possible, although unlikely, that a different date might need to be entered. A container might be fortgotten about or not entered for some reason. Because of that the validation rule has been set to accept any date from 7 days before today until tomorrow - a container might sometimes be logged on the system the day before it's actually present on site.

Do I need to tell you how she knows this?
Here's the Planned Date Out field properties:

The minimum length of time a container can be in store for is 1 week apparantly. Seems a bit odd, but that's the way it works. So the Default Value can be set to a weeks time - Date()+7. The Valudation rule here might need to be >Date()+6 actually - I think you'd need to test all of this.

Now, testing...

All these rules and default values need testing. This goes in the Testing section of a report - in the Test Plan. In all this you clearly have to test the Boundary Values as well as values which are obviously going to the right and those that are obviously going to be wrong.

So, if I'm testing this all today (10/3/09) then I'd be checking if I could enter 2/1/06 (hopefully no - it's clearly wrong), 9/3/09 (OK), 2/3/09 (shouldn't accept it - and it's a boundary value) and 3/3/09 (should accept it - it's a boundary value again).

This demonstrates the importance of a clear test plan at this sort of level - it shows you the detail of testing that systems like this need.

No comments: