Understanding Data Access

Database access in DbfScript is stateless. This means you access data by loading it into a temporary store, changing it, and then committing those changes back again.

All retrieving from the database takes place with the 'load' keyword. This pulls data from the database that matches the specific path.

Once it is retrieved, you would normally assign the data to a variable, for example:

var #inv = load(&invPath)

In this case we are loading the record pointed to by the contents of the &invPath variable, and put it into a variable called #inv.

Once it is retrieved, it is set up to monitor what is being changed on it. You don't have to change it at all, you can also just use it for retrieving specific bits of data.

For example:

var #date = #inv/Date

This retrieves the Date field of the invoice record, and assigns it to another variable called #date.

You can also use predicates on the temporary variable to apply filters to the data that's already in your temporary store. This doesn't touch the database, rather it just filters what's in memory. For example:

var #comments = load(&path/Comments)
var #myComments = #comments[EnteredBy="tom"]

This will load all comments into #comments, but then create a second variable that only holds comments entered by a user called "tom".

If what you retrieved is a list, you can loop through that list using the loop keyword. For example:

loop through #comments as #rec
set #rec/Authorized = false
end loop

In this example we are looping through all comments in the #comments list, and we set the Authorized field to false (assuming this fictitious field exists).

As you saw, you can change the values of fields in your temporary variable by using the set keyword. But remember that changing it in your temporary variable does not commit it to the database. To commit something back to the database you need to 'save' it using the 'save' keyword:

save #rec

This only updates the fields you have changed in that record. Any events that are tied to that record will then be triggered, assuming they match the conditions that were set on that event.

If your record contains a link field, you can also access fields in the record it is linking to. For example, say you had a link field in your record that pointed to the company the invoice was for. You could then access the name of the company using this expression:

var #name = #inv/Client/Name

Behind the scenes this actually looks at the path held in the Client link, retrieves the record associated with that, and retrieves the Name field from that. Keep in mind that such expressions as this will result in a database access, so they should be used sparingly if performance is a consideration.

Next Topic: Variables


Please post all questions on the support forum. Thank you.