Updating or inserting values into a ListString data type field via API

[ClayG]

I have recently started to use the Caspio Rest API to update and insert records into my Caspio tables. I'm using Tray.io as the middleware between my other systems.

All is well as long as my column types are things like Text(255), Date/Time, Yes/No, etc. But if I want a column type in Caspio to be ListString, I'm stuck since my data source from the other system for those columns is more like Text(255). Sample:   gradeLevelsTaught: "middle", "upper"

If I want my destination column in Caspio to be ListString, I would have to transform the data from the other system into something like: 

"gradeLevelsTaught": {
                "2": "middle",
                "4": "upper" }

Does that sound right? Unfortunately, the Tray.io platform I'm using would make that kind of transformation very difficult. I'm wondering if others have attempting something like this before?

P.S. I'm not a developer or programmer, but I have a technical background.

 

[ClayG]

In case anyone else searches for a similar topic, I decided it's just best to avoid the ListString data type, especially since I'm doing an API integration. And I'll use the Listbox display type in my datapages along with the "allow multi-select" option to achieve a similar end result as the ListString data type provides.

 

[CoopperBackpack]

Hello @ClayG,

I agree that it is better to avoid using the List data type in more or less complex workflows. 

This data type has a number of restrictions, e.g. it is impossible to use it in Charts, Pivot Tables, Grouping, Aggregations, Formula Fields, and Calculated Fields.

 

I would like to share some points regarding List data types and REST API for future reference. 

1) Syntax example to add a field with List data type to the Table.

 

{
"Name": "Cities",
"Type": "List-String",
"Unique": false,
"DisplayOrder": 0,
"ListField":["London", "Paris", "New York"]
} 

2) As far as I know, the list of values in the List data type field is the subtable. Values are stored in this subtable in the format:

"Cities": {
         "1": "London",
         "2": "New York",
          "3": "Paris"
}

If this field was added to the Table via REST API, the values are sorted in ascending order and indexes corresponding to the ordinal number are applied.

 

3) To insert(POST) or update(PUT) this field within the record one needs to refer to the value by referring to its index.

Example:

The same logic can be used for this PUT method:

 

4) However, we cannot reference the values that are stored in the subtable (we can reference them by index, but not by value).

Therefore, it is impossible to use the List data type field in the WHERE clause (for example, I need to search for the records where the city has 'New' in the name: New Delhi, New York, New Orleans, etc.) 
It is impossible to use the following syntax:

So, in the GET requests the List data type field just can be used in the list of fields to select from the Table. 

[ClayG]

Thanks, that's great advice and further reason for me to avoid that data type.