informat.table Data Table Operations

Overview

Use the informat.table object for data table-related operations

Tip

Update, insert, and delete operations on data tables in scripts will not trigger listeners

Query Usage Examples

const query = {
  // Page number, starting from 1. Default is 1
  pageIndex: 1,
  // Number of records per page. Default is 100, -1 means query all data
  pageSize: 10,
  // Filter TableRecordFilter
  filter: {
    // Filter conditions TableRecordCondition
    conditionList: [
      {
        // Query field identifier
        fieldId: "name",
        /**
         * Comparison method TableRecordConditionOpt, values are as follows
         * eq                Equal
         * ne                Not equal
         * gt                Greater than
         * ge                Greater than or equal to
         * lt                Less than
         * le                Less than or equal to
         * contains          Contains
         * notcontains       Does not contain
         * startswith        Starts with
         * endswith          Ends with
         * isnull            Is null
         * isnotnull         Is not null
         * in                In list
         * notin             Not in list
         * between           Between
         * notbetween        Not between
         * parenteq          Parent object equals
         * parentrooteq      Root node equals
         * parentcontains    Parent object contains
         * multipleandquery  Multiple value AND fuzzy query
         * multipleorquery   Multiple value OR fuzzy query
         * intree            In tree
         */
        opt: "eq",
        /**
         * Processing function for data values TableRecordConditionFunc
         * Date type field values:
         * week                 Day of the week
         * month                Month of the year
         * quarter              Quarter of the year
         * year                 Year
         * dayofyear            Day of the year
         * weekofyear           Week of the year
         * dayofmonth           Day of the month
         * daytonow             Days from today
         * weektonow            Weeks from today
         * monthtonow           Months from today
         * quartertonow         Quarters from today
         * yeartonow            Years from today
         * fmtday               Year-month-day format:2021-01-01
         * fmtweek              Year-week format:2021-1
         * fmtmonth             Year-month format:2021-01
         * fmtquarter           Year-quarter format:2021-1
         * Member type field values:
         * nameOfAccount        Account name
         */
        func: null,
        // Comparison value
        value: "mr.zhang",
      },
      getTableInfo,
    ],
    // Nested sub-filters, value is an array of TableRecordFilter, supports nested structure layer by layer
    children: [],
    /**
     * Combination method of sub-filters and upper-level filters
     * Values are as follows
     * and      Match all
     * or       Match any
     */
    opt: "and",
  },
  // List of field identifiers included in the return value, query all fields if not passed or empty array
  includeFields: ["id", "name", "age"],
  // List of grouping field identifiers
  groupByList: [],
  // Aggregation query list TableRecordAggregationQuery
  aggregationQueryList: [
    {
      // Aggregated field
      fieldId: "sex",
      /**
       * Aggregation method
       * Values are as follows
       * count        Count
       * avg          Average (number, date type)
       * sum          Sum (number, date type)
       * max          Maximum value (number, date type)
       * min          Minimum value (number, date type)
       */
      func: "count",
      // Whether to deduplicate
      distinct: false,
    },
  ],
  // Sorting method array, OrderBy
  orderByList: [
    {
      // Sort field identifier
      field: "name",
      /**
       * Sorting method values:
       * asc      Ascending order
       * desc     Descending order
       */
      type: "desc",
    },
  ],
  // List of deduplicated field identifiers
  distinctFieldList: [],
  // Return value exclude field identifier list, query all fields if not passed or empty array
  excludeFields: [],
  // Whether to return option names, returns option names for `List Selection`, `Cascading Selection`, `Tree Selection`
  returnOptionName: false,
};

Notes

Difference between contains and in in TableRecordConditionOpt

For example: The task table has a responsible person field (identifier: owner, type: user selection, multiple selection), with the following records

ID	Responsible Person
1	Zhang San, Li Si
2	Zhang San
3	Wang Wu

• Contains: The right member list contains any member from the left

  • { fieldId: 'owner', opt: 'contains', value: ['Zhang San'] } Records 1, 2 satisfy the condition

• In list: The right member list contains all members from the left

  • { fieldId: 'owner', opt: 'in', value: ['Zhang San'] } Only record 2 satisfies the condition
  • { fieldId: 'owner', opt: 'in', value: ['Zhang San','Li Si'] } Records 1, 2 satisfy the condition

queryList

Query multiple data records by conditions

informat.table.queryList(tableId, query);

Parameter	Type	Description
tableId	String	Data table identifier
query	TableRecordQuery	Query conditions

Return Value

List of data records, type: Array<Object>

Query the first 3 employees whose names start with "zhang" (Zhang) or "li" (Li) and are over 20 years old, sorted by age from lowest to highest

informat.table.queryList("staffs", {
  pageIndex: 1,
  pageSize: 3,
  filter: {
    opt: "and",
    conditionList: [{ fieldId: "age", opt: "gt", value: "20" }],
    children: [
      {
        opt: "or",
        conditionList: [
          { fieldId: "name", opt: "startswith", value: "zhang" },
          { fieldId: "name", opt: "startswith", value: "li" },
        ],
      },
    ],
  },
  orderByList: [{ field: "age", type: "asc" }],
});

The above call is equivalent to SQL:

where age>20 and (name like 'zhang%' or name like 'li%')

Query the list of employees located in Nanshan District, Shenzhen City

informat.table.queryList("staffs", {
  pageIndex: 1,
  pageSize: 3,
  filter: {
    opt: "and",
    conditionList: [{ fieldId: "area", opt: "eq", value: ["guangdong", "shenzhen", "nanshan"] }],
  },
});

Child Object - Query all sub-department lists of the Technology Department

informat.table.queryList("scriptTableB", {
  pageSize: -1,
  filter: {
    opt: "and",
    conditionList: [{ fieldId: "children", opt: "parentrooteq", value: ["rpev2ztqnbu8o"] }],
  },
});

Return data as follows:

[
  {
    "children": "o32bbe3eqrrg0.zzzq65nxu0qv1",
    "id": "c91au1arsncd6"
  },
  {
    "children": "o32bbe3eqrrg0.zzzq65nxu0qv1",
    "id": "z00ei9fqymbra"
  },
  {
    "children": "o32bbe3eqrrg0.zzzq65nxu0qv1.z00ei9fqymbra",
    "id": "ci92j4ddenf9o"
  }
]

