DbfScript makes it very easy to interact with the database of your application. Because the database used in WorkflowFirst is hierarchical, querying data can be achieved without the complexity of joins and other difficult query structures in other languages.

To understand querying data, it's first important to understand how paths work. Here we'll go through some example of using paths. For more information, see the section on Understanding Paths.

A path refers to the address of a particular record in your hierarchical database. A path consists of a series of field names, separated by forward slash. eg.

/Tasks/Comments

This example refers to the top level Tasks application tab, and then the Comments underneath the task.

This path isn't useful, though, because it doesn't say which task it refers to. This is where predicates come in. A predicate is a filter that specifies how to find a particular record. Predicates are put into square brackets after the field name. The predicate itself is a condition - usually a sub-field name, a comparison operator (like equals) and a value in quotes. eg.

/Tasks[Title="Shopping"]/Comments

In this example this path will return the list of Comments underneath the task that has the title, "Shopping".

Often you'll see a path that looks something like this:

/Tasks[ID="46d039ca-5bc9-4aa4-ab26-304ebc11ef56"]/Comments

This is the same idea, but the filter is on the field ID. Every single record in your application will have an ID, and it will be a large series of numbers separated by dashes. This is known as a GUID, or Globally-Unique-Identifier. This ID is all but guaranteed to be unique across your database, and probably any other database you'll ever come across.

Wherever you run a script, you will be given a path where the script is running. This will be stored in a variable called &path. The ampersand at the beginning signifies that it's a path variable, instead of a record variable (which will start with a hash or pound symbol).

So if in your script you have:

message &path

You will see the path of the record where it is running. 

To create your own paths, you can declare a new variable and set the path to that. If your path isn't relative to another path, you start with the &root variable.

var &myPath = &root/Tasks[Title="Some Task"]/Comments

This example creates a new variable called myPath that points to the list of comments underneath the task with the title "Some Task".

You can also add on to existing paths. To do this, just start with an existing path variable, and continue as if it were going onto the end of the path:

var &taskPath = &root/Tasks[Title="Some Task"]
var &commentsPath = &taskPath/Comments

Or you can add a predicate (filter) on:

var &someComment = &commentsPath[Author="Bob"]

When defining predicates, you can also reference other variables and use expressions:

var &someComment = &commentsPath[Author=#authorName]

In this example we're filtering for the author name, but getting that from the variable #authorName.

You can also extract a portion of a path, by using ExtractPath:

var &task = ExtractPath(&someComment, &root/Tasks)

This will extract the part of the paths that goes up to /Tasks - no matter what kind of path was passed in.

Often it's useful to detect whether something returned a value. In the above example, ExtractPath will return Nothing() if it didn't find the path you are requesting. You can use a function called Exists or NotExists to determine this:

if Exists(&task) then
    log "My task path: " + &task
end if

Now that we know the basics of path manipulation, we can use the path to retrieve data from the database.

To do this, we use the load command. In its most basic form, you simply pass in a path:

var #rec = load(&path)

This will load the record at the path &path and put it into a variable called #rec. (If the path points to a list, then load will return a list instead)

Once you have the record, you can get fields out of it using the slash syntax:

message #rec/UserName

In this example, we're getting the field called UserName from the record called #rec.

If, in the above example, &path didn't end in a predicate, it will be pointing to a list instead of a record. That means that #rec would contain a list. You can also force the removal of predicates from the end, to always get the list and not a particular record, using ClearPredicates. In this example, we're loading a list into a variable called #list.

var #list = ClearPredicates(&path)

Once you have a list, you can loop through the results:

loop through #list as #rec
   log #rec/UserName
end loop

This will loop through the records in #list, and for each iteration set the variable #rec to be equal to each record, running the bit of code between the loop... and end loop.

You can also count the records:

log Count(#list)

Or you can perform some aggregate functions, such as summing up all the values of the Price field in the list:

log SumField(#list, "Price")

Or you can search for a particular record:

log Find(#list, "Price", 100)

This isn't the best practice, though. It's usually best to send such operations to the server. For example, to find something we'd use a predicate instead:

var #list = load(&path[Price = 100])

This would return a list of all the items with a price of 100. The database does this operation very quickly using indexes, which is why it's best practice to use predicates.

But perhaps you just wanted the first item:

var #rec = load(&path[Price = 100] with top 1)

Notice the "with top 1". The "with" provides lots of ways of changing how the query is run. You can also specify fields to retrieve:

var #rec = load(&path[Price = 100] with top 1; fields ( Name, Price ))

You put multiple "with" options separated by semicolons. In this case we're just retrieving the Name and Price fields. This may be more optimal if there are lots of fields, and you don't want to retrieve too much data.

You can also perform the summing we mentioned earlier:

var #rec = load(&path with fields ( Name, Price [total] ))

Notice the "[total]" after the field - that tells it to aggregate the results and total up all the Price fields that match. This just returns one record, so we don't need to put "top 1".

You can also sort the result using the "with sort" option:

var #rec = load(&path with sort ( Name ascending ))

This will query but return the list in the alphabetical order of Names. This can be useful if you want to get the top 5 most expensive options, for example:

var #rec = load(&path with top 5; sort ( Price descending ))

This will retrieve the top 5 items in the order of the price descending, with the most expensive at the top.