Lookup Tables

Use the endpoints below to manage lookup tables programmatically.

Quick reference

Type	Resource	Description
GET	/api/managed_users/:managed_user_id/lookup_tables	List tables.
GET	/api/managed_users/:managed_user_id/lookup_tables/ :lookup_table_id/lookup	Look up a row.
GET	/api/managed_users/:managed_user_id/lookup_tables/ :lookup_table_id/rows	List rows.
GET	/api/managed_users/:managed_user_id/lookup_tables/ :lookup_table_id/rows/:row_id	Get a row.
POST	/api/managed_users/:managed_user_id/lookup_tables/ :lookup_table_id/rows	Add a row.
POST	/api/managed_users/:managed_user_id/lookup_tables	Create a new lookup table.
POST	/api/managed_users/:managed_user_id/lookup_tables/batch_delete	Delete lookup tables in batch.
PUT	/api/managed_users/:managed_user_id/lookup_tables/ :lookup_table_id/rows/:row_id	Update a row.
DELETE	/api/managed_users/:managed_user_id/lookup_tables/ :lookup_table_id/rows/:row_id	Delete a row.
GET	/api/managed_users/:managed_user_id/lookup_tables/ :lookup_table_id/rows/:row_id	Get a row.

List lookup tables

Returns a list of lookup tables belonging to a customer. Workato includes the project_id of the project to which the lookup table belongs in the response.

GET /api/managed_users/:managed_user_id/lookup_tables

URL Parameters

Name	Type	Description
managed_user_id	string required	Embedded customer ID/external ID. External ID must be prefixed with an E (for example, EA2300) and the resulting ID should be URL encoded.

Query Parameters

Name	Type	Description
page	integer	Page number. Defaults to 1.
per_page	integer	Page size. Defaults to 100 (maximum is 100).

Sample request

curl  -X GET 'https://www.workato.com/api/managed_users/157/lookup_tables' \
      -H 'Authorization: Bearer <api_token>' \
      -H 'Content-Type: application/json' \

Response

{
  "result": [
    {
      "id": 1315,
      "name": "lookup_table4",
      "schema": "[{\"control_type\":\"text\",\"label\":\"code\",\"name\":\"col1\",\"type\":\"string\",\"sticky\":true},{\"control_type\":\"text\",\"label\":\"name\",\"name\":\"col2\",\"type\":\"string\",\"sticky\":true},{\"control_type\":\"text\",\"label\":\"col3\",\"name\":\"col3\",\"type\":\"string\",\"sticky\":false},{\"control_type\":\"text\",\"label\":\"col4\",\"name\":\"col4\",\"type\":\"string\",\"sticky\":false},{\"control_type\":\"text\",\"label\":\"col5\",\"name\":\"col5\",\"type\":\"string\",\"sticky\":false}]",
      "created_at": "2022-05-20T11:54:26.934-07:00",
      "updated_at": "2022-05-20T11:54:26.934-07:00",
      "project_id": "523144"
    }
  ]
}

Look up a row

Finds the first row matching the given criteria in the lookup table. Returns a 404 when the lookup fails.

GET /api/managed_users/:managed_user_id/lookup_tables/:lookup_table_id/lookup

URL parameters

Name	Type	Description
managed_user_id	string required	Embedded customer ID/external ID. External ID must be prefixed with an E (for example, EA2300) and the resulting ID should be URL encoded.
lookup_table_id	integer required	Lookup table id

Query parameters

Name	Type	Description
by[<col name>]	string required	Lookup criteria. Format the column name as follows: by[<col name>]. To match by multiple columns, provide multiple parameters. Refer to the sample request for more details.

Sample request

curl  -X GET https://www.workato.com/api/managed_users/157/lookup_tables/1296/rows/942?by[code]=US \
      -H 'Authorization: Bearer <api_token>' \
      -H 'Content-Type: application/json'

Response

{
  "id": 942,
  "data": {
    "code": "US",
    "name": "United States",
    "col3": null,
    "col4": null,
    "col5": null
  },
  "created_at": "2022-05-20T11:50:32.986-07:00",
  "updated_at": "2022-05-20T11:50:40.986-07:00"
}

List rows

Returns a lists of rows from the lookup table. Supports filtering and pagination.

GET /api/managed_users/:managed_user_id/lookup_tables/:lookup_table_id/rows

URL parameters

Name	Type	Description
managed_user_id	string required	Embedded customer ID/external ID. External ID must be prefixed with an E (for example, EA2300) and the resulting ID should be URL encoded.
lookup_table_id	integer required	Lookup table id

Query parameters

