OData API

The Thinkwise Indicium application tier offers an open API that can be used to perform CRUD-operations and execute procedures on the applications that have been made with the Thinkwise Platform. Through Indicium, a user will only have access to those tables, views and procedures for which the user has been authorized in IAM.

Indicium implements version 4.0 of the OData standard. The OData standard is well documented and as such this manual will not explain all of its features in detail. The OData documentation can be found using the link http://www.odata.org/documentation/

Base URL

To access the API of a specific application, the URL of the request must always start with:

<web_app_root_url>/iam/<appl>/

In this template, <web_app_root_url> refers to the root URL of Indicium in IIS and <appl> refers to the ID or the alias of the application in question in IAM. Below, you will find two examples of valid base URLs:

https://server/indicium/iam/123/
https://server/indicium/iam/insights/

All requests that start with this base-URL will be subjected to authentication. The type of authentication used depends on the configuration of the user in question in IAM. All authentication follows the HTTP Basic authentication scheme, meaning that Indicium should only be exposed to the Internet over HTTPS.

note

Indicium running on a 2018.2 and earlier IAM must use <web_app_root_url>/api/<appl>/ instead.

Public API call

For access without authentication, you can make public API calls. This allows you to easily integrate with messaging services like:

Azure PubSub, Service Bus and Event Hubs
Amazon SQS and SNS

Public API calls require a role with Allow as public API enabled in the Software Factory. For more information, see Public API roles.

After configuring the Public API role, you can make the public API call. The URL is the same as a regular API call aside from an extra /public/ segment as the first segment in the URL.

A regular API call URL:
https://my_server/indicium/iam/my_appl/my_procedure
A public API call URL:
https://my_server/indicium/public/iam/my_appl/my_procedure

If the /public/ and /open/ segments are used simultaneously, the /public/ segment must precede the /open/ segment.

Since public API calls do not require authentication, the function tsf_user() will return the username of the database pool user that Indicium is configured to use.

Public API call will result in the following generated session variables:

tsf_original_login, tsf_use_log_session_id, and guid will all be empty.
tsf_is_public_request identifies public requests on the database. It can have the values 0 or 1.

Software Factory

The Indicium API provides access to branches in the Software Factory development environment, if the following conditions are met:

The Software Factory is available as an application in the IAM specified in Indicium's appsettings.json.
The user must be marked as Active in the runtime configuration of the branch in the Software Factory.

<web_app_root_url>/sf/<sf_appl>/

The segment <sf_appl> refers to the application ID or application alias which can be found in the Software Factory at the desired runtime configuration. The runtime configuration must be activated for the user in the Software Factory to obtain access via Indicium.

For example:

https://server/indicium/sf/144/
https://server/indicium/sf/insights/

Or, if you are using Universal GUI:

https://server/indicium/iam/sf/

On the off-chance that there are multiple Software Factory applications in IAM, the segment /sf/ can be substituted with the alias of another Software Factory application.

Filter within an Open API document

You can filter within an Open API document. This can be a powerful method to reduce the document size, resulting in faster loading times, or to selectively show specific entities. The following query string parameters are available:

entities - A single entity or a comma-separated list of entities. The entities can be tables, reports, and tasks.
- Example: iam/<appl>/openapi?entities=employee,employee_hour,project
include_lookups - A boolean (true or false) that indicates whether the lookups need to be included in the document.
- Example: iam/<appl>/openapi?include_lookups=true
include_details - A boolean (true or false) that indicates whether the details need to be included in the document.
- Example: iam/<appl>/openapi?include_details=true
include_table_tasks - A boolean (true or false) that indicates whether the table tasks need to be included in the document. You can filter normal tasks with the entities' query string parameter.
- Example: iam/<appl>/openapi?include_table_tasks=true
include_table_reports - A boolean (true or false) that indicates whether the table reports need to be included in the document. You can filter normal tasks with the entities' query string parameter.
- Example: iam/<appl>/openapi?include_table_reports=true

Standard operations

Indicium offers several standard operations that can be performed on every application.

Metadata

With the standard operation /$metadata, you can retrieve the metadata document for an application. To do this, add $metadata behind the base URL of the application.