Pagination query of employee list, returning only name and gender fields, and returning gender option names *

informat.table.queryList("staffs", {
  pageIndex: 1, // Starting from 1
  pageSize: 10,
  returnOptionName: true,
  includeFields: ["name", "sex"],
});

Pagination query of employee list, returning fields excluding name and gender *

informat.table.queryList("staffs", {
  pageIndex: 1, // Starting from 1
  pageSize: 10,
  excludeFields: ["name", "sex"],
});

Aggregation query grouped by age

informat.table.queryList("staffs", {
  groupByList: ["age"],
  /*
   * Date aggregation query
   * groupByList usage example: groupByList: ['dataTime$year'],
   * dataTime: Aggregation field
   * $: Aggregation method separator
   * year: Aggregation method, year: year, month of year: month, day of week: week
   * Please refer to TableRecordQuery description
   */
  aggregationQueryList: [
    {
      func: "count",
      fieldId: "age",
    },
  ],
  orderByList: [{ field: "age", type: "asc" }],
});

Return data as follows

[
  {
    "age": 23,
    "age_count": 2
  },
  {
    "age": 24,
    "age_count": 1
  },
  {
    "age": 25,
    "age_count": 1
  }
]

Aggregation query grouped by birth year

informat.table.queryList("staffs", {
  groupByList: ["birthday$year"],
  aggregationQueryList: [
    {
      func: "count",
    },
  ],
  orderByList: [{ field: "birthday$year", type: "asc" }],
});

Return data as follows

[
  {
    "birthday$year": "1998",
    "count": 2
  },
  {
    "birthday$year": "1999",
    "count": 1
  },
  {
    "birthday$year": "2000",
    "count": 1
  }
]

queryListCount

Query the count of data that meets the conditions

informat.table.queryListCount(tableId, filter);

Parameter	Type	Description
tableId	String	Data table identifier
filter	TableRecordFilter	Query conditions

Return Value

The number of records that meet the conditions, type: Integer

Example

js

informat.table.queryListCount("staffs", {
  conditionList: [{ fieldId: "name", opt: "contains", value: "zhang" }],
});

json

2

getTableInfo

Query data table information

informat.table.getTableInfo(tableId);

Parameter	Type	Description
tableId	String	Data table identifier

Return Value

Type: TableInfo, returns data table information

Example

js

informat.table.getTableInfo("task");

json

{
  "table": {
    "id": "cfg9ywr41G",
    "key": "task",
    "name": "task"
  },
  "tableFieldList": [
    {
      "id": "id",
      "key": "id",
      "name": "id",
      "type": "UUID"
    },
    {
      "id": "mzqlwrDgOM",
      "key": "expectDate",
      "name": "expectDate",
      "type": "Date"
    },
    {
      "id": "tp0rfnbmcc3sd",
      "key": "updateTime",
      "name": "updateTime",
      "type": "LastModifyTime"
    },
    {
      "id": "u3qh6zf0lgwus",
      "key": "updateUser",
      "name": "updateUser",
      "type": "LastModifyUser"
    },
    {
      "id": "Wfgb3bwKdf",
      "key": "name",
      "name": "name",
      "type": "SingleText"
    }
  ]
}

getTableFieldInfo

Query data table field information

informat.table.getTableFieldInfo(tableId, fieldId);

Parameter	Type	Description
tableId	String	Data table identifier
fieldId	String	Data table field identifier

Return Value

Type: TableFieldInfo, returns data table field information

Example

js

informat.table.getTableFieldInfo("task", "name");

json

{
  "changeLogAccessRoleList": [],
  "defaultValueRuleList": [],
  "displayWidth": 50,
  "enableChangeLog": true,
  "hidden": false,
  "icon": "text",
  "id": "Wfgb3bwKdf",
  "key": "name",
  "name": "name",
  "readonly": false,
  "singleTextSetting": {
    "autoCompleteAutomaticVarList": [],
    "autoCompleteTriggerOnFocus": false,
    "buttonList": [],
    "enableAutoComplete": false,
    "format": "string",
    "max": 200,
    "min": 0,
    "nullable": true,
    "storageSize": 200
  },
  "tableId": "task",
  "type": "SingleText",
  "validateRuleList": []
}

queryById

Query a single data record by ID

informat.table.queryById(tableId, recordId);

Parameter	Type	Description
tableId	String	Data table identifier
recordId	String	ID of the data record to query

Return Value

The queried data record, type: Object

Example

js

informat.table.queryById("task", "aorxux52i4t1z");

json

{
  "id": "aorxux52i4t1z",
  "name": "task1",
  "startDate": "2022-12-05 00:00:00.0"
}

queryOne

Query the first record that meets the conditions

informat.table.queryOne(tableId, query);

Parameter	Type	Description
tableId	String	Data table identifier
query	TableRecordQuery	Query conditions

Return Value

The queried data record, type: Object

Example

js

informat.table.queryOne("task", {
  pageSize: 1,
  filter: {
    conditionList: [{ fieldId: "name", opt: "eq", value: "task1" }],
  },
});

json

{
  "id": "aorxux52i4t1z",
  "name": "task1",
  "startDate": "2022-12-05 00:00:00.0"
}

query

Query a single record by ID and conditions

informat.table.query(tableId, recordId, setting);

Parameter	Type	Description
tableId	String	Data table identifier
recordId	String	ID of the record to query
setting	TableRecordQuerySetting	Query settings

The structure of TableRecordQuerySetting is as follows

Parameter	Type	Description
forUpdate	Boolean	Whether to lock the row
returnOptionName	Boolean	If there are List Selection, Cascading Selection, or Tree Selection fields, returns option names
includeFields	Array<String>	List of fields to return, returns all fields if empty

Return Value

The queried data record, type: Object

Example: Querying a record with row lock

js

informat.table.query("task", "aorxux52i4t1z", {
  forUpdate: true,
});

json

{
  "id": "aorxux52i4t1z",
  "name": "task1",
  "startDate": "2022-12-05 00:00:00.0"
}