Name	Type	Description
page	integer	Page number. Defaults to 1.
per_page	integer	Page size. Defaults to 500 (maximum is 1000).
by[<col name>]	string	Filter criteria. The column name should be provided as follows: by[<col name>]. To match by multiple columns, provide multiple parameters. Refer to the sample request for more details. When not supplied, all rows are returned.

Sample request

Request 1: list rows

curl  -X GET https://www.workato.com/api/managed_users/157/lookup_tables/1296/rows \
      -H 'Authorization: Bearer <api_token>' \
      -H 'Content-Type: application/json'

Request 2: filter and list rows

curl  -X GET https://www.workato.com/api/managed_users/157/lookup_tables/1296/rows?by[code]=US \
      -H 'Authorization: Bearer <api_token>' \
      -H 'Content-Type: application/json'

Response

Response 1: list rows

{
       "result": [
            {
              "id": 941,
              "data": {
                "code": "IND",
                "name": "India",
                "col3": null,
                "col4": null,
                "col5": null
              },
              "created_at": "2022-06-15T11:50:32.986-07:00",
              "updated_at": "2022-06-15T11:50:40.986-07:00"
            },
            {
              "id": 942,
              "data": {
                "code": "US",
                "name": "United States",
                "col3": null,
                "col4": null,
                "col5": null
              },
              "created_at": "2022-05-20T11:50:32.986-07:00",
              "updated_at": "2022-05-20T11:50:40.986-07:00"
            }
      ]
}      

Response 2: filter and list rows

{
       "result": [
            {
              "id": 942,
              "data": {
                "code": "US",
                "name": "United States",
                "col3": null,
                "col4": null,
                "col5": null
              },
              "created_at": "2022-05-20T11:50:32.986-07:00",
              "updated_at": "2022-05-20T11:50:40.986-07:00"
            }
      ]      
}

Get a row

Get a row from a lookup table.

GET /api/managed_users/:managed_user_id/lookup_tables/:lookup_table_id/rows/:row_id

URL parameters

Name	Type	Description
managed_user_id	string required	Embedded customer ID/external ID. External ID must be prefixed with an E (for example, EA2300) and the resulting ID should be URL encoded.
lookup_table_id	integer required	Lookup table id
row_id	integer required	Row id

Sample request

curl  -X GET https://www.workato.com/api/managed_users/157/lookup_tables/1296/rows/942 \
      -H 'Authorization: Bearer <api_token>' \
      -H 'Content-Type: application/json'

Response

{
  "id": 942,
  "data": {
    "code": "US",
    "name": "United States",
    "col3": null,
    "col4": null,
    "col5": null
  },
  "created_at": "2022-05-20T11:50:32.986-07:00",
  "updated_at": "2022-05-20T11:50:40.986-07:00"
}

Add a row

Add a row to the lookup table.

POST /api/managed_users/:managed_user_id/lookup_tables/:lookup_table_id/rows

URL parameters

Name	Type	Description
managed_user_id	string required	Embedded customer ID/external ID. External ID must be prefixed with an E (for example, EA2300) and the resulting ID should be URL encoded.
lookup_table_id	integer required	Lookup table id

Payload

Name	Type	Description
data	Hash required	The hash contains the data for the new row.

Sample request

curl  -X POST https://www.workato.com/api/managed_users/157/lookup_tables/1296/rows \
      -H 'Authorization: Bearer <api_token>' \
      -H 'Content-Type: application/json' \
      -d '{ "data": { "name": "United States", "code": "USA" }}'

Response

{
  "id": 942,
  "data": {
    "code": "USA",
    "name": "United States",
    "col3": null,
    "col4": null,
    "col5": null
  },
  "created_at": "2022-05-20T11:50:32.986-07:00",
  "updated_at": "2022-05-20T11:50:32.986-07:00"
}

Create a new lookup table

Create a new lookup table. Depending on your requirements, you can choose to make the lookup table available for general access across your workspace or limit its scope to a specific project.

POST /api/managed_users/:managed_user_id/lookup_tables 

URL parameters

Name	Type	Description
managed_user_id	string required	Embedded customer ID/external ID. The External ID must be prefixed with an E (for example, EA2300) and the resulting ID should be URL encoded.

Payload

Name	Type	Description
name	string required	Provide a name for your new lookup table.
project_id	integer optional	Specify a project_id to scope the lookup table to a specific project. If you do not provide a project_id, the lookup table's scope is global. Use the list projects API to obtain a list of projects in your workspace.
schema	hash required	Determine the structure of your lookup table by supplying a schema and specifying a name for each column in your table, for example: [{ "label": "Name" }]. Lookup tables support a maximum of ten columns.

Sample request

