Retrieve table data tutorial

In this tutorial, you will use the Varicent Incentives REST APIs to retrieve results from a specific table in your model.

Prerequisites

You have created tables and uploaded data into those tables. For more information on creating tables and uploading data, see Tables.
You have obtained your Varicent API Key as outlined in REST API authentication and headers.

Overview

In this tutorial, we'll be using the following REST API calls to retrieve data:

Get table list: This call gets the exact name of the table that contains the data that you want to retrieve.

Get input form ID: This call gets the ID of the input form from the table that contains the data that you want to retrieve.

Get table data: This call gets the data from the table.

Retrieving the table name

First, use the Varicent Incentives REST API call to retrieve a list of all the tables to confirm the name of the one you want to retrieve data from.

If you know the name of the table you want to retrieve data from, you can skip these steps.

Note

System tables, such as the Payee and Account tables, has an underscore at the end of the name. For example, Payee_.

Make the following request:

GET /api/v1/customtables

For example,

curl -X GET -H "Authorization: Bearer API_KEY" 
-H "Model: YOUR_CLIENT_MODEL” 
-H "Content-Type: application/json" 
https://YOUR_ICM10_API_SERVER_ADDRESS/api/v1/customtables

You can use the following parameters to filter the return response:

Parameter	Description
filter	Supported fields are: `name`: the table name. `tabletype`: such as custom, structural, dimension (hierarchy), and data. `parentblockid`: the component ID where the table is located. The supported operations are: Equal matching `name=admin`. This is strict and case-sensitive. Starts with `name^admi`, or ends with `name$min`. This is case-insensitive. Equal matching within a list, such as `userType=Admin\\,Web`. This is case-insensitive. Interval matching, such as `date=[2015-09-22\\,2015-09-25]`, supports the mathematics notation for inclusive/exclusive bounds, `[a\\,b]`, `(a\\,b)`, or a mix `(a\\,b]`. Intervals also support open-ended bounds, `(a\\,]`. Not equal matching `name<>admin`. This is strict and case-sensitive. Is contained in `name⊇dmi`. This is case insensitive. NULL checks, such as `name=NULL` or `name<>NULL`. Only equal and not equal operators are supported. You can use multiple filters on one attribute. The filter query also supports the use of an operator. Complete filter definitions, as described above, are expected on both sides of the operator. The column filtered must be the same on both sides of the operator. Supported operators are: AND `&&`, OR `\|\|`. To define a filter on multiple attributes, separate each filter statement with `;`. The following is an example using the filter parameter: GET /api/v1/customtables?filter=name=Payee
orderBy	This parameter provides sorting by column (multiple columns supported, comma delimited). The supported fields are `name`, and `isHistoryEnabled`. Direction: + (ascending), - (descending) Column: name For example, `orderBy=-name`

Parameter

Description

filter

Supported fields are:

name: the table name.
tabletype: such as custom, structural, dimension (hierarchy), and data.
parentblockid: the component ID where the table is located.

The supported operations are:

Equal matching `name=admin`. This is strict and case-sensitive.
Starts with `name^admi`, or ends with `name$min`. This is case-insensitive.
Equal matching within a list, such as `userType=Admin\\,Web`. This is case-insensitive.
Interval matching, such as `date=[2015-09-22\\,2015-09-25]`, supports the mathematics notation for inclusive/exclusive bounds, `[a\\,b]`, `(a\\,b)`, or a mix `(a\\,b]`. Intervals also support open-ended bounds, `(a\\,]`.
Not equal matching `name<>admin`. This is strict and case-sensitive.
Is contained in `name⊇dmi`. This is case insensitive.
NULL checks, such as `name=NULL` or `name<>NULL`. Only equal and not equal operators are supported.

You can use multiple filters on one attribute. The filter query also supports the use of an operator. Complete filter definitions, as described above, are expected on both sides of the operator. The column filtered must be the same on both sides of the operator. Supported operators are: AND `&&`, OR `||`.

To define a filter on multiple attributes, separate each filter statement with `;`.

The following is an example using the filter parameter:

GET /api/v1/customtables?filter=name=Payee

orderBy

This parameter provides sorting by column (multiple columns supported, comma delimited). The supported fields are name, and isHistoryEnabled.

Direction: + (ascending), - (descending)
Column: name

For example, orderBy=-name