insert

Create a record

For the four fields Creator, Creation Time, Last Modifier, and Last Modification Time, if they are not provided in the record to be created, the system will set the current time as the values for Creation Time and Last Modification Time, and set the current operator as the values for Creator and Last Modifier. If the record creation is triggered by a scheduled task, the current operator is INFORMAT.

informat.table.insert(tableId, data);

Parameter	Type	Description
tableId	String	Data table identifier
data	Object	Record to be created

Return Value

Returns the ID of the created record

Example

js

informat.table.insert("staffs", {
  name: "mr. zhao",
  sex: "male",
  age: 30,
  grade: 5,
  area: ["guangdong", "shenzhen", "nanshan"],
});

json

"twmvau4ogujcc"

For detailed formatting, see: Record Type Conversion

insertEx

Create a record with configuration information Similar to Create a record, the only difference is that it has configuration information

informat.table.insertEx(tableId, data, config);

Parameter	Type	Description
tableId	String	Data table identifier
data	Object	Record to be created
config	InsertRecordConfig	Configuration info

The structure of InsertRecordConfig is as follows:

Parameter	Type	Description
disableCalculateRollupField	Boolean	Whether to disable rollup field calculation, default false

When creating a new record, if the table it belongs to is a child table of a main table, and some fields are rollup fields in the main table's lookup rollup or relation list rollup, the system will automatically calculate these rollup fields in the main table. For example, when creating a large number of child table records at once, it is actually unnecessary to recalculate the rollup fields in the main table every time a child table record is created, as the time required for calculating rollups is relatively long. Therefore, it is sufficient to recalculate the rollups only after the last child table record is created.

To improve efficiency and reduce unnecessary calculations, you can set the configuration parameter disableCalculateRollupField in InsertRecordConfig to true in such cases. This way, the system will not calculate rollup fields every time a child table record is created. After all child table records are created, you can then separately call informat.table.refreshRelationRollup and informat.table.refreshLookupRollup to calculate the rollups.

Return Value

ID of the created record

js

informat.table.insertEx(
  "staffs",
  {
    name: "mr. zhao",
    sex: "male",
    age: 30,
    grade: 5,
    area: ["guangdong", "shenzhen", "fukui"],
  },
  {
    disableCalculateRollupField: true,
  }
);

json

"ecxnsomw69dmn"

batchInsert

Batch create records

For the four fields Creator, Creation Time, Last Modifier, and Last Modification Time, if they are not provided in the records to be created, the system will set the current time as the values for Creation Time and Last Modification Time, and set the current operator as the values for Creator and Last Modifier. If the record creation is triggered by a scheduled task, the current operator is INFORMAT.

informat.table.batchInsert(tableId, dataList);

Parameter	Type	Description
tableId	String	Data table identifier
dataList	Array	List of records to be created

Return Value

Number of records created

js

informat.table.batchInsert("staffs", [
  { name: "mr. zhao", sex: "male", age: 30, grade: 5, area: ["guangdong", "shenzhen", "fukui"] },
  { name: "mr. sun", sex: "female", age: 27, grade: 4, area: ["guangdong", "shenzhen", "baoan"] },
]);

json

2

js

informat.table.batchInsert("staffs", [
  { id: "1", name: "mr. zhao", sex: "male", age: 30, grade: 5, area: ["guangdong", "shenzhen", "fukui"] },
  { id: "2", name: "mr. sun", sex: "female", age: 27, grade: 4, area: ["guangdong", "shenzhen", "baoan"] },
]);

json

2

Notes

Differences between batch inserting records and normal single record insertion:

The fields Child Object Number, Number, Lookup Rollup, and Relation List Rollup are not calculated during batch insertion of records.

update

Update a record For the two fields Last Modifier and Last Modification Time, if they are not provided in the record to be updated, the system will set the current time as the value for Last Modification Time, and set the current operator as the value for Last Modifier. If the record update is triggered by a scheduled task, the current operator is INFORMAT.

informat.table.update(tableId, data);

Parameter	Type	Description
tableId	String	Data table identifier
data	Object	Record to be updated

Return Value

Number of successfully updated records

Example 1

js

informat.table.update("staffs", {
  id: "1",
  name: "mr. zhao",
  sex: "male",
  age: 28,
  grade: 2,
  area: ["guangdong", "shenzhen", "fukui"],
});

json

1

For detailed formatting, see: Field Type Conversion

updateEx

Update a record with configuration information Similar to Update a record, the only difference is that it has update configuration information

informat.table.updateEx(tableId, data, config);

Parameter	Type	Description
tableId	String	Data table identifier
data	Object	Record to be updated
config	UpdateRecordConfig	Update configuration

The structure of UpdateRecordConfig is as follows:

Parameter	Type	Description
enableChangeLog	Boolean	Whether to enable change log, default false
disableCalculateRollupField	Boolean	Whether to disable rollup field calculation, default false

When updating a record, if the table it belongs to is a child table of a main table, and the updated field is a rollup field in the main table's lookup rollup or relation list rollup, the system will automatically calculate these rollup fields in the main table. For example, when updating a large number of child table records at once, it is actually unnecessary to recalculate the rollup fields in the main table every time a child table record is updated, as the time required for calculating rollups is relatively long. Therefore, it is sufficient to recalculate the rollups only after the last child table record is updated.

To improve efficiency and reduce unnecessary calculations, you can set the configuration parameter disableCalculateRollupField in UpdateRecordConfig to true in such cases. This way, the system will not calculate rollup fields every time a child table record is updated. After all child table records are updated, you can then separately call informat.table.refreshRelationRollup and informat.table.refreshLookupRollup to calculate the rollups.

Example 1

js

informat.table.updateEx(
  "staffs",
  {
    id: "1",
    name: "mr. zhao",
    sex: "male",
    age: 28,
    grade: 2,
    area: ["guangdong", "shenzhen", "fukui"],
  },
  {
    enableChangeLog: true,
    disableCalculateRollupField: true,
  }
);

json

1

Return Value

Number of successfully updated records

batchUpdate

