The following are several progressive examples that introduce you to the basics of writing DbfScript.

DbfScript is a series of statements, each on a separate line. Execution of these lines happens sequentially, one line after another. Sometimes certain lines are skipped, or a block of lines repeated, and this is accomplished using something called 'program control'. 'If' and 'loop' statements are examples of program control, and we'll get into those a bit later.

A useful statement in DbfScript is the "log" command. This outputs information to the system log (see this section).

log "This is logged"
 

In this case, the command is 'log' and the parameter is "This is logged". Parameters are data that is used by the command, often to tell it what exactly to do.

Parameters can be text strings, numbers, booleans, dates, files, records or lists. When you specify a string, you surround it in double quotes. When you specify a number, you just put the number, without any quotes.

log 20
 

In this example we're specifying a number.

When you specify a particular string or number, this is called a 'literal'. You can also specify a formula, variable or function. Formulae are just like mathematical formulae:

log 20 + 10
 

You can use any of the regular operators, and parentheses to control precedence. When dividing, use 'div', and not a slash. This is because the slash character is used to separate paths, which we'll get into later.

log 100 div 5
 

Now let's look at functions. A function performs a special calculation and returns a value, that can be used instead of a literal. An example is Now(), which returns the current date and time.

log Now()
 

You'll notice that there is an open and closed parenthesis at the end of the function name. All functions may or may not take parameters that are specific to that function, and these are placed, comma separated, in parentheses after the function. The parameters can also be literals, formula, functions and so on - just like any other parameter.

log Random(0, 100, 0)
 

This example calls a function called Random, which returns a random number between the first and second parameter, with the third parameter specifying the number of decimal places. Every time you run this, it will return a different, random number.

As mentioned, these parameters could also be formula:

log Random(0, 10 * 20, 0)
 

Or another function call:

log Year(Now())
 

This uses the function Year, which takes a date as the parameter. For that parameter, we're calling the function Now(), which takes no parameters. You can also combine these, and make complex formula:

log Year(Now()) * 2 + Random(0, 10 * 30, 0)
 

To keep things readable, it's often best to split these up into multiple lines. So now we'll look at variables.

