Paths are fundamental to accessing the database in WorkflowFirst. Because WorkflowFirst applications use a hierarchical database, the path is the equivalent of the address of a specific record or list. They are similar in many respects to paths in file systems, or to pointers in languages like C++.
A path is a human-readable string. Each element of the path is separated by a forward slash. Each element is referred to as a 'path item'.
For example, you may have a list of comments associated with a line item in an invoice. In this case the path may be:
However, to specify the comments for a specific invoice, and a specific line item, you will need to add a filter. The filter is known as a predicate.
Any path item can have a predicate. An example of a predicate is:
Here we are highlighting the parts of the path items that are predicates. The predicate always consists of a field, a comparison operator, and a value. The field must be any field in the record related to the path item. In this case, InvID is a field of the 'Invoices' type.
The following comparison operators (often referred to as comparators) can be used:
= (equals), != (doesn't equal), >= (greater than or equals), <= (less than or equals), > (greater than), < (less than)
In addition, the two comparators 'like' and 'notlike' can also be used. These are special operators that allow you to perform pattern searching. When using either of these operators, the value can also contain an asterisk (*) as a 'wildcard' that can match any number of characters, or a question mark (?) that can match any single character.
/Invoices[EnteredBy like "jo*"]
...will match any invoices where the EnteredBy field starts with 'jo'.
/Invoices[EnteredBy like "t?m"]
...will match any invoices where the EnteredBy field starts with 't', ends with 'm' and has a single character in the middle, such as Tim or Tom.
Predicate searches are not case sensitive.
In DbfScript you can use paths as first-class citizens of your code. A variable that contains a path should start with an ampersand, such as &path. The &path variable is special in that it will always be set at the entry of your script, usually to the path where the script was invoked. Another special path variable is &root, which is the root ('/'), from which you can access any part of your database.
You can retrieve the record or list at the given path by using the load command:
var #list = load(&path)
If the path contained in &path ends with a predicate, it will likely return a single record. If it doesn't contain any predicates, it will likely return a list.
The values of predicates in your paths in DbfScript can reference other variables or can be expressions. For example:
...in this case we are creating a path where the EnteredBy field of the comment must be equal to the value of the #user variable.
Your expressions can be more complex:
&path/Comments[Date > AddDays(Now(), -30)]
...this creates a path that refers to all comments added within the last 30 days.
While the predicate value expressions can be complex, the field part of the predicate must always be a simple field name.
Multiple predicates can also be provided. Each predicate must be separated by a comma. Multiple predicates that have the same field name are ORd together. If they have different field names, they are ANDed together. For example:
This will create a path filtered by comments entered either by tim or tom.
&path/Comments[EnteredBy="tom", Date > AddDays(Now(), -7)]
This will create a path filtered by comments both entered by tom AND entered after 7 days ago.
If you want to OR two predicates that have different field names, simply use field tags - where you put a colon and the same label after the field name, for example:
&path/Comments[EnteredBy:a="tom", Date:a > AddDays(Now(), -7)]
This will return a list of comments that are either EnteredBy tom OR any comment entered in the last week.
All filters must adhere to this format of comma-separated predicates - you cannot put in arbitrary logic expressions such as:
&path/Comments[EnteredBy=#user & (Date > AddDays(Now(), -7) | (EnteredBy!=#user & (Date < =AddDays(Now(), 7)))]
This is invalid as it does not adhere to the comma predicate format. This particular filter should instead be split into two queries of:
&path/Comments[EnteredBy=#user, Date > AddDays(Now(), -7)]
&path/Comments[EnteredBy!=#user, Date < AddDays(Now(), -7)]
This is a trade off in order to keep the database access simple, to allow for efficient translation to a variety of data stores, and to enable real-time update subscriptions to queries.
You can also query for values in sub-forms under a list. We call these predicates subrefs:
This will return all users who have Roles list with an entry that has RoleID set to Manager.
Some DbfScript functions return paths. For example you can retrieve the path of any record that came from the database by using the GetPath function:
var &commentPath = GetPath(#rec)
set &commentPath = &recPath/Comments
A path expression in DbfScript can also start with a function that returns a path. So the above statements can also just be rewritten as:
var &commentPath = GetPath(#rec)/Comments
Understanding the way paths work is fundamental to being able to use DbfScript.
Next Topic: Understanding Data Access
Up Since 11/12/2018 1:21:44 PM