In the response body, find the table containing the data that you want to retrieve, and note the name.

},
{
    "table":{
        "name": "Payee_",
        "tableType": "Dimension",
        "effectiveDated": false,
        "version": {
            "rowVersion": 1601027
        }
    },
    "isWebEnabled": false,
    "isHistoryEnabled": true,
    "comment": "",
    "isGlobal": true,
    "elementId": 4,
    "parentBlockId": 6,
    "bounds": {
        "x": 118.8,
        "y": 2.46,
        "width": 144.0667,
        "height": 68.04
    },
    "color": {
        "a": 255,
        "b": 61,
        "g": 188,
        "r": 251
    },
    "name": "Payee_",
    "parentBlock": {
        "id": 6,
        "href": "/api/v1/components/6"
    },
    "version": {
        "rowVersion": 1457003
    }

Retrieving the input from ID

Next, use the Varicent Incentives REST API call to retrieve a list of all the input forms for the specified table.

If the table you want to retrieve data from does not contain any additional input forms, the ID is zero (0), and you can skip the following steps.

Make the following request:

GET /api/v1/customtables/{tablename}/inputforms

For example,

curl -X GET -H "Authorization: Bearer API_KEY" 
-H "Model: YOUR_CLIENT_MODEL” 
-H "Content-Type: application/json" 
https://YOUR_ICM10_API_SERVER_ADDRESS/api/v1/customtables/Payee_/inputforms

You can use the following parameters to filter the return response:

Parameter	Description
filter	The supported field is `currentname` (the input form name). The supported operations are: Equal matching `name=admin`. This is strict and case-sensitive. Starts with `name^admi`, or ends with `name$min`. This is case-insensitive. Equal matching within a list, such as `userType=Admin\\,Web`. This is case-insensitive. Interval matching, such as `date=[2015-09-22\\,2015-09-25]`, supports the mathematics notation for inclusive/exclusive bounds, `[a\\,b]`, `(a\\,b)`, or a mix `(a\\,b]`. Intervals also support open-ended bounds, `(a\\,]`. Not equal matching `name<>admin`. This is strict and case-sensitive. Is contained in `name⊇dmi`. This is case insensitive. NULL checks, such as `name=NULL` or `name<>NULL`. Only equal and not equal operators are supported. You can use multiple filters on one attribute. The filter query also supports the use of an operator. Complete filter definitions, as described above, are expected on both sides of the operator. The column filtered must be the same on both sides of the operator. Supported operators are: AND `&&`, OR `\|\|`. To define a filter on multiple attributes, use the `;` symbol to separate the various filter statements. The following is an example using the filter parameter: GET /api/v1/customtables/{tablename}/inputforms ?filter=currentname=Payee Setup
orderBy	This parameter provides sorting by column (multiple columns supported, comma delimited). The supported fields are; `id` and `currentname`. Direction: + (ascending), - (descending) Column: name For example, `orderBy=-id`
offset	Use this parameter combined with the `limit` parameter to return a group of records. By default, the offset is set to zero (0). For example, GET /api/v1/customtables/{tablename}/inputforms will return the first group of 50 rows starting with the first row and GET /api/v1/customtables/{tablename}/inputforms?offset=1 will return the next group of 50 rows.
limit	Use this parameter combined with the `offset` parameter to only return a group of records. By default, 50 rows are returned. For example, GET /api/v1/customtables/{tablename}/inputforms?offset=0&limit=100 will return the first 100 records and GET /api/v1/customtables/{tablename}/inputforms?offset=1&limit=100 will return the next 100 records.

In the response body, find the input from containing the data that you want to retrieve and note the ID.

[
    {
        "id": 3,
        "currentName": "Payee Setup",
        "basedOnTable": "Payee_"
    },
    {
        "id": 0,
        "currentName": "Payee_ Default",
        "basedOnTable": "Payee_"
    }
]

Retrieving table data

Next, use the ID obtained from the Get table data call to retrieve the table data from Varicent Incentives.

Make the following request:

GET /api/v1/customtables/{tablename}/inputforms/{inputformid}/data

For example,

curl -X GET -H "Authorization: Bearer API_KEY" 
-H "Model: YOUR_CLIENT_MODEL” 
-H "Content-Type: application/json" 
https://YOUR_ICM10_API_SERVER_ADDRESS/api/v1/customtables/Payee_/inputforms/3/data

Use the following parameters to filter the return response:

Parameter	Description
offset	Use this parameter with the `limit` parameter to return a group of records. By default, the offset is set to zero (0). For example, GET /api/v1/customtables/{tablename}/inputforms/{inputformid}/data will return the first group of 50 rows starting with the first row, and GET /api/v1/customtables/{tablename}/inputforms/{inputformid}/data?offset=1 will return the next group of 50 rows.
limit	Use this parameter with the `offset` parameter to return only a group of records. By default, 50 rows are returned. For example, GET /api/v1/customtables/{tablename}/inputforms/{inputformid} /data?offset=0&limit=100 will return the first 100 records, and GET /api/v1/customtables/{tablename}/inputforms/{inputformid}/data?offset=1&limit=100 will return the next 100 records.
filter	Supported fields are: all columns in the input form. The supported operations are: Equal matching `name=admin`. This is strict and case-sensitive. Starts with `name^admi`, or ends with `name$min`. This is case-insensitive. Equal matching within a list, such as `userType=Admin\\,Web`. This is case-insensitive. Interval matching, such as `date=[2015-09-22\\,2015-09-25]`, supports the mathematics notation for inclusive/exclusive bounds, `[a\\,b]`, `(a\\,b)`, or a mix `(a\\,b]`. Intervals also support open-ended bounds, `(a\\,]`. Not equal matching `name<>admin`. This is strict and case-sensitive. Is contained in `name⊇dmi`. This is case insensitive. NULL checks, such as `name=NULL` or `name<>NULL`. Only equal and not equal operators are supported. You can use multiple filters on one attribute. The filter query also supports the use of an operator. Complete filter definitions, as described above, are expected on both sides of the operator. The column filtered must be the same on both sides of the operator. Supported operators are: AND `&&`, OR `\|\|`. To define a filter on multiple attributes, separate each filter statement with `;`. The following is an example using the filter parameter: GET /api/v1/customtables/{tablename}/inputforms/ {inputformid}/data?filter=PayeeID_=AE001
fields	This parameter is used to limit the output results to selected columns. Use a comma delimited list of fields that you want to display. For example, `fields=PayeeID_`. Defaults to all available fields.
effectiveDate	This parameter is used for effective dated tables. You can leave this parameter empty to view all data or specify the date to view the data that is effective on that date. For tables without effective dates, this parameter is ignored.

In the response body, you will see the table data.

{
    "columnDefinitions": [
        {
            "name": "PayeeID_",
            "type": "String",
            "isKey": true,
            "nullable": false
        },
        {
            "name": "Name_",
            "type": "String",
            "isKey": false,
            "nullable": false
        },
        {
            "name": "Parent_",
            "type": "String",
            "isKey": false,
            "nullable": true,
            "values": "/api/v1/customtables/Payee_/descriptions/Parent_",
            "referencedCol": "PayeeID_"
        },
        {
            "name": "Salutation_",
            "type": "String",
            "isKey": false,
            "nullable": true,
            "values": "/api/v1/customtables/Salutation_/inputforms/0/data
               ?fields=Salutation_"
        },
        {
            "name": "Email_",
            "type": "String",
            "isKey": false,
            "nullable": false
        },
        {
            "name": "Phone_",
            "type": "String",
            "isKey": false,
            "nullable": false
        },
        {
            "name": "Extension_",
            "type": "String",
            "isKey": false,
            "nullable": false
        },
        {
            "name": "TitleID_",
            "type": "String",
            "isKey": false,
            "nullable": true,
            "values": "/api/v1/customtables/Payee_/descriptions/TitleID_",
            "referencedCol": "TitleID_"
        },
        {
            "name": "Reports_To_",
            "type": "String",
            "isKey": false,
            "nullable": true,
            "values": "/api/v1/customtables/Payee_/descriptions/Reports_To_",
            "referencedCol": "PayeeID_"
        },
        {
            "name": "Payee_Currency_",
            "type": "String",
            "isKey": false,
            "nullable": true,
            "values": "/api/v1/customtables/Payee_/descriptions/Payee_Currency_",
            "referencedCol": "CurrencyID_"
        },
        {
            "name": "Date_of_Hire_",
            "type": "Date",
            "isKey": false,
            "nullable": true
        },
        {
            "name": "Termination_Date_",
            "type": "Date",
            "isKey": false,
            "nullable": true
        },
        {
            "name": "Comment_",
            "type": "LongString",
            "isKey": false,
            "nullable": false
        },
        {
            "name": "Admin",
            "type": "String",
            "isKey": false,
            "nullable": true,
            "values": "/api/v1/customtables/Payee_/descriptions/Admin",
            "referencedCol": "Boolean"
        },
        {
            "name": "Language",
            "type": "String",
            "isKey": false,
            "nullable": true,
            "values": "/api/v1/customtables/Languages/inputforms/0/data
               ?fields=Language_Key"
        }
    ],
    "data": [
        [
            "AE001",
            "Dan Huddle",
            "SM001",
            null,
            "dhuddle@rti.com",
            "",
            "",
            "T004",
            "SM001",
            "USD",
            "2002-01-11T00:00:00",
            null,
            "",
            null,
            "ENG"
        ],
        [
            "AE002",
            "Paul McNeil",
            "SM001",
            null,
            "pmcneil@rti.com",
            "",
            "",
            "T004",
            "SM001",
            "USD",
            "1999-11-08T00:00:00",
            null,
            "",
            null,
            "FRE"
        ],

Retrieve table data tutorial

Prerequisites

Overview

Retrieving the table name

Note

Retrieving the input from ID

Retrieving table data

Search results