doo.table

How to manipulate data in tables in Tabidoo scripts.

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

You can use one of the functions in the doo.table namespace to manipulate with table's data:

Functions

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

async getCount(tableNameOrId, options, application?)
get the number of data in the specified table according to the filter

async getParameter(tableNameOrId, fieldName, application?)
get the value from the first row in the table

async getRecord(tableNameOrId, recordId, application?)
load one record by its ID from the specified table

async getLinkedRecords(tableNameOrId, recordId, linkTableNameOrId, linkFieldName, application?)
get all records from the linked table which are linked to a record in another table
description of parameters is in the Parameters section below

async createRecord(tableNameOrId, fields, application?)
create (insert) one record into the specified table

async updateFields(tableNameOrId, recordId, fields, application?)
update only particular fields in a record by its ID in the specified table
(partial update - properties missing in the fields parameter object won't be updated, they will keep their original value)

async updateRecord(tableNameOrId, recordId, fields, application?)
update a whole record by its ID in the specified table
(properties missing in the fields parameter object will be updated to the null/empty value)

async deleteRecord(tableNameOrId, recordId, application?)
delete a record by its ID in the specified table
(be careful, it is an irreversible change)

async createRecordsBulk(tableNameOrId, fieldsArray, dataResponseType?, application?)
create (insert) multiple (array) records into the specified table

async deleteRecordsBulk(tableNameOrId, limit, filter, application?)
delete multiple records (max count of records limited by limit parameter - required) by specified filter (required) in the specified table
(be careful, it is an irreversible change)

async getFile(tableNameOrId, fileId, application?)
load one attachment file by its ID from the specified table

async getThumbnail(tableNameOrId, fileId, application?)
load thumbnail of one attachment file by its ID from the specified table

reloadUserData(tableNameOrId)
reload user data in all data views (grid/cards/calendar/...) 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, deleteRecordsBulk

You can learn more about these functions in Tabidoo API documentation here.

All functions have parameter 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 (it is unique within the application), or you can use a table ID (you can see the ID in the URL in the browser's address bar when you use this table in Tabidoo).

All functions have the optional parameter 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/schema/...).

All functions except reloadUserData are asynchronous and you must use the await keyword to wait until they finished and get a result or catch an error.


Parameters

  • Parameter options example for getData and getCount
{ 
    filter: "surname(in)(Fox;Dog)",  // 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)
}

Find all records where the field surname contains the string "Fox" or "Dog".

or

{
    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)
}

Find all records where field age equals 25 and field firstname equals Jack or Peter.*

All options are optional and have a default value if they are not provided.

  • Parameters for getLinkedRecords
    This function can be used e.g. for loading all invoice-rows linked to invoice-header.
    You need to know invoice-rows table name, invoice-header table name, name of a link item in invoice-header table linked to invoice-row, and invoice header record ID.
    • tableNameOrId - table from which the data is loaded (e.g. invoice-rows table)
    • recordId - ID of the parent record in a linked table (e.g. ID of invoice header record in invoice-header table)
    • linkTableNameOrId - linked table containing parent record (e.g. invoice-header table)
    • linkFieldName - the name of the link field in a linked table (e.g. field 'linkToRows' in invoice-header table)
  • Parameter fields example for createRecord and updateRecord
    Put in all the fields of the data record which should have some value.
{
  "firstname": "Jack",
  "surname": "Newman"
}
  • Parameter fields example for updateFields
    Put in only the fields you want to update, the other fields will remain unchanged after an update.
{
  "firstname": "Jack"
}
  • Parameter fieldsArray example for createRecordsBulk
[
  {
    "firstname": "Jack",
    "surname": "Newman"
  },
  {
    "firstname": "Peter",
    "surname": "Novak"
  }
]
  • Optional parameter dataResponseType for createRecordsBulk
    "None" - (default) no data are returned
    "MetadataOnly" - Only metadata are returned
  • Parameter filter example for deleteRecordsBulk
"surname(in)(Fox;Dog)"  // data filter in simple string form *

Find all records where the field surname contains the string "Fox" or "Dog".

It does not support an advanced filter.

You can learn more about filters in Tabidoo API documentation here.


Return values

  • RETURN VALUE example for getData and getLinkedRecords
{
  "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
      }
    }
  ]
}
  • RETURN VALUE example for getCount
{
  "data": {
    "count": 3
  }
}
  • RETURN VALUE example for getRecord, createRecord, updateFields and updateRecord
{
  "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
    }
  }
}
  • RETURN VALUE example for createRecordsBulk
{
  "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"
    }
  ]
}

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 URL in the browser's address bar when you use this table in Tabidoo).