The metadata document contains an extensive description of all entities and operations available for that particular user in that particular application. You can use it to validate whether a user's permissions in IAM have been configured properly. It can also serve as a guideline for writing your own Indicium client.

Open API

With the standard operation /openapi, you can retrieve the API specification for an application in JSON format. To do this, add /openapi behind the base URL of the application.

The API specification can be used by third-party tools to generate code or documentation. Copy-paste the JSON content into the editor to provide a web-based UI with information about your API. Examples include:

NSwagStudio for code generation.
Swagger for documentation generation.
Postman for API testing. Postman will use the schemas defined in the API specification to create a new collection and automatically generate request and response bodies.

Refresh model

Windows GUI Web GUI Indicium

The standard operation Refresh model can be accessed from the ribbon tab Developer in the Windows or Web GUI. It is also available as the /refresh_model API endpoint in the Indicium application tier. To reload (refresh) the model, send a POST request to one of the following URLs:

<web_app_root_url>/sf/<appl>/refresh_model

<web_app_root_url>/iam/<appl>/refresh_model

OData Service Document

With the standard operation /application.svc, you can retrieve the OData Service Document. This is a metadata document that contains all of the top-level feeds that are exposed for an application model. To do this, add /application.svc behind the base URL of the application, using the format <application_root>/application.svc. For example, https://my_server/indicium/iam/appl/application.svc.

The OData Service Document can be used by clients to discover the feeds that a service offers. Some clients rely on this Service Document, such as OData Feed. This can be used in PowerBI queries.

CRUD-operations, functions and procedures

Aside from the standard operations, the Indicium API offers the ability to perform CRUD-operations on entities and to execute tasks, reports, functions and procedures on the database.

Note that every insert, update, task and report action is done by Indicium via a process called resource staging. For a more detailed explanation of this process and a description of the available endpoints and operations on each endpoint, see Resource Staging.

API action	HTTP Verb	Example request
Select an entity	`GET`	Request URL to retrieve all records from the project entity: `https://server/indicium/iam/appl/project` Request url to retrieve all records from the overview variant of the project entity: `https://server/indicium/iam/appl/project.overview` Request URL to retrieve a specific record from the project entity given that it only has one numerical primary key column: `https://server/indicium/iam/appl/project(123)` Request URL to retrieve a specific record from the sub_project entity which has a primary key of project_id and sub_project_id: `https://server/indicium/iam/appl/sub_project(project_id=123,sub_project_id=321)` Request URL to retrieve the first 100 records from the project entity where the project_id is greater than 100, ordered by the project ID in ascending order: `https://server/indicium/iam/appl/project?$top=100&$select=project_id, description&$filter=project_id gt 100&$orderby=project_id asc`
Insert an entity	`POST`	Request URL to insert a new record into the project entity: `https://server/indicium/iam/appl/project` Request body assuming that project_id is not an identity: `{"project_id": 123, "description": "a new project", "deadline": "2017-01-01T12:00:00.000"}` Only one record can be inserted per request. All query string parameters will be ignored. See also Resource Staging and Choose a lookup value.
Insert a detail entity	`POST`	Request URL to insert a new subproject record in the context of the project entity: `https://server/indicium/iam/appl/project(1)/detail_ref_project_subproject` Request body: `{"subproject_name": "Documentation"}` Only one record can be inserted per request. All query string parameters will be ignored. The last part of the url is the name of the reference prefixed by `detail_`. The context of the parent record is automatically filled in by Indicium. See also Choose a lookup value.
Update an entity	`PATCH`	Request URL to update the description of an existing record of the project entity: `https://server/indicium/iam/appl/project(123)` Request body: `{"description": "a changed project"}` Records can only be updated by specifying their entire primary key between the parentheses. All query string parameters will be ignored. See also Resource Staging and Choose a lookup value.
Delete an entity	`DELETE`	To delete an existing record of the project entity: `https://server/indicium/iam/appl/project(123)` Records can only be deleted by specifying their entire primary key between the parentheses. All query string parameters will be ignored.
Execute a task	`POST`	Request URL to call the task my_task with parameters project_id and description: `https://server/indicium/iam/appl/my_task` Request body: `{"project_id": 123, "description": "my project"}` All query string parameters will be ignored. See also Resource Staging.
Execute a task on a record	`POST`	Request URL to call the task `generate_invoice` with parameters in the context of a project. The table task parameters are automatically filled in by Indicium. `https://server/indicium/iam/appl/project(1)/task_generate_invoice` Request body: `{"month": 1}`
Execute a task on an empty detail	`POST`	Request URL to call the task `create_default_subprojects` on the subproject subject with parameters. The task is executed within the context of a project. `https://server/indicium/iam/appl/project(1)/` `empty_detail_ref_project_subproject/task_create_default_subprojects` Request body: `{"create_default_activities": true}` The context of the parent record is automatically filled in by Indicium.
Export a report	`POST`	Request URL to call the report `my_report` with parameters project_id and description: `https://server/indicium/iam/appl/my_report` Request body: `{"project_id": 123, "description": "my project"}` All query string parameters will be ignored. See also Resource Staging.
Call a function	`GET`	Request URL to call the function `my_function` with parameters project_id and description: `https://server/indicium/iam/appl/my_function(project_id=123,description='my project'`) All query string parameters will be ignored.
Call a procedure	`POST`	Request URL to call the procedure `my_procedure` with parameters project_id and description: `https://server/indicium/iam/appl/my_procedure` Request body: `{"project_id": 123, "description": "my project"}` All query string parameters will be ignored.

