doo.table

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, in 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 and filters 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 ;)

Functions

You can use any function from the doo.table namespace to manipulate table data:

Get Data

async getData(tableNameOrId, options?, applicationId?)    
Load a list of data from the specified table according to filter, paging, sort, ...

Get Count of Data

async getCount(tableNameOrId, options?, applicationId?)    
Get the number of data in the specified table according to the filter.

Get Data Summary

async getDataSummary(tableNameOrId, options, applicationId?)    
Get summary data from a table with filter conditions and required aggregations (min/max/sum/avg for numbers and min/max for date-time).
 

Get Record

async getRecord(tableNameOrId, recordId, applicationId?)    
Load one record by its ID from the specified table.

Get Linked Records (v2)

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).

Get Linked Records

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).

Get Parameter

async getParameter(tableNameOrId, fieldName, applicationId?)    
Get the value from the first row in the table.

Get Table Structure

async getTableStructure(tableNameOrId, applicationId?)    
Get one table of  the application.

Create Record

async createRecord(tableNameOrId, fields, options?)    
Creates a new record in a specific Tabidoo table. 
This function supports UPSERT mode - see the useUpsert parameter.

Update Fields

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.

Update Record

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.

Delete Record

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.)

Create Records (Bulk)

async createRecordsBulk(tableNameOrId, fieldsArray, options?)    
Creates/Inserts multiple records in a specific Tabidoo table.
This function supports UPSERT mode - see the useUpsert parameter.

Update Records (Bulk)

async updateRecordsBulk(tableNameOrId, bulkArray, options?)   
Updates records 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.
• WARN - If the ID is empty or the record is not found in the table, the record is ignored/skipped without reporting.

Update Full Records (Bulk)

async updateFullRecordsBulk(tableNameOrId, bulkArray, options?)   
Updates records 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.
• WARN - If the ID is empty or the record is not found in the table, the record is ignored/skipped without reporting.

Delete Records (Bulk)

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.)

Get File (as text)

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.

Get File (as Base64)

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.

Get File (as ArrayBuffer)

async getFileArrayBuffer(tableNameOrId, fileId, applicationId?)    
Load one attachment file by its ID from the specified table - returns ArrayBuffer.

Get Thumbnail (as ArrayBuffer)

async getThumbnail(tableNameOrId, fileId, applicationId?)    
Load a thumbnail of one attachment file by its ID from the specified table - returns ArrayBuffer.

Get Thumbnail (as Base64)

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.

Reload User Data

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.

Parameters

Explanation of the function parameters.

tableNameOrId

You 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).

applicationId

If 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/...).

recordId

Unique 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

fields

Set 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
	loadFields: [ 'code', 'description' ]				 // load only some columns
	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 parameter for getDataSummary functions. 
Only number and date/date-time fields are supported. 
Aggregations min/max/sum/avg are supported for numbers. 
Aggregations min/max are supported for date/date-time.

Example #1

{
	"filter": [
    	{ 
        	"field": "age",
            "operator": "gte",
            "value": "30"
        }   
    ],
    "fields": {
    	"age": { aggregations: ["min", "max", "sum", "avg"] },
        "dateOfBirth": { aggregations: ["min", "max"] },
        "someNumber": { aggregations: ["avg"] },
        "dateOfEmployment": { aggregations: ["min"] }
    }
}

Example #2 - using the currently set user data filter in the current table

{
	"filter": doo.environment.currentApplication.currentTable.dataConditions,
    "fields": {
    	"age": { aggregations: ["avg"] }
    }
}

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
}

fieldsArray

Parameter 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"
	}
]

bulkArray

Parameter for updateRecordsBulk and updateFullRecordsBulk function.

Example - Update two records:

[
    {
      "id": "158cb6ef-02a6-47dc-8134-188c1b6426e6",
      "fields": {
        "firstname": "Jack",
        "surname": "Newman"
      }
    },
    {
      "id": "2f64141a-ec9f-43df-a50d-1ca8eb277df7",
      "fields": {
        "firstname": "Peter",
        "surname": "Novak"
      }
    }
]

fileId

Unique file identification (ID). Parameter for getFile and getThumbnail functions. 

fieldName

The name of the field in the table.

reloadUserDataAfterAction

User 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.

useUpsert

Tabidoo API and doo.table CREATE/UPDATE functions (including bulk functions) support two upsert modes:

• The CREATE functions (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.
   
• The UPDATE functions (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.

dataResponseType

The 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.

• "All" - the response body contains all fields of the record (default for simple Create and Update functions, not allowed for bulk functions).
• "MetadataOnly" - only metadata is returned in the response body (record ID(s), created/modified time, ...).
• "None" - no data is returned in the response body (default for bulk operations).

filter

You 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.

Return values

• Example of return value for 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
			}
		}
	]
}
                

• Example of return value for getCount function.

{
	"data": {
		"count": 3
	}
}       

• Example of return value for getDataSummary function.

{ 
	"data": {
    	"count": 3,
        "fields": {
        	"age": {
            	"min": 31,
                "max": 44,
                "sum": 111,
                "avg": 37,
            },
            "dateOfBirth": {
            	"min": "1980-05-23T00:00:00Z",
                "max": "1994-07-03T00:00:00Z",
            },
            "someNumber": {
            	"avg": 24
            },
            "dateOfEmployment": {
            	"avg": "2002-11-11T12:34:56Z"
            }
        }
    }
}    

• Example of return value for 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
		}
	}
}             

• Example of return value for createRecordsBulk, updateRecordsBulk and updateFullRecordsBulk functions.
  This example shows that three records were sent for bulk update, two recorde were updated and one record was not updated (it was unchanged or it does not exist).
  In UPSERT mode, some records can be inserted and some can be updated.
   

{
	"bulk": {
		"successCount": 3,
		"insertedCount": 0,
		"updatedCount": 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"
 		}
	]
}
                

Handle Errors

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.

Debugging tips

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.

Example

1. In this example, we load all orders with the status "Tentative" for a specific customer.
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.
3. Then we increase the ordered amount in every order again, but we update only the field "Amount", not other fields (partial update).
4. Then we insert a new order for a specific customer.
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.

// 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).