overview 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 table('user') 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 tableprotected('user') naming/case convention the table api uses a strict case/capitalisation convention to ensure built in methods, fields and query names do not collide type convention example methods begin with lower case letter fields/relationships begin with upper case letters and words (i e camelcase) can not begin with a number query methods all capital letters table available functions function returns description table record object adds an empty new record to the table boolean checks whether a particular record already exists in the table two variants are currently supported using an id or using a query the function will throw invalidargumentexception if the types are not string (id) or criterion (query) table object (self) run the query we built against the table using the query operators table object (self) adds a json query to the current query these can be obtained from the query builder as mentioned here get request retrieving records docid\ scdfmk8b0xoec3wpyjfql table object (self) returns the rows in ascending order, sorted by fieldname table object (self) returns the rows in descending order, sorted by fieldname boolean returns true if there are more records to read in the table, false otherwise table record object returns the next available record in the table array of results, filtered by function runs the query if it has not been run before using query() then for each record in the dataset, calls a callback function function and pass a single parameter to it the table record object return result will be an array containing the return result of each record that passes the filter function nothing runs the query if it has not been run before using query() then for each record in the dataset, calls a callback function function and pass two parameters to it the table record object the record number note that each() cannot be chained as it does not return a table record array of results from function runs the query if it has not been run before using query() then for each record in the dataset, calls a callback function function and pass two parameters to it the table record object the record number return result will be an array containing the return result of each invocation of function note if function returns the javascript value undefined that entry will not be included in the result array to return a 'blank' entry, return null instead of undefined table object (self) sets a callback function against the table this function will be invoked whenever a query execution returns no rows the callback function only needs to be set once for the entire duration of the object life the called function receives the table object as a parameter object returns all the fields (direct and reference) of the table in the form a of key/value pair object the key is the field name and the value is a field object field object returns the field object corresponding to the field fieldname object returns all the relations of the table in the form of a key/value pair object the key is the field name and the value is a relation object relation object returns the relation object corresponding to the field relationname string return the ui link for the given table type with no optionsobject, it simply returns the default link for the table type e g https //sandbox servicely ai/#/user the following are the options available create add the link element for creating a new record, e g , aspect add specific aspect to the link, e g objectonly just the object of interest, not the enclosing menus, etc relative link does not include domain/host part, only the relative part note the options can be used together, say will produce https //sandbox servicely ai/!#/user/ create/selfservice the other options are discussed in the similar method for table records below on this page managing batches table object (self) sets the maximum number of rows to return when querying the table; this enables the batch mode, explained in this section of the article table object (self) sets the position where to start returning the rows from integer returns the number of rows available in the current batch please note that as of version 1 9 0 it is suggested to instead use the aggregation api aggregation api integer returns the total number of rows available, regardless of the batch size please note that as of version 1 9 0 it is suggested to instead use the aggregation api aggregation api integer will delete all the records matched by the specified query withough table record available functions function returns description nothing finalise the creation of the table record the new record must be prepared by using newrecord() at the table object level nothing delete the table record nothing update the table record table record a shortcut for applying a set of field values to the record example will set the fields name,active, and status to the appropriate values will respect the ‘disablesystemfieldrestrictions’ and ‘disablemanagedfieldrestrictions’ options will not set system or managed fields if the restriction is in place, but will allow setting the fields if the restriction is disabled table record same as ‘apply’ but missing fields will be explicitly set to null boolean returns true if the record is new and has not been saved yet returns false otherwise table record clone the table record and returns the cloned record at that stage the cloned record does not persist in the database and needs a call to create() to do so all the field values from the original record are copied onto the cloned one, with these exceptions all the system fields all the fields with a unique constraint the reference fields point to the same foreign record than the original record the flag clonenmrelationships indicates whether we want the many to many relationships to be cloned as well see description hierarchy is a chain of reference fields and finishing with a direct field evaluate() evaluates this chain and depending on how it's been invoked will return if no callback functions have been passed on success the value of the direct field; on failure or null on failure if callback functions have been passed whatever those functions returned the success callback function receives a parameter which can be the table field object if the last element of the hierarchy is a direct field the table record object if the last element of the hierarchy is a reference field string returns the name of the table the record is originated from list\<string> returns a list of the associated field names field object returns the field object of the field fieldname variant return the value stored in the field fieldname the type of the returned value depends on the field type table record (self) sets the field fieldname with value value and returns itself string returns the unique id of the record boolean returns true if field fieldname exits in the field collection of the record table list\<string> returns a list of the associated relation names relation object returns the relationship corresponding to the relationname boolean returns true if permission checking is enabled, false otherwise since 1 7 sets the value of the record check permissions flag boolean returns true if validation is enabled, false otherwise sets the value of the record validation flag boolean returns true if triggers are enabled, false otherwise sets the value of the record triggers enabled flag boolean returns true if system field update is enabled, false otherwise sets the value of the record system field update flag boolean returns true if managed field update is enabled, false otherwise sets the value of the record managed field restrictions enabled flag boolean returns true if system field restrictions are enabled, false otherwise sets the value of the record system field restrictions enabled flag boolean returns true if audit is enabled, false otherwise sets the value of the record audit enabled flag table record disables processing of any triggers which would usually execute against the record on creation, update or delete table record disables processing of any table of field validation which would usually execute against the record on creation or update table record disables processing of any table of field permission checks which would usually execute against the record on creation or update table record disables the updating of the system managed field (e g updatedby, updatedon, etc) table record disables the restrictions on updating system fields table record disables the restrictions on updating managed fields table record disables all of the above validation checks disablevalidation() disablepermissionchecks() disablesystemfieldupdate() disablesystemfieldrestrictions() disablemanagedfieldrestrictions() note this method does not disable triggers (as that is not validation) string the display value for the record string return the ui link for the given table record with no optionsobject, it simply returns the default link for the record, e g " https //sandbox servicely ai/#/user/4028818a3a2f75cc013a311ffad60000 " the following are the options available aspect add specific aspect to the link, e g will produce https //sandbox servicely ai/#/user/4028818a3a2f75cc013a311ffad60000/selfservice https //sandbox servicely ai/#/user/4028818a3a2f75cc013a311ffad60000/selfservice objectonly just the object of interest, not the enclosing menus, etc e g will produce https //sandbox servicely ai/!#/user/4028818a3a2f75cc013a311ffad60000 note the options can be used together, say {aspect "selfservice", relative true, objectonly true} boolean checks to see if the record would violate any unique constraints if saved questiontablerecordwrapper if the record was created from a catalog item, provides access to the questions and answers field available functions function returns description result of the script evaluation if the field is 'code' field, the script will be evaluated using the supplied scope object note only available for fields of type code example will evaluate the script, and make the properties 'current' and 'test' available to the script string the display value for the field relation available functions function returns description string the display value for the relation boolean returns true if the relation is a type that will return at most a single record string returns the type of the relation string returns the name of the relation string returns the table name of the parent reference string returns the table name of the target reference boolean returns true if the relation has an intermediate table (i e it is a many to many relationship string returns the name of the intermediate table (null if is doesn't exist) string returns the name of the intermediate table parent field (null if is doesn't exist) string returns the name of the intermediate table target field (null if is doesn't exist) string returns the i18n key associated with the relationships name (label) string returns the i18n key associated with the relationships description tablerecord adds a record to the relationship returns the created record tablerecord returns the related record for a single result relationship boolean returns true if the relationship is connected to a record instance (i e whether it is related to an actual data instance) allows the related records to be iterated through array of function result allows the related records to be iterated through and an array to be constructed with the result of the calling function boolean returns true if the record can be read boolean returns true if the record can be written to setting relationship values single relationship 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 // get an incident record let incident = table("incident", "inc0000003"); // get a user to associate let user = table("user", "admin"); // valid incident requestor(user); // also valid incident requestor(user id()); multiple relationship 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 // get a user to associate let user = table("user", "admin"); // get a set of groups to associate let groups = table("group") starts with("name", "support"); // set the users groups to this set user group(groups); you can also incrementally add records to a multiple relationship // get a user to associate let user = table("user", "admin"); // get a set of groups to associate let newgroup = table("group", "cab"); // add the new relation, preserving the existing relationships user group addrelatedrecord(newgroup); accessing a single record 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 table('user', '4028818a3a2f75cc013a311ffad60000') username(); if you want to query the table by using one or more of its fields, you can use the table query syntax table('user', equal('username', 'chrisjones')) getfieldvalue('firstname'); 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 note that both those constructs directly return a table record object; the platform will throw an exception platform 10007 no record found if the query returns no row; the platform will throw an exception platform 10003 multiple records were returned when a single record was expected if the query returns more than one row it is highly recommended to catch those exceptions and properly handle them in your code accessing field values accessing the current value the current value of a table field content can be obtained by using one of these three approaches // using the getfieldvalue() shortcut neater and easier to read this is our favorite! table('user', equal('username', 'chrisjones')) firstname(); // using the getfieldvalue() functiont able('user', equal('username', 'chrisjones')) getfieldvalue('firstname'); // going through the table field object table('user', equal('username', 'chrisjones')) getfield('firstname') getvalue(); checking the presence of a value the presence of a value can be verified by using the field table function hasvalue() if (table('user', equal('username', 'chrisjones')) firstname hasvalue()) { // do something } retrieving the original value of a field 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 var oldvalue = current firstname getoriginalvalue(); var newvalue = current firstname(); var haschanged = (oldvalue != newvalue); if ( haschanged ) { current comments('previous first name was ' + oldvalue); } determining whether a field has changed the above example can be simplified by using the 'haschanged' method of the field object if ( current firstname haschanged() ) { log debug('value has changed'); } setting field values setting a value for a primitive type 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' // using the setfieldvalue() shortcut neater and easier to read this is our favorite! table('user', equal('username', 'chrisjones')) firstname('simon'); // using the setfieldvalue() function table('user', equal('username', 'chrisjones')) setfieldvalue('firstname', 'simon'); // going through the table field object table('user', equal('username', 'chrisjones')) getfield('firstname') setvalue('simon'); setting a date value 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 table('user', equal('username', 'chrisjones')) birthdate(datetime withdate(1970,6,11)) update(); setting a value for a reference a reference field can be set by setting either the id of the target record or the table record itself var schedulerec = table('schedule', equal('schedulenumber', 'sch 12')); // valid table('user', equal('username', 'chrisjones')) schedule(schedulerec); // also valid table('user', equal('username', 'chrisjones')) schedule(schedulerec getid()); setting the value to empty use the javascript null value to reset the value of a field and make it empty table('user', equal('username', 'chrisjones')) schedule(null); querying data 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 query operators 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 operator description the simplest operator implementing a strict equality for example n ot e qual the reverse of equal() search for records by using wildcards the supported wildcards are a substitute for zero or more characters a substitute for a single character\[ charlist ]sets and ranges of characters to match\[^ charlist ] or \[! charlist ]matches only a character not specified within the brackets examples get the records where the value in the field is null note if you want to check if a field is not empty or null, you are able to use this in conjunction with the not operator search for records where the field is between two range limits, like (note the two values forming the interval are in an array) get all the records that match one of the values of a list for example (note the second parameter is an array) operator g reater t han , g reater or e qual, l ower t han or e qual, l ower or e qual use them like this operator to check if a boolean field is either true or false combining operators for complex queries operator description reverse the logical result of any constructs for example combine two or more operators and apply a logical and operation combine two or more operators and apply a logical or operation query another table through its table relation this offers a very powerful method for joining multiple tables the example below prints out all the active users that are not a member of the group called "servicely" it is also possible to perform subquery operations on tables which do not have a managed relationship (for example from availablevalue to localizedmessage) the example below prints the key/value pairs for all the availablevalue entries which have a localizedmessage starting with the word 'new' simple queries simple queries typically use one standalone operator in a simple construct like that table('user') like('username', 'chris%'); standalone operators can be combined by chaining them, in a construct like this table('user') like('username', 'chris%') between('birthdate', \[ new datetime(\[1968, datetime november, 27]), new datetime() ]); chaining standalone operators like in the example above is equivalent to an and complex queries 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 // query pseudo code is where lastname is empty, or (username like 'dion%' and lastname = 'williams'), or username is one of 'chris', 'erik', 'mike' table('user') or( empty('lastname'), and( like('username', 'dion%'), equal('lastname', 'williams') ), in('username', \['chris', 'erik', 'mike']) ); 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 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 var euser = table('user') equal('active', false); euser query(); while (euser hasnext()) { processmyrecord(euser next()); } function processmyrecord( record, recnumber) { log info('user #{} login is {}', recnumber, record getfieldvalue('username')); } however the table object also offers the each() function it takes a callback function as a parameter and would be used like this var euser = table('user') equal('active', false); euser each(processmyrecord); function processmyrecord( record, recnumber) { log info('user #{} login is {}', recnumber, record getfieldvalue('username')); } or, in a more condensed way (not necessarilly always recommended from a readibility standpoint) table('user') equal('active', false) each(function ( record, recnumber) { log info('user #{} login is {}', recnumber, record getfieldvalue('username')); }); handling a zero row query a convenient way of handling a situation where a query returned no records is to set a callback function using onnoresult() table('user') onnoresult(noresults) equal('username', 'doesnotexist') query(); function noresults( table) { // do something } batching the results 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 var myusers = table('user') maxresults(5); 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 var table; var batchsize = 3; var startfrom = 0; var totalrecords = 0; do { // set the maximum number of results &#x9;table = table('user') maxresults(batchsize); &#x9; &#x9;// set the starting point &#x9;table firstresult(startfrom); &#x9; // now query the table, within the limits of the batch table query(); // returns the result count of the query without the maxresults applied totalrecords = table totalresultcount(); &#x9;// show details about the batch log info("records {} to {} of {}", startfrom + 1, startfrom + table resultcount(), // resultcount returns just the rows in this particular instance or the query totalrecords ); // and the the records we got back log info(table each( function( rec) { return rec username(); } ) join(", ")); // increment our start position startfrom = startfrom + batchsize; } while (totalrecords 1 > startfrom); // and start again if we need to creating, cloning, updating and deleting records new record 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 table('user') newrecord() username('simon bofrost') firstname('simon') lastname('bofrost') email('simon bofrost\@servicely com') birthdate(datetime withdate(1918, 7, 18)) create(); you can check whether a record is new (as opposed to already saved) by using the isnewrecord() function cloning 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 table('user', equal('username', 'chrisjones')) clone(true) username('simon bofrost') firstname('simon') lastname('bofrost') email('simon bofrost\@servicely com') birthdate(datetime withdate(1918, 7, 18)) create(); updating 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 table('user', equal('username', 'simon bofrost')) &#x9;firstname('robert') &#x9;lastname('munster') &#x9;update(); deleting simply use the delete() function to delete a record table('user', equal('username', 'simon bofrost')) delete(); navigating through relations 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 table('user', equal('username', 'chrisjones')) organization value(); // or // table('user', equal('username', 'chrisjones')) getfieldvalue('organization'); whereas this construct will return the organisation table field, giving access to all its functions table('user', equal('username', 'chrisjones')) organization; finally this one will return the organisation table record object table('user', equal('username', 'chrisjones')) organization(); as seen before, this latter enables statements like those ones, where we directly access fields from the target record table('user', equal('username', 'chrisjones')) organization() name() table('user', equal('username', 'chrisjones')) organization() getfieldvalue('name') 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 table('user', equal('username', 'chrisjones')) organization() schedule() createdon(); safely traversing the relations the issue with a construct like the one below table('user', 'chrisjones') organization() schedule() createdon()); 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 // returns the actual name of the schedule, or null if any part of the chain is empty let schedulename = table('user', 'chrisjones') evaluate('organization schedule name()'); // returns the name field of the schedule (note no '()' at the end) let schedulenamefield = table('user', 'chrisjones') evaluate('organization schedule name'); 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 let isscheduleactive = table('user', 'chrisjones') evaluate( 'organization schedule', schedulerec => schedulerec active(), () => false ); // true if the schedule is found and active, false otherwise isscheduleactive; let scheduledisplay = table('user', 'chrisjones') evaluate( 'organization schedule name()', name => name, () => 'default text' ); // will be the schedule name if the path exists will be 'default text' if there is no organisation, schedule, or name scheduledisplay; questions 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 questions object available functions the questions object is similar to a tablerecord object it has all the same methods and properties except the additions below function returns description list\<questionresponse> a list of the questions and answers in question order questionresponse returns a questionandanswertablerecord that contains methods to access the question and answer responses questionresponse available functions 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 function returns description boolean true if an answer is present tablerecord the actual answer tablerecord tablerecord the actual question tablerecord string the prompt associated with the question (in the current users language) string the i18n key associated with the questions prompt object (field type only) the value of the answer string (field type only) the displayvalue of the answer if the question is related to let questions = current questions(); // questions can be accessed directly by their 'name' log info(`requestedfor question ${questions requestedfor value()} displayvalue ${questions requestedfor displayvalue()}`); // all question names e g // \["requestedfor","shortdescription","description","requestoremail","requestorphone"] let allquestionnames = questions aslist() map(question => question name()); // all question prompts e g // \["who is the contact person for the incident?","please provide a short, one line summary of your issue ","please provide any additional information that may help us in dealing with your issue ","what email address should we use for sending updates?","what is the best contact phone number for us to use?"] let allquestionprompts = questions aslist() map(question => question prompt()); // all name/display value pairs e g / { "requestedfor" "andrew venn", "shortdescription" "testing catalog questions", "description" "demonstrating the api", "requestoremail" "andrew\ venn\@servicely ai", "requestorphone" "555 555 5555" } / let allquestionmap = questions aslist() reduce((map, question) => { map\[question name()] = question displayvalue(); return map; }, {});