Batch update records For the two fields Last Modifier and Last Modification Time, if they are not provided in the records to be updated, the system will set the current time as the value for Last Modification Time, and set the current operator as the value for Last Modifier. If the record update is triggered by a scheduled task, the current operator is INFORMAT. Note: The list of fields to be updated must be consistent across all records in the update list. If the value is null, it will be updated to null in batch.

informat.table.batchUpdate(tableId, dataList);

Parameter	Type	Description
tableId	String	Data table identifier
dataList	Object	List of records to update

Example

js

informat.table.batchUpdate("staffs", [
  { id: "1", name: "mr.zhao", sex: "male", age: 29, grade: 3, area: ["guangdong", "shenzhen", "nanshan"] },
  { id: "2", name: "mr.sun", sex: "female", age: 26, grade: 2, area: ["guangdong", "shenzhen", "baoan"] },
]);

json

3

Notes

Differences between batch updating records and normal single record updating:

The fields Child Object Number, Number, Lookup Rollup, and Relation List Rollup are not calculated during batch update of records.

updateList

Update multiple records according to conditions

informat.table.updateList(tableId, filter, data);

Parameter	Type	Description
tableId	String	Data table identifier
filter	Filter	Filter conditions
data	Object	Data to be updated

Return Value

Number of successfully updated records

Example: Update all employees with the last name "zhang" to level 4

js

informat.table.updateList(
  "staffs",
  {
    conditionList: [{ fieldId: "name", opt: "contains", value: "zhang%" }],
  },
  { grade: 4 }
);

json

2

updateListEx

Update multiple records according to conditions and configuration

informat.table.updateListEx(tableId, filter, data, config);

Parameter	Type	Description
tableId	String	Data table identifier
filter	Filter	Filter conditions
data	Object	Data to be updated
config	UpdateRecordConfig	Update configuration

The structure of UpdateRecordConfig is as follows:

Parameter	Type	Description
enableChangeLog	Boolean	Whether to enable change log, default false
disableCalculateRollupField	Boolean	Whether to disable rollup field calculation, default false

Return Value

Number of successfully updated records

Example:

js

informat.table.updateListEx(
  "staffs",
  {
    conditionList: [{ fieldId: "name", opt: "contains", value: "zhang%" }],
  },
  { grade: 4 },
  { disableCalculateRollupField: true }
);

json

2

delete

If the recycle bin is enabled, deleted records will be moved to the recycle bin. Delete a single record

informat.table.delete(tableId, recordId);

Parameter	Type	Description
tableId	String	Data table identifier
recordId	String	ID of the record to delete

Return Value

Number of successfully deleted records

Example:

js

informat.table.delete("staffs", "twmvau4ogujcc");

json

1

deleteEx

Delete a record with configuration information Similar to delete a record, the only difference is that it has configuration information

informat.table.deleteEx(tableId, recordId, config);

Parameter	Type	Description
tableId	String	Data table identifier
recordId	String	ID of the record to delete
config	DeleteRecordConfig	Configuration information

The structure of DeleteRecordConfig is as follows:

Parameter	Type	Description
disableCalculateRollupField	Boolean	Whether to disable rollup field calculation, default false

When deleting a record, if the table it belongs to is a child table of a main table, and some fields are rollup fields in the main table's lookup rollup or relation list rollup, the system will automatically calculate these rollup fields in the main table. For example, when deleting a large number of child table records at once, it is actually unnecessary to recalculate the rollup fields in the main table every time a child table record is deleted, as the time required for calculating rollups is relatively long. Therefore, it is sufficient to recalculate the rollups only after the last child table record is deleted. To improve efficiency and reduce unnecessary calculations, you can set the configuration parameter disableCalculateRollupField in DeleteRecordConfig to true in such cases. This way, the system will not calculate rollup fields every time a child table record is deleted. After all child table records are deleted, you can then separately call informat.table.refreshRelationRollup and informat.table.refreshLookupRollup to calculate the rollups.

Return Value

Number of successfully deleted records

deleteList

If the recycle bin is enabled, deleted records will be moved to the recycle bin. Delete multiple records according to conditions

informat.table.deleteList(tableId, filter);

Parameter	Type	Description
tableId	String	Data table identifier
filter	Filter	Filter conditions

Return Value

Number of successfully deleted records

Example: Delete the list of resigned employees

js

informat.table.deleteList("staffs", {
  conditionList: [{ fieldId: "status", opt: "ge", value: "dimission" }],
});

json

2

TIP

The underlying implementation logic of informat.table.deleteList is to first query all records and then delete them one by one. If the recycle bin is configured, the records will be moved to the recycle bin after deletion, so the speed is not fast. When batch deleting large amounts of data and faster speed is required, please use informat.table.batchDelete

deleteListEx

If the recycle bin is enabled, deleted records will be moved to the recycle bin. Delete multiple records according to conditions and configuration

informat.table.deleteListEx(tableId, filter, config);

Parameter	Type	Description
tableId	String	Data table identifier
filter	Filter	Filter conditions
config	DeleteRecordConfig	Configuration info

Return Value

Number of successfully deleted records

Example:

js

informat.table.deleteListEx(
  "user",
  {
    conditionList: [{ fieldId: "id", opt: "in", value: ["k3oybvxo0lwxv", "htfesu6ghbcro"] }],
  },
  {
    disableCalculateRollupField: true,
  }
);

json

2

batchDelete

Batch delete records If the recycle bin is enabled, deleted records will not be moved to the recycle bin.

informat.table.batchDelete(tableId, idList);

Parameter	Type	Description
tableId	String	Data table identifier
idList	Array	List of record IDs

Example

js

informat.table.batchDelete("staffs", ["uphuksrz88xij", "cyljta5v60mup"]);

json

2

queryRelationList

Query the record list of the child table corresponding to the relation list field through the main table record ID

informat.table.queryRelationList(tableId, relationFieldId, recordId, query);

Parameter	Type	Description
tableId	String	Data table identifier
relationFieldId	String	Relation list field ID
recordId	String	Main table record ID
query	Query	Child table query criteria

Return Value

List of data records, type is Array<Object>

Example