Variables are names given to virtual boxes that can contain literal values. You can create any number of variables you like. All variable names start with a pound/hash symbol (#). To create a new variable, use a statement like this:

var #myVar = 3
 

The 'var' statement is followed by a variable name (in this case myVar), an equals, and then a parameter. This is called declaring a variable, or the variable declaration.

Once you've declared a variable, you can then use it as a parameter anywhere. It will automatically be replaced with the value it holds.

var ##max = 3
log Random(0, ##max, 0)

In this case we're creating a variable called 'max', assigning 3 to it. Then we're calling the function Random, and using our #max variable as the second parameter, to denote the maximum range for the random number.

After you've declared a variable, you can also set it to different values. To do this, use the 'set' command:

var #max = 3
 set #max = 10
 log Random(0, #max, 0)
 

In this example we're declaring max with a value of 3, then we're setting it to 10, replacing the current value of 3, and then we're using it in our Random function. The last value assigned to it will be used, so in this case it'll use 3.

You can also use 'var' to set a variable after it's been declared. It's best to use set though, because if you mistype the name of the variable then var will inadvertently create a new variable, creating two of them, which will lead to program bugs.

Also you can assign a formula to a variable. It doesn't store the formula, but the result of the formula. The formula can include references to other variables, even the same variable:

var #max = 5
 set #max = #max + 10
 log #max
 

In this example we assign 5 to the variable, max. Then we assign '#max + 10' - which means the previous value of #max plus 10, in this case 15. So max ends up being equal to 15.

For more information on variables, read through this section.

There are two types of variables, one for holding values, and the other for holding locations in the database. The location is like the address of the record in the database. In WorkflowFirst, all locations are a path to a certain location. The path works the same way as the breadcrumb at the top of a WorkflowFirst screen. As you navigate deeper in the database hierarchy, the breadcrumb at the top of the screen gets longer, and the path is the same as this.

All path variables start with an ampersand.

var &location = &root/Users

This helps to visually distinguish paths from value variables in your script. One important variable that is always available, without declaring it yourself, is &path. This contains the location that the user was viewing when the action or event script was run.

Another important variable that is provided to you automatically is &root. This is what you use to access data on the top-level. So if you had a tab with all your contacts, you could access this regardless of where the user is currently looking by using &root/Contacts.

As you saw, you can extend path variables by putting a slash (/) and then child fields under that location. For example &root/Users refers to the Users tab, and &root/Users/AuditTrail may refer to an Audit Trail list underneath each user.

For a more in-depth discussion on paths, please refer to this section.

There are a few things you can do with path variables. The most common one is to load a record from the database. You do this using the load command. The 'load' command is not a regular function, and has a special syntax:

var #rec = load(&path)
 

It's worth mentioning casing of names in dbfScript at this point. Everything except functions use camel casing - that means the first letter is lower case, but subsequent words start with an upper-case initial. Function names use pascal casing - which means all words start with an upper case initial.

Certain 'system' operations that involve creating variables, or basic loading, updating, deleting or creating or records, will always be used with camel cased statements. Other, non-essential operations, are usually found in functions.

Getting back to the load statement, to retrieve a record in the database at the location held in the path variable, you simply pass the path variable as a parameter to the load statement. That returns a record value, which can be assigned to a variable.

As well as path variables, you can extend them as we showed in our example. This lets you get to child records, and also add filters to just get out specific records. These filters are known as predicates. Either way, the load statement returns one or more records.

Record values are like collections of fields and values. Instead of containing just one value, records can contain lots of values, each with a specific field name. This corresponds to the fields and values you defined in your data model in the application.

To access a field in a record, we just put a slash after the variable name, and then the field name:

var #rec = load(&path)
 log #rec/Name
 

This example loads the record at the current path, and then outputs the value of the field Name in that record. If you just output #rec only, without a field specified, it will output the whole record to the log.

As we mentioned earlier, to load specific records you can apply a filter - this filter is called a predicate. Here's an example of a predicate:

var #powerUsers = load(&root/Users[PowerUser=true])

Notice that the predicates are put in square brackets like this [...]. You can have one or more filters, and each of these are separated by a comma. Let's look at another example:

var #recentUsers = load(&root/Users[_CreatedDate > AddDays(Today(), -7)])

This is a bit more complicated - it loads all of the user records that have a created date (this field is always called _CreatedDate) greater than a week ago. We get the "week ago" by a function: adding -7 days from the current day (in other words, deducting 7 days from today). You could also get only the power users created in the last week by combining two predicates:

var #recentUsers = load(&root/Users[_CreatedDate > AddDays(Today(), -7)], PowerUser=true)

Once a record is loaded from the database, it is separated from it and stored in memory. You can make changes to the record and they won't appear in the application. But you can save the record at any time, and this will look at what's changed in the record, and put it back in the location it was found in the database so others can see it.

To change a field, you also use 'set', but with the field syntax we used above:

var #rec = load(&path)
 set #rec/Name = "John"
 save #rec
 

In this example we're loading the record, and then updating the value of the 'Name' field to be 'John'. Then in the last statement we save the record, and this writes those changes back into the database.

Saving updates also raises any events that you created on that type, but we'll get into that later.

An important variable that is automatically provided to you in your script is #input. This contains the record of the form that invoked the action, or it may contain the values that were changed triggering the event containing your script.

In the above examples, we're assuming &path points to a record. But in reality it could also point to a list of records, depending on whether the user is on a list of on a record at the time. When a path variable points to a list of records, the load statement returns a list instead of a record. You can do several things with lists, but the most common of them is to loop through them, or use them in certain functions.

One of these functions is 'Count',

var #list = load(&path)
 log Count(#list)
 

In this example, we're outputting the number of records in the list we loaded.

When you're loading lists, you should be careful about how many items you're loading. If your list contains thousands of entries, loading them all into memory wouldn't be a good idea as the system has a limited amount of memory available. If multiple users are running that same script at the same time, the system could easily run out of available memory and the operation will fail with an error.

So now we'll move on to program control. Program control statements are special statements that skip or repeat lines of script for various purposes. First we'll look at the 'loop' command.

The loop command can be used to repeat a section of the script for each record in a list. Let's say you had a list of contacts:

var #contacts = load(&path)
 loop through #contacts as #person
    log #person/Name
 end loop
 

In this example, we load the list of contacts at the current path (&path). Then we loop through each of those contacts. The lines between the "loop" line and the "end loop" line are then repeated, and each time they're repeated it automatically sets a variable called #person for each record.

Typically, when we're defining a special section of script like this, we'll indent it by starting the line with spaces or a tab. This doesn't affect how the script executes, it's purely to make it easier to read.

Getting back to our example, this particular script will log out the name of each person in the contact list. You can have as many lines as you like in the loop block of code.

The loop statement always take the same form of "loop through", a list, "as" and a new variable name for each record.

We could shorten this script, and use the 'load' statement inside the loop statement:

loop through load(&path) as #person
   log #person/Name
 end loop
 

The effect here is exactly the same, but sometimes it's easier to read the code if we assign important values to variables, because the variable name tells you exactly what you expect it to contain.

You can also include loops inside loops. In that case it would repeat the inner loop first, and then the outer loop would progress to the next iteration, then the inner loop would start again with the new record and so on.

Always think carefully whether you should be using a loop, because loops are time consuming and will slow down your application. If you're filtering through results in a list, rather than loading the entire list into memory and then checking each record, it would be best to pass a special query to the server. To do this we use predicates, and add these onto the path.

Predicates are discussed in more depth in this section, but simply put they allow you to add filters to paths. Let's say you had a list of contacts but some were relatives and some were friends. Previously you would load all contacts by doing this:

var #contacts = load(&path)
 

But you could add a filter onto this:

var #contacts = load(&path[Type="Relative"])
 

Predicates are placed in the path, next to the name of the field. They're always in square brackets ([]), and consist of a field, comparison operator, and value. You can have multiple predicates, each separated by a comma. They're all ANDed together, unless the field name is the same, in which case they're ORd.

The benefit of loading with these filters specified is that it allows the database to utilize its indexes to find the data, which it can do extremely fast. While the database is handling this request, your application can be servicing requests from other users. It's also a lot less script.

Paths can also be extended, to access other, relative, parts of the database. For example, if each Contact had a list of PhoneNumbers, we might load records like this:

var #numbers = load(&path/PhoneNumbers)
 

You'll notice that we just add to our &path variable with a slash followed by the nested record field name.

There are some other options you can specify in the load, and these are discussed in this section. Most of these options are specified after a 'with' specifier. One example is the 'top' specifier, which reduces the number of records that can be returned.

var #contacts = load(&path[Type="Relative"] with top 5)
 loop through #contacts as #person
    log #person/Name
 end loop
 

This outputs the names of the first 5 relatives in your contact list.

Two useful functions that can be used with paths are the Parent and ExtractPath function. Parent returns the parent of the current path. So if we're at an invoice item, the parent path might point to the invoice record.

ExtractPath extracts a part of the path, and is useful if you're going several steps back in the path. Let's say you were at this PhoneNumber record under the contact, and &path pointed to that. But you wanted the actual contact record, above that number. To do that you could use this:

var #contact = ExtractPath(&path, &root/Contacts)
 

ExtractPath takes two parameters: the first is the path you have, and the second is the path you want it to look like. The &root/Contacts is a path to a contact. Because we know that &path points to something like /Contact[Name="Jim Jones"]/PhoneNumbers, we can get just the first part of the path by using the above example. This would return a path that points to something like /Contact[Name="Jim Jones"]. You could then load that contact, modify it and so on.

Next we'll take a look at the 'if' statement. The 'if' statement will only run the script between the if statement and its corresponding 'end if' statement if the specified condition is true.

var #age = 19
 if #age > 18 then
   log "You are old enough"
 end if
 

In this example we create a variable called age, with a value of 19. In the next line we have an 'if' statement. This checks to see if #age is greater than 18. If it is then the line between the 'if' and 'end if' is executed, otherwise it's skipped and execution resumes after the 'end if'.

You can also have a block of code that gets executed if the condition is false, by using 'else':

var #age = 19
 if #age > 18 then
   log "You are old enough"
 else
   log "Sorry, you're too young!"
 end if
 

You also can combine multiple program control statements, like this:

var #contacts = load(&path[Type="Relative"] with top 5)
 loop through #contacts as #person
    if #person/Age > 18 then
       log "You are old enough"
    else
       log "Sorry, you're too young!"
    end if
 end loop
 

Similar to 'if' is a control statement called 'while'. This repeats the statements between the 'while' and 'end while' until the condition is false.

var #count = 0
 while #count < 10
    log #count
    set #count = #count + 1
 end while
 

Be careful when writing while statements though, because it's very easy to get caught in an endless loop where the while condition will never be false.

At any point in your script you can abort using two different techniques. The first is 'exit script' statement. This just exits the script prematurely.

The second way to abort is by raising an error. This is an error you want to tell the user about. To do this you use the 'error' statement:

if #age < 18 then
   error "You are too young to perform this operation!"
 end if
 

In this example we check the age variable, and if it's less than 18 we abort the operation with an error displayed to the user.

A slightly different type of error is a warning. This also aborts, but only if the user hasn't accepted the warning. Generally it aborts, the user then checks the checkbox to accept the warning, runs the operation again and it skips the warning statement and completes.

if #age < 18 then
   warning "This may be unsuitable for those under 18"
 end if
 

Because of WorkflowFirst's transaction support, if you raise an error or a warning, all of the changes you made in your script will automatically get rolled-back to how they were when you started.

Another way to present information to the user is by using the "message" statement. This shows general information to the user in a yellow alert box at the end of the operation.

message "The operation completed successfully!"
 

We have already discussed loading and updating records. You can just as easily delete and insert records. To delete a record, you pass in a path to the record, or list, you want to delete:

delete at &path
 

Keep in mind this deletes the record without any confirmation by the user. It also deletes everything underneath the record, so you have to be incredibly careful how you use it!

Inserting a record is simple but usually takes a few lines of script, so you can set up the new record. The first step is to create the record. To do this use the 'new record for' statement:

var #person = new record for &path
 

This creates a new record that's ready to be inserted at the given location in the database. Next we set the values of that record:

set #person/Name = "Jim Jones"
 set #person/Age = 20
 set #person/Type = "Friend"
 

Lastly we insert it into the database:

insert #person at &path
 

This is all assuming that &path points to a list of contacts.

You can take a shortcut, using a special record syntax:

var #person = record (Name="Jim Jones", Age=20, Type="Friend")
 insert #person at &path
 

Or, even this:

insert record (Name="Jim Jones", Age=20, Type="Friend") at &path
 

But, again, it's often clearer to put the code on multiple lines.

Lastly it's worth mentioning security. Your script runs with the same permissions that the user invoking your script has. This means that some of your operations, such as deleting records, may fail because the user doesn't have permissions. This will cause your script to abort and a permission error shown to the user.

It may not always be direct. Sometimes you may update one record, which raises an event script that updates a different record, and it's that record the user doesn't have access to. Either way, the security system is there to protect your data no matter how it's updated.

There may be times when you need to bypass the restrictions the user has, because your script needs to update parts of the database the user does not have access to. You can do this using a special command called 'elevate':

elevate on
 

By putting 'elevate on' you remove all security restrictions and your script has full access to the system. At the end of the script you should run 'elevate off' to re-enforce the security. However, at the end of your script that will be done for you automatically.