In Tabidoo JavaScript, you can manipulate data in tables in your applications. You can perform the same operations on the data and with the same user rights that you can perform as a logged-in user in the graphical interface - e.g. loading/filtering data, inserting new data, updating and deleting existing data.
For example, you might want to automatically generate (insert) a new invoice record in the "Invoices" table after setting the status of the order record to "Shipped" in the "Orders" table - you can do it using table functions in the After model save event of the edit form in table Orders.
A table-manipulation request can be performed on the client-side in JavaScript of the edit form (events On model load, On model change, Before model save, and After model save) and on the server-side (events Before model save and After model save).
A table-manipulation request cannot be performed in JavaScript for calculated fields, JavaScript for a data load condition defined in a user role, and in JavaScript for a condition for using a whole user role.
Using these functions is very similar to using the Tabidoo API.
You can learn more about Tabidoo API documentation here: https://tabidoo.docs.apiary.io
See the example at the bottom of this page ;)
You can use any function from the doo.table namespace to manipulate table data:
async getData(tableNameOrId, options?, applicationId?)
Load a list of data from the specified table according to filter, paging, sort, ...
async getCount(tableNameOrId, options?, applicationId?)
Get the number of data in the specified table according to the filter.
async getRecord(tableNameOrId, recordId, applicationId?)
Load one record by its ID from the specified table.
async getLinkedRecordsV2(tableNameOrId, recordId, fieldName, options?)
Get all records from the linked table which are linked to a record in the current table
(a new, simplified version of getLinkedRecords function). This function can be used, for example, to retrieve all invoice rows linked with the invoice header. You need to know the invoice header table name (tableNameOrId), the invoice header record ID (recordId), and a link field name in the invoice header table (fieldName).
async getLinkedRecords(tableNameOrId, recordId, linkTableNameOrId, linkFieldName, applicationId?)
Get all records from the linked table which are linked to a record in another table. This function can be used, for example, to retrieve all invoice rows linked with the invoice header. You need to know the invoice rows table name (tableNameOrId), the invoice header record ID (recordId), the invoice header table name (linkTableNameOrId), the name of a link field name in the invoice header table (linkFieldName).
async getParameter(tableNameOrId, fieldName, applicationId?)
Get the value from the first row in the table.
async getTableStructure(tableNameOrId, applicationId?)
Get one table of the application.
async createRecord(tableNameOrId, fields, options?)
Creates a new record in a specific Tabidoo table.
This function supports UPSERT mode - see the useUpsert parameter.
async updateFields(tableNameOrId, recordId, fields, options?)
Updates an existing record (specified by the recordId parameter) in a specific Tabidoo table.
This function supports UPSERT mode - see the useUpsert parameter.
This is a PARTIAL update - it only updates the fields that are sent.
async updateRecord(tableNameOrId, recordId, fields, options?)
Updates an existing record (specified by the recordId parameter) in a specific Tabidoo table.
This function supports UPSERT mode - see the useUpsert parameter.
This is a FULL update - it replaces the whole record with sent fields, thus unsent fields are deleted.
async deleteRecord(tableNameOrId, recordId, options?)
Deletes a single record (specified by the recordId parameter) in a specific Tabidoo table.
(be careful, this is an irreversible change.)
async createRecordsBulk(tableNameOrId, fieldsArray, options?)
Creates/Inserts multiple records in a specific Tabidoo table.
This function supports UPSERT mode - see the useUpsert parameter.
async updateRecordsBulk(tableNameOrId, bulkArray, options?)
Updates records in a specific Tabidoo table.
This function supports UPSERT mode - see the useUpsert parameter.
async updateFullRecordsBulk(tableNameOrId, bulkArray, options?)
Updates records in a specific Tabidoo table.
This function supports UPSERT mode - see the useUpsert parameter.
async deleteRecordsBulk(tableNameOrId, limit, filter, options?)
Deletes records in a specific Tabidoo table. Requires the limit parameter (max count of records) and the filter parameter.
(be careful, this is an irreversible change.)
async getFile(tableNameOrId, fileId, applicationId?)
Load one attachment file by its ID from the specified table - returns text, so this works only for txt, json, xml, ... files in UTF8 encoding.
async getFileBase64(tableNameOrId, fileId, applicationId?)
Load one attachment file by its ID from the specified table - returns an object with the properties content (base64), fileName, and mimeType.
async getFileArrayBuffer(tableNameOrId, fileId, applicationId?)
Load one attachment file by its ID from the specified table - returns ArrayBuffer.
async getThumbnail(tableNameOrId, fileId, applicationId?)
Load a thumbnail of one attachment file by its ID from the specified table - returns ArrayBuffer.
async getThumbnailBase64(tableNameOrId, fileId, applicationId?)
Load a thumbnail of one attachment file by its ID from the specified table - returns an object with the properties content (base64), fileName, and mimeType.
reloadUserData(tableNameOrId)
Reload user data in all data views (grid/cards/calendar/...) that are currently displayed to the user (main table, widgets, linked tables on opened edit form, ...).
As a parameter, you can pass in a simple string or array of strings (table name(s)/id(s)) you want to reload.
This function is called automatically after createRecord, updateFields, updateRecord, deleteRecord, createRecordsBulk, updateRecordsBulk, updateFullRecordsBulk, deleteRecordsBulk
You can learn more about these functions in Tabidoo API documentation here.
All functions except reloadUserData are asynchronous and you must use the await keyword to wait until they are finished and get a result or catch an error.
Explanation of the function parameters.
tableNameOrIdYou can use the table name (header), internal table name for API, or table ID to determine which table you want to work with. When using a name (header) of the table, it is expected that exactly one table in the application has this name otherwise, an error is thrown. Alternatively, you can set and use an internal table name (unique within the application), or a table ID (you can see the ID in the URL in the browser's address bar when you use this table in Tabidoo).
applicationIdIf this parameter is not provided (or it is null) then the ID of the current (opened) application is used (eg. app. ID where the edit form was opened). You can provide desired application ID when you want to load/save the data from/to a specific application (you must have sufficient user rights to load/save from/to this application). You can get the application ID from the URL (the address row in the browser) when you open desired application (eg. https://app.tabidoo.cloud/app/123-456/table/...).
recordIdUnique record identification (ID). The easiest way to get the record ID is to open the record form and click the share button - the URL containing the record ID will be displayed (look at the end of the URL). Example:
...?recordId=8ef96265-1e21-49db-909b-94a8f004aade
fieldsSet of record fields. Example:
{
"firstname": "Jack",
"surname": "Newman"
}options (GET)Options parameter for getData, getCount and getLinkedRecordsV2 functions.
Example #1 - Find all records where the field surname contains the string "Fox" or "Dog" or "text with , comma".
To filter text containing a comma, such as "text with , comma", you must escape the comma inside the text by a double backslash.
{
filter: "surname(in)(Fox,Dog,text with \\, comma)", // data filter in simple string form *
sort: "age,surname(desc)", // data sort
limit: 25, // number of records to load (one page)
skip: 3 // number of pages to skip (default)
}
Example #2 - Find all records where field age equals 25 and field firstname equals Jack or Peter.
{
filter: [
{
field: "age",
operator: "eq",
value: "25"
},
{
filter: [
{
field: "firstname",
operator: "eq",
value: "Jack"
},
{
field: "firstname",
operator: "eq",
value: "Peter"
}
],
filterOperator: "or" // "and"/"or" operator for items in "filter" array on the same level
}
], // data filter in advanced form (tree) *
filterOperator: "and", // "and"/"or" operator for items in "filter" array on the same level
sort: "age,surname(desc)", // data sort
limit: 25, // number of records to load (one page)
skip: 3 // number of pages to skip (default)
}options (CREATE, UPDATE, DELETE)Options parameter for createRecord, updateFields, updateRecord, deleteRecord, createRecordsBulk, updateRecordsBulk, updateFullRecordsBulk, deleteRecordsBulk functions.
It contains these optional parameters: applicationId?, reloadUserDataAfterAction?, useUpsert?, dataResponseType?.
Example of using the options parameter:
{
applicationId: 'My App', // application 'My App'
reloadUserDataAfterAction: false, // user data is not loaded
useUpsert: true, // allow upsert mode
skipAudit: true, // allows skip or force audit, otherwise table settings is used
dataResponseType: 'MetadataOnly' // use 'MetadataOnly' response type
}fieldsArrayParameter for createRecordsBulk function. Each set of fields represents a new record.
Example - Create two records (Jack Newman and Peter Novak):
[
{
"firstname": "Jack",
"surname": "Newman"
},
{
"firstname": "Peter",
"surname": "Novak"
}
]bulkArrayParameter for updateRecordsBulk and updateFullRecordsBulk function.
Example - Update two records:
[
{
"id": "c7609e47-a63c-4d6a-a4c3-3d8253dbf443",
"fields": {
"firstname": "Jack",
"surname": "Newman"
}
},
{
"id": "28df9ff3-4b1a-42ae-92df-d394577354f7",
"fields": {
"firstname": "Peter",
"surname": "Novak"
}
}
]fileIdUnique file identification (ID). Parameter for getFile and getThumbnail functions.
fieldNameThe name of the field in the table.
reloadUserDataAfterActionUser data is loaded (true) or not loaded (false) in all displayed data views (grid/tabs/calendar/...) that are currently displayed to the user (main table, widgets, linked tables on the open edit form, ...). The default value is true.
useUpsertTabidoo API and doo.table CREATE/UPDATE functions (including bulk functions) support two upsert modes:
createRecord, createRecordsBulk) enable UPSERT mode - UPSERT for CREATE functions is driven by the 'Import key field' (one field of the table marked as 'Import key field' in the table/fields settings - unique record identifier for data import) - the passed-in record has to have non-empty value in the field marked as 'Import key field' then this value is used for search for the record in the DB, if the record exists then it is updated, if the record does not exist then a new record is created (inserted). If the passed-in record has no value in the field marked as 'Import key field' then CREATE UPSERT raises an error.updateFields, updateRecord, updateRecordsBulk, updateFullRecordsBulk) enable UPSERT mode - UPSERT for UPDATE functions is driven by record ID - if the passed-in record has no id or the record is not found in the DB by id then a new record is created (inserted), otherwise found record is updated.The default value is false.
skipAudit
Option of whether or not the data change will be reflected in the audit.
Check Data audit limitation chapter for details here.
dataResponseTypeThe response body of the CREATE and UPDATE functions contains a newly created or updated record. You can change the default settings by changing that parameter.
filterYou can filter user data by simple filtering conditions. You can learn more about filters in Tabidoo API documentation here.
Example of the simple filter: Find all records where the field surname equals the string "Newman".
"surname(eq)Newman" // simple filter It does not support advanced API filters.
getData, getLinkedRecordsV2 and getLinkedRecords functions.{
"data": [
{
"id": "158cb6ef-02a6-47dc-8134-188c1b6426e6",
"created": "2019-03-22T14:41:23.5783244+00:00",
"modified": "2019-03-24T16:31:28.5783244+00:00",
"ver": 0,
"fields": {
"firstname": "John",
"surname": "Snow",
"age": 35
}
},
{
"id": "8905f3aa-97df-4c0b-b10f-d5fe3bcd6979",
"created": "2019-03-22T14:41:23.5783244+00:00",
"modified": "2019-03-24T16:31:28.5783244+00:00",
"ver": 2,
"fields": {
"firstname": "Jack",
"surname": "Reacher",
"age": 42
}
}
]
}
getCount function.{
"data": {
"count": 3
}
}
getRecord, createRecord, updateFields and updateRecord functions.{
"data": {
"id": "158cb6ef-02a6-47dc-8134-188c1b6426e6",
"created": "2019-03-22T14:41:23.5783244+00:00",
"modified": "2019-03-24T16:31:28.5783244+00:00",
"ver": 0,
"fields": {
"firstname": "John",
"surname": "Snow",
"age": 35
}
}
}
createRecordsBulk, updateRecordsBulk and updateFullRecordsBulk functions.{
"bulk": {
"successCount": 2
},
"data": [
{
"id": "158cb6ef-02a6-47dc-8134-188c1b6426e6",
"created": "2019-03-22T14:41:23.5783244+00:00",
"modified": "2019-03-25T17:22:26.5783244+00:00",
"ver": 0,
"fields": {
"firstname": "Jack",
"surname": "Newman"
}
},
{
"id": "2f64141a-ec9f-43df-a50d-1ca8eb277df7",
"created": "2019-03-22T14:41:23.5783244+00:00",
"modified": "2019-03-25T17:22:26.5783244+00:00",
"ver": 0,
"fields": {
"firstname": "Peter",
"surname": "Novak"
}
}
],
"errors": [
{
"type": "string",
"id": "string",
"message": "string"
}
]
}
Functions may fail due to missing or incorrectly filled-in parameters - eg incorrectly/incompletely filled-in data filter, text passed to a parameter of type number, a table was not found by name, etc. When editing javascript in Tabidoo Javascript editor you can see errors under the JS editor or in the browser's developer's console (F12).
You can also catch the error using the try-catch clause:
try {
// correct filter syntax: { filter: 'myitem(eq)5' } - internal field name, (eq) operator
const cnt = await doo.table.getCount('My table', { filter: 'My item = 5' });
} catch(err) {
console.log(err);
}
Caught err object:
{
"errorData": {
"errors": [
{
"type": "warning",
"id": "parameterParseFailedException",
"message": "'filter' parameter parse failed - filter condition 'my item = 5' has a bad format - operator not found.",
"RecordIndex": 0
}
]
}
}
You can learn more about API errors in Tabidoo API documentation here.
In case, you are not sure, how the model object looks like or what the value of a variable is, check the value in the developer console of the browser
console.log(doo.model);
Or you can debug your JavaScript step by step and check values of variables and so on in the developer console of the browser using the debugger command in your code (be sure to delete it when done :))
debugger;
You can open the developer console by pressing F12 in Chrome/Edge browser or CTRL+SHIFT+I in Opera browser and similarly in other browsers.
// 1. In this example, we load all orders with the status "Tentative" for a specific customer.
const orders = await doo.table.getData('My Orders', {
filter: 'Customer(eq)john@smith.com,Status(eq)Tentative'
});
// 2. Then we set the status to "Approved" and we increase the ordered amount in every order and then we update (save) all whole records (all fields) in the DB.
for(const order of orders.data) {
order.fields.Status = 'Approved';
order.fields.Amount += 1.5;
}
for(const order of orders.data) {
const updatedRecord = await doo.table.updateRecord('My Orders', order.id, order.fields);
}
// 3. Then we increase the ordered amount in every order again, but we update only the field "Amount", not other fields (partial update).
for(const order of orders.data) {
const updatedRecord = await doo.table.updateFields('My Orders', order.id, {
Amount: order.fields.Amount + 2.7
});
}
// 4. Then we insert a new order for a specific customer.
const insertedOrder = await doo.table.createRecord('My Orders', {
Customer: 'john@smith.com',
Date: new Date(),
Status: 'New',
Amount: 30
});
// 5. Then we delete the oldest order with the status "Canceled" for a specific customer - only when the number of Canceled orders is greater than 10.
const count = await doo.table.getCount('My Orders', {
filter: 'Customer(eq)john@smith.com,Status(eq)Canceled'
});
if (count.data.count > 10) {
const ordersForDelete = await doo.table.getData('My Orders', {
filter: 'Customer(eq)john@smith.com,Status(eq)Canceled',
sort: 'Date',
limit: 1
});
if (ordersForDelete.data.length) {
await doo.table.deleteRecord('My Orders', ordersForDelete.data[0].id);
}
}
In this example, the table name "My Orders" is used instead of the "My Orders" table ID. Exactly one table in this application is expected to be named "My Orders".
Alternatively, you can set and use an internal table name, or you can use a table ID (you can see the ID in the browser's developer tools when you load data from this table in Tabidoo).