informat.table.queryRelationList("staffs", "rewardList", "yhg8b23ej2gt6", {
  pageIndex: 1,
  pageSize: 50,
  filter: {
    conditionList: [{ fieldId: "name", opt: "contains", value: "excellent employee" }],
  },
});

addRelation

Add relation list value

informat.table.addRelation(tableId, relationFieldId, recordId, relationRecordId);

Parameter	Type	Description
tableId	String	Data table identifier
relationFieldId	String	Relation list field
recordId	String	Main table record ID
relationRecordId	String	Relation list record ID

Example

informat.table.addRelation("staffs", "rewardList", "yhg8b23ej2gt6", "rgf4fbbb543kj");

deleteRelation

Delete relation list value

informat.table.deleteRelation(tableId, relationFieldId, recordId, relationRecordId);

Parameter	Type	Description
tableId	String	Data table identifier
relationFieldId	String	Relation list field
recordId	String	Main table record ID
relationRecordId	String	Relation list record ID

Example

informat.table.deleteRelation("staffs", "rewardList", "yhg8b23ej2gt6", "rgf4fbbb543kj");

hasRelation

Query whether records are in the relation list If the record ID specified in relationRecordIdList exists in the relation list, this record ID is also included in the returned array.

For example, if the data stored in the relation list is:

• record1
• record2
• record3

And relationRecordIdList is record1 record2 record4, then the return value is record1 record2

informat.table.hasRelation(tableId, relationFieldId, recordId, relationRecordIdList);

Parameter	Type	Description
tableId	String	Data table identifier
relationFieldId	String	Relation list field
recordId	String	Main table record ID
relationRecordIdList	Array<String>	List of relation record IDs

Return Value

List of record IDs that exist in the relationFieldId relation list of recordId, type is Array<String>

Example

informat.table.hasRelation("staffs", "rewardList", "yhg8b23ej2gt6", ["rgf4fbbb543kj", "ct82dszf20s2u"]);
// Return data as follows:
["rgf4fbbb543kj"];

getRelationRecordIdList

Query the record list that contains the specified record in the relation list Query data related to the main table through the record ID in the target table associated with the relation list field. Returns a list of main table record IDs that have associations.

For example, if the data stored in the data table is as follows:

Main table record ID	Relation list
m1	[record1,record2]
m2	[record1,record3]
m3	[record3,record4]

If the specified record is record3, the return value is m2 m3

informat.table.getRelationRecordIdList(tableId, relationFieldId, recordId);

Parameter	Type	Description
tableId	String	Main table data table identifier
relationFieldId	String	Relation list field ID of main table
recordId	String	Record ID of child table

Return Value

List of record IDs where recordId exists in the relationFieldId relation list, type is Array<String>

Example

informat.table.getRelationRecordIdList("staffs", "rewardList", "rgf4fbbb543kj");
// Return data as follows:
["yhg8b23ej2gt6"];

updateChildrenField

Update children field

Move the record under parentRecordId. If the current record has child nodes, the child nodes will be updated synchronously

informat.table.updateChildrenField(tableId, childrenFieldId, recordId, parentRecordId);

Parameter	Type	Description
tableId	String	Data table identifier
childrenFieldId	String	Children field
recordId	String	Record ID to be updated
parentRecordId	String	Parent node to move to. If parent node is null, the record will be moved to the root directory

Example

informat.table.updateChildrenField("task", "children", "z00ei9fqymbra", "zzzq65nxu0qv1");

queryChildrenList

Query the children record list of the specified record through conditions

informat.table.queryChildrenList(tableId, childrenFieldId, recordId, query);

Parameter	Type	Description
tableId	String	Data table identifier
childrenFieldId	String	Children field
recordId	String	Main table record ID
query	Query	Query conditions

Example

js

informat.table.queryChildrenList("staffsDept", "children", "rpev2ztqnbu8o", {
  pageIndex: 1,
  pageSize: 50,
});

json

[
  {
    "children": "rpev2ztqnbu8o",
    "createTime": 1717500998762,
    "name": "Technical Group 1",
    "id": "pz9y1fse8hccw",
    "seq": 5
  },
  {
    "children": "rpev2ztqnbu8o",
    "createTime": 1717501030793,
    "name": "Technical Group 2",
    "id": "l8w9u9raftp2a",
    "seq": 6
  }
]

cloneAttachment

Copy files from attachment field to target field Copy files from the attachment field in the source data table to the attachment field in the target table.

TIP

Note: attachment is an object, not an array. If the attachment field is multi-select, you need to use array index to select the corresponding attachment object

informat.table.cloneAttachment(sourceTableId, sourceFieldId, targetTableId, targetFieldId, attachment);

Parameter	Type	Description
sourceTableId	String	Source data table identifier
sourceFieldId	String	Source field identifier
targetTableId	String	Target data table identifier
targetFieldId	String	Target field identifier
attachment	TableAttachment	Attachment object

Return Value

Attachment object after successful copy, type is TableAttachment

Example

js

var record = informat.table.queryById("staffs", "yhg8b23ej2gt6");
console.log("record.attachment", record.attachment);
if (record.attachment == null) {
  return null;
}
var targetAttachments = [];
record.attachment.forEach((att) => {
  //checkbox
  var targetAttachment = informat.table.cloneAttachment("staffs", "attachment", "staffsDept", "attachment", att);
  targetAttachments.push(targetAttachment);
});
console.log("targetAttachments", targetAttachments);
informat.table.update("staffsDept", {
  id: "pz9y1fse8hccw",
  attachment: targetAttachments,
});

json

[
  {
    "id": "1d14a4c1080e4f5992052aeab13951df.jpg",
    "md5": "b394f37c3180e68090e2a0f1370f2aa9",
    "name": "test.jpg",
    "path": "uljrmxosme2uw/dwbndumn3dc8k/1d14a4c1080e4f5992052aeab13951df.jpg",
    "size": 7757,
    "thumbnail": "c270728528b54876abf67fb7e9ec4f24.jpg"
  }
]

moveAttachment

Move files from attachment field to target attachment field Move files from the attachment field in the source data table to the attachment field in the target table.

TIP

Note: attachment is an object, not an array. If the attachment field is multi-select, you need to use array index to select the corresponding attachment object

