In Tabidoo Javascript, you can manipulate data in tables in yours 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 linked table which are linked to record in another table

  • 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 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 field surname contains 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.

  • 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 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
    "All" - all data are returned
  • Parameter filter example for deleteRecordsBulk
"surname(in)(Fox;Dog)"  // data filter in simple string form *

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

It does not support 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 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);
}

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


Tip

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 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)[email protected],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: '[email protected]',
  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)[email protected],Status(eq)Canceled' 
});
if (count.data.count > 10) {
  const ordersForDelete = await doo.table.getData('My Orders', { 
    filter: 'Customer(eq)[email protected],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).