curl  -X POST https://www.workato.com/api/managed_users/121/lookup_tables \
      -H 'Authorization: Bearer <api_token>' \
      -H 'Content-Type: application/json' \
      -d '{
            "lookup_table": {
            "name": "Contacts",
            "project_id": 4321,
            "schema": [{ "label": "ID" }, { "label": "Name" }, {"label": "CSM"}, {"label": "Email"}, {"label": "Phone number"}, {"label": "Priority"}, {"label": "Tier"}, {"label": "Notes"}, {"label": "Created at"}, {"label": "Updated at"}]
            }
          }'

Response

{
    "id": 2372,
    "name": "Contacts",
    "schema": "[{\"control_type\":\"text\",\"label\":\"ID\",\"name\":\"col1\",\"type\":\"string\",\"sticky\":true},{\"control_type\":\"text\",\"label\":\"Name\",\"name\":\"col2\",\"type\":\"string\",\"sticky\":true},{\"control_type\":\"text\",\"label\":\"CSM\",\"name\":\"col3\",\"type\":\"string\",\"sticky\":true},{\"control_type\":\"text\",\"label\":\"Email\",\"name\":\"col4\",\"type\":\"string\",\"sticky\":true},{\"control_type\":\"text\",\"label\":\"Phone number\",\"name\":\"col5\",\"type\":\"string\",\"sticky\":true},{\"control_type\":\"text\",\"label\":\"Priority\",\"name\":\"col6\",\"type\":\"string\",\"sticky\":true},{\"control_type\":\"text\",\"label\":\"Tier\",\"name\":\"col7\",\"type\":\"string\",\"sticky\":true},{\"control_type\":\"text\",\"label\":\"Notes\",\"name\":\"col8\",\"type\":\"string\",\"sticky\":true},{\"control_type\":\"text\",\"label\":\"Created at\",\"name\":\"col9\",\"type\":\"string\",\"sticky\":false},{\"control_type\":\"text\",\"label\":\"Updated at\",\"name\":\"col10\",\"type\":\"string\",\"sticky\":false}]",
    "project_id": 4321,
    "created_at": "2023-09-11T11:31:35.335-07:00",
    "updated_at": "2023-09-11T11:31:35.335-07:00"
}

Delete lookup tables in batch

Use this endpoint to delete lookup tables in batch.

POST /api/managed_users/:managed_user_id/lookup_tables/batch_delete

URL parameters

Name	Type	Description
managed_user_id	string required	Embedded customer ID/external ID. The External ID must be prefixed with an E (for example, EA2300) and the resulting ID should be URL encoded.

Payload

Name	Type	Description
ids	hash required	Include the ID(s) of the lookup table(s) you plan to delete.

Sample request

curl  -X POST https://www.workato.com/api/managed_users/121/lookup_tables/batch_delete \
      -H 'Authorization: Bearer <api_token>' \
      -H 'Content-Type: application/json' \
      -d '{ "ids": [1234, 1235, 1236] }'

Response

{
    "data": {"deleted": [1234, 1235, 1236] },
    "errors": []
}

Update a row

Updates a row in the lookup table.

PUT /api/managed_users/:managed_user_id/lookup_tables/:lookup_table_id/rows/:row_id

URL parameters

Name	Type	Description
managed_user_id	string required	Embedded customer ID/external ID. External ID must be prefixed with an E (for example, EA2300) and the resulting ID should be URL encoded.
lookup_table_id	integer required	Lookup table id
row_id	integer required	Row id

Payload

Name	Type	Description
data	Hash required	The hash containing the data for the updated row. Only the columns provided are updated.

Sample request

curl  -X PUT https://www.workato.com/api/managed_users/157/lookup_tables/1296/rows/942 \
      -H 'Authorization: Bearer <api_token>' \
      -H 'Content-Type: application/json' \
      -d '{ "data": { "code": "US" }}'

Response

{
  "id": 942,
  "data": {
    "code": "US",
    "name": "United States",
    "col3": null,
    "col4": null,
    "col5": null
  },
  "created_at": "2022-05-20T11:50:32.986-07:00",
  "updated_at": "2022-05-20T11:50:40.986-07:00"
}

Delete a row

Delete a row from the lookup table

DELETE /api/managed_users/:managed_user_id/lookup_tables/:lookup_table_id/rows/:row_id

URL parameters

Name	Type	Description
managed_user_id	string required	Embedded customer ID/external ID. External ID must be prefixed with an E (for example, EA2300) and the resulting ID should be URL encoded.
lookup_table_id	integer required	Lookup table id
row_id	integer required	Row id

Sample request

curl  -X DELETE https://www.workato.com/api/managed_users/157/lookup_tables/1296/rows/942 \
      -H 'Authorization: Bearer <api_token>' \
      -H 'Content-Type: application/json'

Response

{
  "success": true
}