warning

It is important to apply URL encoding (also known as Percent-encoding) to all value literals in the request URL. Value literals can only occur between the parentheses ( ) after the name of an entity and in the value of the $filter query string parameter.

Subroutines

For security reasons, subroutines are not exposed by the Indicium OData API by default. To expose a subroutine using Indicium, enable the API option of the subroutine in the Software Factory. The service name of a subroutine and its parameters can be changed using the Alias settings.

Choose a lookup value

There are three ways to specify values for lookup columns and columns with elements when directly POSTing or PATCHing to table, task and report endpoints:

choose - Use this to choose a lookup value by providing primary key values of the lookup source. This can be useful if the client knows from which source record to select the target value, but the foreign key from the target to the source is not part of the source's primary key.
```
POST <metasource>/<application>/<target_table>
{
"<target_FK_column>": {
   "choose": {
      "<source_PK_column>": "36724a01-521a-4b4a-b51f-30d943f9a0d0"
   }
}
}
```
choose_by_display - Use this to choose a lookup value by providing the value from the column configured as the display column.
- The display value must resolve to a unique record in the source.
- When the display column has domain elements, the given value must correspond to the database value of an element instead of the translation.
- Use choose_by_element instead when selecting based on element translation values.
For example, if the model contains a project table:

project_id project_name
1234 Example project

and a sub_project table that has a sub_project.project_id -> project.project_id lookup which is configured to display the project.project_name. Then, Indicium allows the following when adding a new sub_project:
```
POST <metasource>/<application>/sub_project

{
"project_id": {
   "choose_by_display": {
      "display_value": "Example project"
   }
}
}
```
This example would resolve to 1234 as the value for project_id for the new sub project.

project_id	project_name
1234	Example project

choose_by_element - Use this to choose the database value for a column configured with elements based on the translation value for an element.

Use it for both regular columns and lookups that have configured a display column with elements.
When used with a lookup, the value must resolve to a unique record in the source, just like when using choose_by_display.

For example, in a model containing a domain example_domain configured with the following elements:

element_id	element_value
example_element_1	0
example_element_2	1
example_element_3	2

and element translations:

element_id	lang_id	transl_value
example_element_1	EN	Element one
example_element_2	EN	Element two
example_element_3	EN	Element three
example_element_1	NL	Element een
example_element_2	NL	Element twee
example_element_3	NL	Element drie

Then, Indicium will allow a column using this domain to be posted or patched like this:

POST <metasource>/<application>/example_table
{
"some_element_column": {
   "choose_by_element": {
      "transl_value": "Element two"
   }
}
}

PATCH <metasource>/<application>/example_table(1234)
{
"some_element_column": {
   "choose_by_element": {
      "transl_value": "Element three"
   }
}
}

By default, this feature uses the session user's language to match the translations or the application's fallback language if the application does not contain translations for the user language. The above example assumes this to be English (EN) for the user making the example request. You can overwrite this behavior by providing a language_hint value with the column. For example, to use Dutch instead of English in the previous example, specify the following:

