Data Table Services
There are a number of built-in services for getting data in and out of data tables that are specific to the data table shape. They are as follows:
• AddDataTableEntry (TAGS tags, VALUES values) — Pass in VALUES, and optionally tags. Boolean return indicates success or failure.
• AddDataTableEntries
— Adds multiple data table entries.
• AddOrUpdateDataTableEntry (TAGS tags, VALUES values) — Pass in VALUES , and optionally tags. Returns a STRING value of the streamID of the new or updated entry in the data table. Updates a row if it exists, otherwise a row is added.
• AddOrUpdateDataTableEntries—Adds or updates multiple data table entries. Updates a row if it exists, otherwise a row is added. Primary keys must match in order to update.
• AssignDataTableEntries—Replaces existing data table entries.
• DeleteDataTableEntry (STRING keyvalue) — Deletes a single entry based on the key value. Boolean return indicates success or failure.
 |
Requires a primary key to delete an entry.
|
• DeleteDataTableEntries—Deletes multiple entries that match the first row of values provided in the infotable input parameter. For example, if a data table has a field named Int01 and three entries have Int01 set to 10. When executed, DeleteDataTableEntries will delete all three entries if 10 is specified as the delete criteria.
|
|
An error stating "Invalid Number of values provided to DeleteDataTableEntries in thingName" is displayed if more than one row of values is provided in the infotable input parameter.
|
• FindDataTableEntries— Searches for indexes only, so it may be faster to use than QueryDataTableEntries service.
|
|
If more than one index is defined in the configuration for the data table, the index that is used to search on is weighted depending on what you have indicated as fields to search on in the Values parameter that you pass into FindDataTableEntries. For example, if you have four properties (INTEGER, BOOLEAN, STRING, and TEXT) and you have created two indexes (one on BOOLEAN and STRING and one on TEXT), if you only specify a value to search on for the TEXT property, that index will be weighted heavier than the BOOLEAN/STRING index because you have provided a value to search for in that index.
|
|
|
Only one infotable input parameter is accepted for FindDataTableEntries. No result is returned if no input or more than one input parameter is provided.
|
• GetDataTableEntries (NUMBER maxItems) — Returns that latest entries up to the maximum number of items requested.
• GetDataTableEntry (STRING keyvalue) — Returns an infotable of one row with the matching entry.
• GetFieldNames — returns a list of field names associated with this data table.
• PurgeDataTableEntries — Removes all entries for this data table. Boolean return indicates success or failure.
• QueryDataTableEntries (NUMBER maxItems, STRING queryExpression, TAGS tags, VALUES query) — Returns an infotable of records that match the request parameters.
|
|
Only one infotable input parameter is accepted for QueryDataTableEntries.
If no input parameter is provided, QueryDataTableEntries returns all the rows of the data table.
If more than one input parameter is provided, rows matching only the first input are returned, and other inputs will be discarded. In this case, use the Query input parameter of the service.
|
• SearchDataTableEntries— Returns all data table entries that match the search query parameters. The searchExpression is the key to this service, it includes full text searching for STRING and TEXT fields only. The following filers can be used for the searchExpression: + (plus sign), case sensitive, ? (question mark), % (percent sign), AND, and OR.
• UpdateDataTableEntry (TAGS tags, VALUES values) — Pass in values, and optionally tags. Boolean return indicates success or failure. Updates a row if it exists.
Defining Case Sensitivity in Data Table Searches
You can define case sensitivity in data table searches by using a filter in the query parameter.
For example, using the SearchDataTableEntries service with the following entries:
You can define a filter for the query parameter to return case sensitive results. For example:
{"filters":{"type":"EQ", "fieldName":"Field1","isCaseSensitive":true, "value":"AA"}}
Using the query above, the following results are returned:
In another example using the same service, using the following filter in the query parameter:
{"filters":{"type":"EQ", "fieldName":"Field1","isCaseSensitive":true, "value":"aa"}}
The following results are returned:
