The Table and Table Record objects are used to deal with Servicely tables and the records stored in them. They offer a series of functions to query, create, update and delete Table records.

Tables can be accessed by using this generic notation:

Where ‘User’ can be replaced with any Table name.

By default, the Table API does not enforce all Permissions and Restrictions (as it is most often used in a context where the Users Permission set is not the intended permission set). If you wish all permissions and restrictions to be applied, you can use the TableProtected method.

The Table API uses a strict case/capitalisation convention to ensure built in methods, fields and query names do not collide.

Setting the value of a single result relationship is the same process as setting any other field. You can set the value by assigning either the String ID of the target record, or by supplying a reference to the record itself.

Setting the values of a Multiple relationship is similar to setting a field or single relationship, except that you can also specify multiple records to associate.

You can also incrementally add records to a multiple relationship.

Although accessing a single record is simply a particular instance of a query, Servicely offers quicker ways of achieving this and thus avoiding longer constructs.

If you have an ID in your hand, the best way to fetch the corresponding record is to use this statement:

If you want to query the Table by using one or more of its fields, you can use the Table Query syntax:

Other ways to retrieve a record, is shown below using an example Incident. All methods return the same result.

In case you're after one single record, it is better to use those constructs instead of a complete query, as you can directly chain functions like in the example above. It helps to write concise, readable code.

You will need to know a field's name prior to configuring your script to get its values. Recommended way to do that, is to find the field on the form of the Table you want to interrogate, right click on the field and left click on "Copy field name". This will place the field name into your clipboard:

The current value of a Table field content can be obtained by using one of these three approaches:

The presence of a value can be verified by using the Field Table function hasValue():

When a server side script is executed on a trigger, the value that was in the field before invoking the trigger can be retrieved by using the Field Table function getOriginalValue(). For example the following script, stored in a on-before trigger, will save the previous first name in a field called comments before the update is committed:

The above example can be simplified by using the 'hasChanged' method of the Field object.

To get a record's related list of records, you will need to find the related list's field name. Similar to getting a regular field's name, you need to right click on the related list's label, and then left click on "Copy field name" to copy the related list's field name onto your clipboard:

You can then access what's in the related list and it will be returned in an array of TableRecord objects, with each representing a record in the related list.

Just like for accessing a value, setting a value supports three approaches. In the examples below we set the first name of a user to 'Simon':

The easiest way to set a date is to use the global DateTime object. See the Table API and Table Record objects article for more information about handling dates and times with Servicely:

A reference field can be set by setting either the ID of the target record or the Table Record itself:

Use the Javascript null value to reset the value of a field and make it empty:

Queries are executed by using different functions on a Table object. Different approaches can be used depending on the complexity of your query. This chapter also explains how to process each row and how to handle the situation where no rows are returned.

Contrary to single record access that returns a Table Record object, query constructs explained in this chapter return a Table object. Access to the records of this Table therefore requires the use of the next() or each() function.

Both simple and complex queries use the same set of operators, explained below. Complex queries will combine several operators whereas simple queries typically only use one. Although there are no fundamental differences between simple and complex queries, we considered that introducing the concept in two steps makes it easier to understand how the Servicely query engine works.

The following table is a list of the supported queries. They be used alone (for simple queries) or combined by using AND(), OR() and NOT() operators.

Simple queries typically use one standalone operator in a simple construct like that:

Standalone operators can be combined by chaining them, in a construct like this:

Chaining standalone operators like in the example above is equivalent to an AND.

Complex queries differ from simple ones by the use of NOT, AND and OR operators. Those queries can be built by nesting (that is, combining) different standalone operators. A typical complex query would like this:

Because some queries can be hard to read, we recommend that complex queries should be presented with indentation (like the example above).

Processing each row returned by the query can be done through a "traditional" iteration traversing the whole dataset and using the query(), next() and hasNext() function. In the following example we process all the inactive users:

However the Table object also offers the each() function. It takes a callback function as a parameter and would be used like this:

Or, in a more condensed way (not necessarilly always recommended from a readibility standpoint):

A convenient way of handling a situation where a query returned no records is to set a callback function using onNoResult():

It can be sometimes useful not to process all the rows of a query in one shot, but rather process in several batches: this is what the batch mode allows us to do.

A batch size must be set first, which is done by using the maxResults() function:

Then we can use two functions to control our navigation through the batch: resultCount() returns the number of rows in the current batch (between 0 and the size of the batch), whereas totalResultcount() returns the total number of rows (i.e. not taking the batch size into account).

The following example shows how a Table can be queries and walked through using the batch mode:

Before a new record can be created it must be initialised by using the newRecord() function. This function returns an empty Table Record that can then be used to set the field values and be created. Because accessing the fields via the shortcut function returns the same Table record, multiple operations can be chained to a point where we can initialise a record, set the values and create the record in one construct:

You can check whether a record is new (as opposed to already saved) by using the isNewRecord() function.

An existing record can be cloned by using the clone() method. This will create a brand new record and copy all the field values of the source record to the cloned one. The method accepts one boolean that indicates whether the many-to many relationships must be cloned as well. In the below example, we clone an existing user and change the first name, last name, login, birth date and email address. Note that the system fields and the fields with a unique constraint are NOT cloned during the process.

Updating a record can follow the same principle; in the example below we update in one construct the first and last name of an existing record:

Simply use the delete() function to delete a record:

Consider the Organisation reference field of the User Table and the different possibilities of accessing its value (also see chapter Accessing field values).

The following constructs will return the ID of the user's organisation:

Whereas this construct will return the organisation Table Field, giving access to all its functions:

Finally this one will return the organisation Table Record object:

As seen before, this latter enables statements like those ones, where we directly access fields from the target record:

This can be repeated multiple times allowing navigation through several relationships. However bear in mind that each access to a foreign Table will end up making a database round trip.

The issue with a construct like the one below:

is that we assume all of the intermediate elements (i.e. Organization and Schedule) have values. If either of those items do not have a value, it will lead to a ‘Null Pointer’ error, and the script will fail. A safer way is to use the ‘evaluate’ method, which will check that the hierarchy is valid throughout the entire path.

The following statement will return the organization name is a valid reference, or null if the organization field is empty.

When evaluating a hierarchy, we can also indicate that we want a specific function to be called upon success and failure. Consider the following code:

If a record was created from a catalog item, the questions that were answered as part of the Catalog process can be accessed through the questions API.

The questions object is similar to a TableRecord object. It has all the same methods and properties except the additions below.

The QuestionResponse object is a wrapper around a Field object or Relation object (depending on the Question type). All the same methods and properties are available, with the addition of the methods below.