POST <metasource>/<application>/example_table
{
"some_element_column": {
   "choose_by_element": {
      "transl_value": "Element twee",
      "language_hint": "NL"
   }
}
}

If the language for the language_hint cannot be found, the application's fallback language will be used instead.

Supported OData operations

Indicium supports the following standard OData operations and query string options in the URL:

$metadata
$top
$skip
$select
$filter
$search
$orderby
$expand
$apply (with groupby or standard computations)
$query

In addition to the standard OData operations, Indicium offers several extended operations. See also Extended API.

note

Use OData query options like $filter, $search or $orderby to filter and/or order a set of records.
Use $seek to search within this filtered and sorted set.

Top

To get the number of records in a table without getting actual data, use a $top=0&$count=true query.

Template: http://server/indicium/iam/123/table?$top=0&$count=true

Example: https://xxx.thinkwise.app/indicium/iam/265/uur?$top=0&$count=true

Response: {"@odata.context":"https://tcp.thinkwise.app/iam/265/$metadata#uur","@odata.count":598325,"value":[]}

Filter

Indicium supports filtering on and ordering by the display value of a lookup. This can be achieved by specifying reference_id/display_column_id in the request URL, rather than simply the ID of the column. For example, when filtering by lookup display value:

http://server/indicium/iam/123/table?$filter=contains(reference_id/display_column_id,'value')

In addition to the standard OData operations, Indicium also supports a custom $prefilter operation which can be used to add prefilters to requests in order to select records from entities. For example:

http://server/indicium/iam/123/table?$prefilter=prefilter_1,prefilter_2

Search

The $search query option can be used to retrieve all records that match a free-text search expression. For example:

http://server/indicium/iam/123/project?$search="my project"

You can only search in columns included in the Search field.

Expand

The $expand operation allows you to select any properties from the entity on the other side of the navigation property. The navigation properties for lookups will always lead to the entity that contains the display property of the lookup, which is not necessarily the same entity as the source_tab of the corresponding lookup reference.

Examples:

http://server/indicium/api/123/sub_project?$expand=transl_project_id
To select all columns from sub_project and project.
http://server/indicium/api/123/sub_project?$select=project_id&$expand=transl_project_id($select=description) To select project_id from sub_project and description from project.

note

When using $expand, you can use all of the supported operations such as $select, $filter, $orderby, and $prefilter on the target entity, but for now only $select is supported on the source entity in the $expand clause.

The response of an $expand request will look like this:

{
   "value":
   [
      {
         "project_id":15,
         "sub_project_id":25,
         "name":"Board of supervisors",
         "hourly_rate":135.00,
         "planned_start_date":"2006-08-18T00:00:00Z",
         "planned_end_date":"2007-08-19T00:00:00Z",
         "hours_budgeted":337.00,
         "actual_start_date":"2009-11-18T00:00:00Z",
         "finished":1,
         "finished_on_date":"2009-09-24T00:00:00Z",
         "finished_text":1,
         "hours_booked":806.25,
         "sub_project_name":"Board of supervisors",
         "start_date_and_end_date":"2006-08-18 | 2007-08-19",
         "transl_project_id":
         {
            "description":"Hydraulics"
         }
      }
   ]
}

You can use the $expand operation also for one-to-many (1:N) relationships. For example, to retrieve a parent record and its detail records in a single API call. To expand a detail within the context of a parent, the request should look like this:

<indicium_base_url>/<meta>/<application>/<parent_entity>?$expand=detail_<ref_id>

For example: https://server/indicium/iam/appl/sales_order?$expand=detail_ref_sales_order_sales_order_line

Most of the supported operations such as $select and $filter can also be applied to the expanded detail records. They can be added as a semicolon-separated list between parentheses.

For example, to retrieve all sales orders including the order_id and order_line_id of their order lines with a cost greater than 10:

https://server/indicium/iam/appl/sales_order?$expand=detail_ref_sales_order_sales_order_line($select=sales_order_id,sales_order_line_id;$filter=sales_order_line_cost gt 10)

Apply with Groupby

You can also use the $apply=groupby clause to select lookup values, but this method should typically only be used when selecting properties that are part of the lookup reference or the display property, otherwise you're better off using $expand. All columns specified in the $apply=groupby clause will in fact be added to a GROUP BY clause in the resulting SQL statement.