informat.table.moveAttachment(sourceTableId, sourceFieldId, targetTableId, targetFieldId, attachment);

Parameter	Type	Description
sourceTableId	String	Source data table identifier
sourceFieldId	String	Source field identifier
targetTableId	String	Target data table identifier
targetFieldId	String	Target field identifier
attachment	TableAttachment	Attachment object

Return Value

Attachment object after successful move, type is TableAttachment

Example

var record = informat.table.queryById("task", "cyrzgschwivz1");
informat.table.moveAttachment("task", "attachment", "task2", "attachment", record.attachment);

createAttachment

Generate attachment using files from local sandbox Upload files from local sandbox to shared storage and generate TableAttachment objects. If the file is an image type, the system will automatically generate a thumbnail; if there is a watermark setting in the attachment settings, the system will automatically generate a watermark.

informat.table.createAttachment(tableId, fieldId, path);

Parameter	Type	Description
tableId	String	Identifier of the data table
fieldId	String	Identifier of the attachment field
path	String	File path in the local sandbox

Return Value

The attachment field after successful creation, type is TableAttachment

Example

Download images from URL, save to local sandbox files, then use files from local sandbox to generate attachments and save to the attachment field of a record in the staff table

The staff table staffs has an attachment field

Field ID	Type	Description
attachment	Attachment	Multiple selection

const req = {
  url: "https://www.informat.cn/favicon.ico",
};
const rsp = informat.http.request(req);
// Save to local sandbox
rsp.saveBodyAsFile("tmp/demo.png");
var attachment = informat.table.createAttachment("staffs", "attachment", "tmp/demo.png");
// Update the attachment field of the data table record
informat.table.update("staffs", {
  id: "yhg8b23ej2gt6",
  attachment: [attachment], // Because the attachment field is multiple selection, use an array here; similarly, if it is not multiple selection, no need to use an array
});
// Delete local file
informat.file.delete("tmp/demo.png");

createAttachmentStorage

Generate attachment using files from remote storage Copy files from remote storage as values for attachment fields. If the file is an image type, no thumbnail will be generated, and the thumbnail value will be the same as the attachment value. This operation also does not generate watermarks.

informat.table.createAttachmentStorage(tableId, fieldId, path);

Parameter	Type	Description
tableId	String	Identifier of the data table
fieldId	String	Identifier of the attachment field
path	String	File path in remote storage

Return Value

The attachment field after successful creation, type is TableAttachment

Example

Download images from URL, save to shared storage, then use files from remote storage to generate attachments and save to the attachment field of a record in the staff table

The staff table staffs has an attachment field

Field ID	Type	Description
attachment	Attachment	Multiple selection

const req = {
  url: "https://www.informat.cn/favicon.ico",
};
const rsp = informat.http.request(req);
// Save to remote storage
rsp.saveBodyAsStorage("remote/demo.png");
var attachment = informat.table.createAttachmentStorage("staffs", "attachment", "remote/demo.png");
// Update the attachment field of the data table record
informat.table.update("staffs", {
  id: "lc44f3lfggnbb",
  attachment: [attachment], // Because the attachment field is multiple selection, use an array here; similarly, if it is not multiple selection, no need to use an array
});
// Delete local file
informat.file.delete("tmp/demo.png");

cloneTableSignature

Copy files from handwritten signature field to target field Copy files from the handwritten signature field in the source data table to the handwritten signature field in the target table.

informat.table.cloneTableSignature(sourceTableId, sourceFieldId, targetTableId, targetFieldId, signature);

Parameter	Type	Description
sourceTableId	String	Identifier of the source data table
sourceFieldId	String	Identifier of the source field
targetTableId	String	Identifier of the target data table
targetFieldId	String	Identifier of the target field
signature	TableSignature	Handwritten signature object

Return Value

Handwritten signature after successful copying, type is TableSignature

Example

Automatically upload handwritten signatures to a custom form, then copy the handwritten signature attachments from the custom form to the handwritten signature field of a record in the staff table staffs.

Custom form data table identifier: $Automatic_b0qt6ci821n2m_nuxfjrga56kj Custom form data table has a handwritten signature field (single selection) Staff table staffs has a handwritten signature field (single selection)

Field ID	Type	Description
signature	Handwritten	Single selection

let form = automatic.getVar("form");
let signature = informat.table.cloneTableSignature("$Automatic_b0qt6ci821n2m_nuxfjrga56kj", "signature", "staffs", "signature", form.signature);
informat.table.update("staffs", {
  id: "yhg8b23ej2gt6",
  signature: signature,
});

refreshDataSource

Refresh materialized view data When the data source is from a database view and the materialized view option is enabled, it will refresh the materialized view data. It will throw an exception in other cases.

informat.table.refreshDataSource(tableId);

Parameter	Type	Description
tableId	String	Identifier of the data table

Example

informat.table.refreshDataSource("techStaffsView");

refreshRelationRollup

Recalculate the value of relation list rollup field

informat.table.refreshRelationRollup(tableId, fieldId, recordIdList);

Parameter	Type	Description
tableId	String	Data table identifier
fieldId	String	Relation list rollup field identifier
recordIdList	String	List of main table record IDs. If empty, recalculate the entire table

Example

informat.table.refreshRelationRollup("staffs", "rewardCount", null); // Recalculate all records
informat.table.refreshRelationRollup("staffs", "rewardCount", ["yhg8b23ej2gt6"]); // Only recalculate the rollup data corresponding to yhg8b23ej2gt6

refreshLookupRollup

Recalculate the value of lookup list rollup field

informat.table.refreshLookupRollup(tableId, fieldId, recordIdList, subFilter);

Parameter	Type	Description
tableId	String	Data table identifier
fieldId	String	Lookup list rollup field identifier
recordIdList	Array<String>	List of main table record IDs. If empty, recalculate the entire table
subFilter	Filter	Child table filter condition, can be empty

Example

informat.table.refreshLookupRollup("staffs", "totalLevelDays", null, null); // Recalculate all records
informat.table.refreshLookupRollup("staffs", "totalLevelDays", ["yhg8b23ej2gt6"], null); // Only recalculate yhg8b23ej2gt6

