Access-style functions in Base scripts: DLookup, Nz and more

Scripts behind buttons are where a database starts to feel like an application. A button that checks a value, asks a question and then does the right thing saves people a string of manual steps. In Base by Zaweba those scripts are short pieces of JavaScript, and to make them easier for people who learned on Access, Base gives them a set of familiar functions with familiar names.

If you have written a little VBA in Access, you will recognise most of them straight away.

Looking things up and adding things up

The domain functions are the workhorses. DLookup fetches a single value from a table or saved query, such as a customer's credit limit. DCount counts matching records, DSum adds them up, DAvg finds the average, and DMin and DMax the smallest and largest. They work on tables and on saved queries, and they take Access-style criteria to say which records count.

That opens up many small jobs. Before adding an order, a script can use DSum to total a customer's unpaid invoices and warn if it is over their limit. A button can use DCount to tell you how many members are due to renew this month. DMax can find the highest invoice number so far.

Talking to the person at the keyboard

MsgBox shows a message, and with options such as vbYesNo it asks a yes or no question and tells the script which button was pressed. InputBox asks the person to type something, such as a reason for cancelling. Between them, a script can confirm before doing anything drastic and collect the small details a job needs.

Keep messages short and clear, and only ask when the answer changes what happens next. A script that pops up three boxes for a routine job soon gets clicked through without being read.

Dates, text and empty values

DateAdd adds days, months or years to a date. DateDiff counts the gap between two dates, which suits working out how many days late something is. DateSerial builds a date from a year, month and day. Format turns values into text for display, with options for currency and dates, so a total shows as pounds and a date shows the way UK readers expect.

For text there are Left, Mid and InStr and similar functions, for picking out parts of a string or checking whether one piece of text contains another.

Nz deserves a special mention. It swaps an empty value for one you choose, usually zero or an empty string. Use it whenever a field might be blank, so a sum doesn't fail because one invoice has no amount yet.

Writing scripts that stay readable

Base has a scripting guide in its Help menu, and it is the place to check exactly how each function is called. Keep each script to one job, give it a clear name, and add a short comment at the top saying what it does. A script you can read in a year's time is worth more than a clever one you can't.

Remember that scripts stay in the desktop app. The exported website uses standard pages instead, so anything vital to the data, such as a rule that a value must never be negative, belongs in a validation rule on the table, where every route into the database obeys it. Use scripts for the helpful extras: checks, warnings and shortcuts.