Example:

http://server/indicium/api/123/sub_project?$apply=groupby(project_id,transl_project_id/description)

To select project_id from sub_project and description from project and group the result by both values.

The response of an $apply=groupby request will look the same as the response of an $expand request, depending on the properties you select or group by:

{
   "value":
   [
      {
         "project_id":15,
         "transl_project_id":
         {
            "description":"Hydraulics"
         }
      }
   ]
}

It is also possible to apply an aggregate function to the display property, when a key can result in multiple display values or if you want to provide an alias to the display value.

Example:

http://server/indicium/api/123/sub_project?$apply=groupby((project_id),aggregate(transl_project_id/description with min as display_field)

Apply with standard computations

Standard computations are a great way to extract data from a column without the need to store them in a different column with a default expression. For example, if the computation Year is called, the computation will return the year of the supplied dates. Or, if you want to get a part of a column value, you can use Substring to retrieve it.

Indicium supports the following standard computations:

Computation	Input	Output	Example
Date	Date, DateTime	Date	$apply=Compute(Date(columnname) as Alias)
Time	Time, DateTime	Time	$apply=Compute(Time(columnname) as Alias)
Isoweek	Date, DateTime	Int	$apply=Compute(Isoweek(columnname) as Alias)
Weekday	Date, DateTime	Int	$apply=Compute(Weekday(columnname) as Alias)
Year	Date, DateTime	Int	$apply=Compute(Year(columnname) as Alias)
Quarter	Date, DateTime	Int	$apply=Compute(Quarter(columnname) as Alias)
Month	Date, DateTime	Int	$apply=Compute(Month(columnname) as Alias)
Day	Date, DateTime	Int	$apply=Compute(Day(columnname) as Alias)
Hour	Time, DateTime	Int	$apply=Compute(Hour(columnname) as Alias)
Minute	Time, DateTime	Int	$apply=Compute(Minute(columnname) as Alias)
Second	Time, DateTime	Int	$apply=Compute(Second(columnname) as Alias)
Ceiling	Int, Numeric, Decimal	Int	$apply=Compute(Ceiling(columnname) as Alias)
Substring	Varchar, Nvarchar, Char, Nchar	Varchar	$apply=Compute(Substring(columnname, Startindex, Length) as Alias)
Concat	Varchar, Nvarchar, Char, Nchar	Varchar	$apply=Compute(Substring(FirstColumn, SecondColumn) as Alias)

note

If $compute is used in the URL, the alias must be included in $select in order to be selected. The alias is also available in $filter, $orderby, and $groupby.

Example:

compute(year(column) as alias)

Becomes:

select datepart(year, column) as alias

Chain transformations in the $apply clause

Indicium Universal

In the $apply clause, you can do a single compute, groupby, aggregate, groupby+aggregate, or filter transformation. Added to this, you can chain the transformations indefinitely, with each transformation adding to the result set of the previous transformation.

To illustrate this, the request below selects all customers with more than five projects in the year 2021:

/project?$apply=compute(year(project_date) as project_year) 
                /filter(project_year eq 2021) 
                /groupby((customer_id), aggregate(project_id with count as count)) 
                /filter(count gt 5)&$select=customer_id

Extended API

In addition to the standard OData API, Indicium offers several extended operations.

Deselect

$deselect is a comma-separated list of column IDs which are not requested. Use $deselect instead of $select if you want to exclude only a few columns and keep the rest. So, remove $select (to make all columns available) and add the exceptions with $deselect. This can help reduce the response size when, for example, binary data is stored in columns, i.e. $deselect=column_one, column_two.

Prefilter

$prefilter is a comma-separated list which can be used for adding prefilters to requests so you can select records from entities. Authorization prefilters are applied automatically, regardless of whether you explicitly specify them. This prevents unauthorized access to data beyond your authorized rights.

Export

Exports the data set for a table or table variant to the file system. The allowed file formats are .xls, .xslx and .csv. For example:

http://server/indicium/sf/123/customer?$export=csv
http://server/indicium/iam/123/sales_order?$export=xlxs

Seek

$seek returns the row-index of the records that match the $seek expression. When the custom $seek query option is used, Indicium will return the row index of the records that match the given filter condition. For example:

http://server/indicium/iam/123/project?$seek=contains(name,'my project')

The response of a $seek request will look like this:

{
   "value":
   [
      {
         "row_index@odata.type": "#Int64",
         "row_index": 1,
         "project_id":1,
         "name":"my project"
      },
      {
         "row_index@odata.type": "#Int64",
         "row_index": 2,
         "project_id":10,
         "name":"this is also my project"
      }
   ]
}

API validation

Indicium validates that the user has access to the API and that the operations are allowed. For example:

When calling a task on a record, Indicium validates that this task is available by executing the context procedure.
When a request body contains parameters, the layout procedure is executed to ensure that the parameters can be edited by the user executing the API.
The same applies to inserting or patching a record. For each parameter in the request body, Indicium will execute a layout procedure to detect if the column is editable. If a column is not editable, the action aborts, and a status code "Forbidden" is returned. After patching the value, a default is executed that can change the column value. For the layout and the default procedure, the cursor_from_col_id will be filled in for each patched parameter.
If the column is a lookup control, the value is validated to ensure it is a valid option for the user.

TSFMessages header

In some cases, Indicium returns a "TSFMessages" header. This is a base64 encoded header and contains the information why the request failed. This can be a message that has been added by Indicium, for example, because not all mandatory parameters are filled in. It can also be a message from the database sent via the tsf_send_message procedure.

tip

Base64 encoded messages can be decoded on this website: https://www.base64decode.org/.
Click the DECODE button to show the message in JSON format.

Example of a TSFMessages header:

>eyJNZXNzYWdlVHlwZSI6IkluZGljaXVtTWVzc2FnZSIsIk1lc3NhZ2VJRCI6Imd1aV9jYW5ub3RzYXZlbWFuZGF0b3J5IiwiTmFtZWRQYXJhbWV0ZXJzIjpbeyJLZXkiOiIwIiwiVmFsdWUiOiJOYW1lIn1dLCJSYXdNZXNzYWdlIjpudWxsLCJUcmFuc2xhdGVkTWVzc2FnZSI6IkNhbm5vdCBzYXZlIHRoaXMgcmVjb3JkIGJlY2F1c2Ugbm90IGFsbCBvZiB0aGUgcmVxdWlyZWQgZmllbGRzIHdlcmUgZW50ZXJlZC4gUmVxdWlyZWQgZmllbGQ6ICdOYW1lJy4ifQ==

Decoded result:

{
"MessageType":"IndiciumMessage",
"MessageID":"gui_cannotsavemandatory",
"NamedParameters":[{"Key":"0","Value":"Name"}],
"RawMessage":null,
"TranslatedMessage":"Cannot save this record because not all of the required fields were entered. Required field: 'Name'."
}

$query

To avoid exceeding the maximum URL length, it is possible to pass the query options part of the OData URL in the request body and append /$query to the resource path of the request URL.

When $query is used, the request has to be a POST request instead of a GET, and the request body has to use the content type text/plain.

For example:

POST http://server/indicium/iam/123/project/$query
Content-Type: text/plain

Request body: $filter=contains(name,'my project')&$select=project_id

The response will be the same as the response of a GET request with the query options specified in the request URL.

Query options specified in the request body are processed together with the query options specified in the request URL.

Was this article helpful?

Base URL​

Public API call​

Software Factory​

Filter within an Open API document​

Standard operations​

Metadata​

Open API​

Refresh model​

OData Service Document​

CRUD-operations, functions and procedures​

Subroutines​

Choose a lookup value​

Supported OData operations​

Top​

Filter​

Search​

Expand​

Expand with related entities​

Apply with Groupby​

Apply with standard computations​

Chain transformations in the $apply clause​

Extended API​

Deselect​

Prefilter​

Export​

Seek​

API validation​

TSFMessages header​

$query​

Base URL

Public API call

Software Factory

Filter within an Open API document

Standard operations

Metadata

Open API

Refresh model

OData Service Document

CRUD-operations, functions and procedures

Subroutines

Choose a lookup value

Supported OData operations

Top

Filter

Search

Expand

Expand with related entities

Apply with Groupby

Apply with standard computations

Chain transformations in the $apply clause

Extended API

Deselect

Prefilter

Export

Seek

API validation

TSFMessages header

$query