queryChangeLogListCount

Get the total number of change records

informat.table.queryChangeLogListCount(tableId, filter);

Parameter	Type	Description
tableId	String	Data table identifier
filter	Filter	Filter conditions

List of fields that can be used in the filter

Field	Type	Description
id	Integer	Change record ID
fieldName	String	Field name
recordId	String	Record ID
createUserId	String	Creator ID

Return Value Type is Integer Returns the total number of change records

Example

const count = informat.table.queryChangeLogListCount("tableKey", {
  conditionList: [
    {
      fieldId: "recordId",
      opt: "eq",
      value: "gn43hedzzgztp",
    },
  ],
});
// Get the matching count
console.log("count:", count);

queryChangeLogList

Query change record list

informat.table.queryChangeLogList(tableId, informatQuery);

Parameter	Type	Description
tableId	String	Data table identifier
informatQuery	Query	Query conditions

List of fields that can be used in the filter

Field	Type	Description
id	Integer	Change record ID
fieldName	String	Field name
recordId	String	Record ID
createUserId	String	Creator ID

Return Value Type is Array<TableChangeLog> Returns change record list

Example

js

informat.table.queryChangeLogList("staffs", {
  pageIndex: 1,
  pageSize: 20,
  filter: {
    conditionList: [
      {
        fieldId: "recordId",
        opt: "eq",
        value: "yhg8b23ej2gt6",
      },
    ],
  },
});

json

[
  {
    "afterValue": {
      "type": "RelationRecord",
      "value": "fh1snm1k18kqc",
      "multiple": false,
      "valueString": "Sales Department"
    },
    "beforeValue": {
      "type": "RelationRecord",
      "multiple": false
    },
    "fieldName": "Department",
    "id": "1",
    "recordId": "yhg8b23ej2gt6"
  }
]

queryCommentListCount

Get total number of form comments

informat.table.queryCommentListCount(tableId, filter);

Parameter	Type	Description
tableId	String	Data table identifier
filter	Filter	Filter conditions

List of fields that can be used in the filter

Field	Type	Description
id	int	Comment ID
comment	String	Comment content
isDeleted	Boolean	Is deleted
parentId	Integer	Reply comment ID
recordId	String	Record ID
createUserId	String	Creator ID

Return Value Type is Integer Returns total number of form comments

Example

js

informat.table.queryCommentListCount("staffs", {
  conditionList: [
    {
      fieldId: "recordId",
      opt: "eq",
      value: "yhg8b23ej2gt6",
    },
  ],
});

json

1

queryCommentList

Query form comment list

informat.table.queryCommentList(tableId, query);

Parameter	Type	Description
tableId	String	Data table identifier
query	Query	Query conditions

List of fields that can be used in the filter

Field	Type	Description
id	int	Comment ID
comment	String	Comment content
isDeleted	Boolean	Is deleted
parentId	Integer	Reply comment ID
recordId	String	Record ID
createUserId	String	Creator ID

Return Value Type is Array<TableComment> Returns form comment list

Example

js

informat.table.queryCommentList("staffs", {
  pageIndex: 1,
  pageSize: 20,
  filter: {
    conditionList: [
      {
        fieldId: "recordId",
        opt: "eq",
        value: "yhg8b23ej2gt6",
      },
    ],
  },
});

json

[
  {
    "comment": "{\"type\":\"doc\",\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Good\",\"type\":\"text\"}]}]}",
    "createTime": 1717728322411,
    "createUserAvatar": "79a487eb7f384321bfaed16eb9e020d8.jpg",
    "createUserId": "skydu",
    "createUserName": "mr.du",
    "delete": false,
    "id": 1,
    "isDelete": false,
    "parentId": 0,
    "recordId": "yhg8b23ej2gt6"
  }
]

addComment

Add form comment

informat.table.addComment(tableId, tableComment);

Parameter	Type	Description
tableId	String	Data table identifier
tableComment	TableCommentForm	Form comment

Return Value Type is Integer Returns newly added ID

Comment Example

js

informat.table.addComment("staffs", {
  recordId: "yhg8b23ej2gt6",
  content: "Comment content",
  createUserId: "skydu", // Optional
});

json

2

Reply Comment Example

js

informat.table.addComment("staffs", {
  recordId: "yhg8b23ej2gt6",
  parentId: 2, // Optional
  content: "Reply to previous comment",
  createUserId: "skydu", // Optional
});

json

3

deleteComment

Delete form comment

TIP

If the comment has replies, it will be logically deleted; otherwise, it will be physically deleted. If the comment does not exist, an error will be reported indicating the comment does not exist.

informat.table.deleteComment(tableId, commentId);

Parameter	Type	Description
tableId	String	Data table identifier
commentId	Integer	Form comment Id

Example

informat.table.deleteComment("staffs", 3);

getIdFieldSeq

Get current auto-increment sequence of number field

informat.table.getIdFieldSeq(tableId, fieldId);

Parameter	Type	Description
tableId	String	Data table identifier
fieldId	String	Number field identifier

Return Value Type is Integer Returns current auto-increment sequence

Example

js

informat.table.getIdFieldSeq("task", "number");

Return Value

2

setIdFieldSeq

Set auto-increment sequence of number field

informat.table.setIdFieldSeq(tableId, fieldId, seq);

Parameter	Type	Description
tableId	String	Data table identifier
fieldId	String	Number field identifier
seq	Integer	Auto-increment sequence

Example

informat.table.setIdFieldSeq("staffs", "staffNo", 10);

updateRecordSeq

Update data table sort sequence

informat.table.updateRecordSeq(tableId, recordId, seq);

Parameter	Type	Description
tableId	String	Data table identifier
recordId	String	Data table record ID
seq	Integer	New sort sequence

Example

informat.table.updateRecordSeq("staffs", "yhg8b23ej2gt6", 101);

moveRecord

Move record

TIP

After moving, seq will be recalculated in order

informat.table.moveRecord(tableId, recordId, targetRecordId, type);

Parameter	Type	Description
tableId	String	Data table identifier
recordId	String	Record ID
targetRecordId	String	Target record ID
type	String	Drag method. Optional values are as follows: before: Move record before target record after: Move record after target record

