Table API (Server)

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 // test is record is a new record taskrec isnewrecord(); fields/relationships begin with upper case letters and words (i e camelcase) can not begin with a number // get the short description text taskrec shortdescription(); // test if the short description has a value taskrec shortdescription hasvalue(); query methods all capital letters // query all users where the active flag is true table("user") equal("active", true) query(); table available functions true 247 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type table record available functions true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type field available functions function returns description evaluatescript( scope) 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 triggerrec scriptfield evaluatescript({ current table("incident") newrecord(), type "test"}); will evaluate the script, and make the properties 'current' and 'test' available to the script displayvalue() string the display value for the field relation available functions function returns description displayvalue() string the display value for the relation issingleresult() boolean returns true if the relation is a type that will return at most a single record relationtype() string returns the type of the relation name() string returns the name of the relation parenttablename() string returns the table name of the parent reference targettablename() string returns the table name of the target reference hasintermediatetable() boolean returns true if the relation has an intermediate table (i e it is a many to many relationship intermediatetablename() string returns the name of the intermediate table (null if is doesn't exist) intermediatetableparentfield() string returns the name of the intermediate table parent field (null if is doesn't exist) intermediatetabletargetfield() string returns the name of the intermediate table target field (null if is doesn't exist) geti18nkeyname() string returns the i18n key associated with the relationships name (label) geti18nkeydescription() string returns the i18n key associated with the relationships description addrelatedrecord(tablerecord) tablerecord adds a record to the relationship returns the created record getrecord() tablerecord returns the related record for a single result relationship isconnected() boolean returns true if the relationship is connected to a record instance (i e whether it is related to an actual data instance) each( function(tablerecord) ) / foreach( function(tablerecord) ) allows the related records to be iterated through map( function(tablerecord) ) 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 canread() boolean returns true if the record can be read canwrite() 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 true 180 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type combining operators for complex queries true 220 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 aslist() list\<questionresponse> a list of the questions and answers in question order \<questionname> 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 hasanswer() boolean true if an answer is present answer() tablerecord the actual answer tablerecord question() tablerecord the actual question tablerecord prompt() string the prompt associated with the question (in the current users language) prompti18n() string the i18n key associated with the questions prompt value() object (field type only) the value of the answer displayvalue() 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; }, {});