Example

informat.table.moveRecord("task", "ksrhoqarae7hj", "dvmdfh74m0s2w", "before");

moveTreeViewRecord

Move record in tree view

informat.table.moveTreeViewRecord(tableId, childrenFieldId, recordId, parentRecordId, targetRecordId, type);

Parameter	Type	Description
tableId	String	Data table identifier
childrenFieldId	String	Children field
recordId	String	Record ID
parentRecordId	String	Parent record ID. Not required when moving at the same level
targetRecordId	String	Target record ID
type	String	Drag method. Optional values are as follows: before: Move record before target record after: Move record after target record inner: Inside parent object

Example 1 Move record at the same level

informat.table.moveTreeViewRecord("staffsDept", "children", "l8w9u9raftp2a", null, "pz9y1fse8hccw", "before");

Example 2 Move record to a new parent record

informat.table.moveTreeViewRecord("staffsDept", "children", "l8w9u9raftp2a", "fh1snm1k18kqc", "u3lma96ibq52h", "before");

moveLookupFieldRecord

Move record in lookup field list

informat.table.moveLookupFieldRecord(tableId, lookupFieldId, mainRecordId, recordId, targetRecordId, type);

Parameter	Type	Description
tableId	String	Data table identifier
lookupFieldId	String	Lookup field
mainRecordId	String	Main table record ID
recordId	String	Child table record ID
targetRecordId	String	Child table target record ID
type	String	Drag method. Optional values are as follows: before: Move record before target record after: Move record after target record

Example

informat.table.moveLookupFieldRecord("staffs", "leaveLogRel", "yhg8b23ej2gt6", "p54xumqba4kmm", "vdbt9kk1cugvy", "before");

moveRelationFieldRecord

Move record in relation field list

informat.table.moveRelationFieldRecord(tableId, relationFieldId, mainRecordId, recordId, targetRecordId, type);

Parameter	Type	Description
tableId	String	Data table identifier
relationFieldId	String	Relation field
mainRecordId	String	Main table record ID
recordId	String	Child table record ID
targetRecordId	String	Child table target record ID
type	String	Drag method. Optional values are as follows: before: Move record before target record after: Move record after target record

Example

informat.table.moveRelationFieldRecord("staffs", "rewardList", "yhg8b23ej2gt6", "vsf3lo6ryj6lc", "ct82dszf20s2u", "before");

moveChildrenFieldRecord

Move record in children field

informat.table.moveChildrenFieldRecord(tableId, childrenFieldId, mainRecordId, recordId, targetRecordId, type);

Parameter	Type	Description
tableId	String	Data table identifier
childrenFieldId	String	Children field
mainRecordId	String	Main table record ID
recordId	String	Child table record ID
targetRecordId	String	Child table target record ID
type	String	Drag method. Optional values are as follows: before: Move record before target record after: Move record after target record

Example

informat.table.moveChildrenFieldRecord("staffsDept", "children", "rpev2ztqnbu8o", "l8w9u9raftp2a", "pz9y1fse8hccw", "before");

moveLookupFieldTreeViewRecord

Move record in lookup field tree view

informat.table.moveLookupFieldTreeViewRecord(tableId, lookupFieldId, childrenFieldId, mainRecordId, recordId, parentRecordId, targetRecordId, type);

Parameter	Type	Description
tableId	String	Data table identifier
lookupFieldId	String	Lookup field
childrenFieldId	String	Children field
mainRecordId	String	Main table record ID
recordId	String	Child table record ID
parentRecordId	String	Parent record ID
targetRecordId	String	Child table target record ID
type	String	Drag method. Optional values are as follows: before: Move record before target record after: Move record after target record inner: Inside parent object

Example

informat.table.moveLookupFieldTreeViewRecord("lookup", "lookup_children", "children", "ozyqa05tj4xs0", "xim3193k04lu0", null, "pjrpekzh9gc2j", "before");

moveRelationFieldTreeViewRecord

Move record in relation field tree view

informat.table.moveRelationFieldTreeViewRecord(tableId, relationFieldId, childrenFieldId, mainRecordId, recordId, parentRecordId, targetRecordId, type);

Parameter	Type	Description
tableId	String	Data table identifier
relationFieldId	String	Relation field
childrenFieldId	String	Children field
mainRecordId	String	Main table record ID
recordId	String	Child table record ID
parentRecordId	String	Parent record ID
targetRecordId	String	Child table target record ID
type	String	Drag method. Optional values are as follows: before: Move record before target record after: Move record after target record inner: Inside parent object

Example

informat.table.moveRelationFieldTreeViewRecord("relation", "relation_children", "children", "xzyqa35tj4xsq", "kim3193k54lu8", null, "fjrpekzh9gc6h", "before");

moveChildrenFieldTreeViewRecord

Move record in children field tree view

informat.table.moveChildrenFieldTreeViewRecord(tableId, mainChildrenFieldId, childrenFieldId, mainRecordId, recordId, parentRecordId, targetRecordId, type);

Parameter	Type	Description
tableId	String	Data table identifier
mainChildrenFieldId	String	Children field
childrenFieldId	String	Children field
mainRecordId	String	Main table record ID
recordId	String	Child table record ID
parentRecordId	String	Parent record ID
targetRecordId	String	Child table target record ID
type	String	Drag method. Optional values are as follows: before: Move record before target record after: Move record after target record inner: Inside parent object

Example

informat.table.moveChildrenFieldTreeViewRecord("children", "children_children", "children", "izyqa25tj4xsp", "iim3103k54lup", null, "ilrpekzh3gc2p", "before");

refreshIndexNumber

Recalculate children field index numbers

informat.table.refreshIndexNumber(tableId, fieldId, parentRecordId);

Parameter	Type	Description
tableId	String	Data table identifier
fieldId	String	Children field index number field
parentRecordId	String	Parent record ID, can be empty; when empty, recalculate all index numbers

Example

informat.table.refreshIndexNumber("staffsDept", "indexNumber", "rpev2ztqnbu8o"); // Recalculate child record list for parent record
informat.table.refreshIndexNumber("staffsDept", "indexNumber", null);