Problem while Adding or Editing Rows in a Dataset via API

Developer Experience
1

Hi everyone,
short question to using the API with Postman. We are actually writing a Mobile App with ionic Framework as remote control for our Xibo Implementation. We need to add and edit or remove a row in a DataSet.

What i actually try to accomplish is Adding a Row to a Dataset filled with some Data using Postman as a test System.

The API Call from my side looks as Follows.

trying to POST on {{url}}/api/dataset/data/11 where 11 is my DataSet ID.

In the documentation (swagger) is only one Key Value Pair Submitted in body as dataSetColumnId_ID.

DataSet 11 has some Columns like “emergency” which is a Boolean and “reason” which is a string.

The Data sent is formatted as form-data.

No matter what i try as value even if i try to submit the data in format {‘emergency’:True, ‘reason’:‘reason1’}, anything i get is an empty new column and a ColumnID as json response.

Maybe a simple question for those API Devs beyond us, i don’t really get a clue out of the swagger documentation.

So what is the right way of adding a row to a dataset filled with data. Or do i need to add the row and after that i need to edit the data in that data row? This would be a waste of API Calls i guess.

Cheers and thanks a lot.
Jan

2

Ok, let’s proceed under the assumption that you have your dataset with all columns etc already set up.

In API you will first want to know your columnsId ie you will use the following call
{{url}}/api/dataset/59/column

Where 59 is my dataSet id (visible in web ui as well, if you do not know your dataSet Id you can use the GET dataset call first).

Example response of the call above:

[
    {
        "dataSetColumnId": 135,
        "dataSetId": 59,
        "heading": "Date",
        "dataTypeId": 1,
        "dataSetColumnTypeId": 1,
        "listContent": null,
        "columnOrder": "1",
        "formula": null,
        "dataType": "String",
        "dataSetColumnType": "Value"
    },
    {
        "dataSetColumnId": 136,
        "dataSetId": 59,
        "heading": "Location",
        "dataTypeId": 1,
        "dataSetColumnTypeId": 1,
        "listContent": null,
        "columnOrder": "2",
        "formula": null,
        "dataType": "String",
        "dataSetColumnType": "Value"
    },
    {
        "dataSetColumnId": 137,
        "dataSetId": 59,
        "heading": "Description",
        "dataTypeId": 1,
        "dataSetColumnTypeId": 1,
        "listContent": null,
        "columnOrder": "3",
        "formula": null,
        "dataType": "String",
        "dataSetColumnType": "Value"
    },
    {
        "dataSetColumnId": 138,
        "dataSetId": 59,
        "heading": "Time",
        "dataTypeId": 1,
        "dataSetColumnTypeId": 1,
        "listContent": null,
        "columnOrder": "4",
        "formula": null,
        "dataType": "String",
        "dataSetColumnType": "Value"
    }
]

Now, we know all the dataSetColumnId we need, so we proceed to the next call (add row):
{{url}}/api/dataset/data/59

Where 59 is, again, my datasetId.

In the body of this call you need to pass parameters for each column that should have some value in this row, you do not need to pass value to every available column if you don’t want to you can even don’t pass any parameters at all it will then just add an empty row to the dataSet.

As you can see in the code response above my columns have the Ids 135,136,137 and 138

The body of this call in my example will look like this:

In a response you will see (in this example):

{
    "id": 4
}

Which is the rowId of the row that I’ve just added.

Now if we look in the datasets in web ui, you will see the following:

image.png1553×78 2.77 KB

Which means that I’ve successfully added a new row with the required values via API call.

If you want to edit existing row in the dataSet, then again you’d need to GET the rowId
and then use PUT /dataset/data/{dataSetId}/{rowId} call with the same logic as shown above for POST row call.

Hope that helps.

1 Like
3

Thank you Peter,

where i can find the rowid?
I want use PUT but wehen i add the dataSetColumnID and use PUT to Edit a Entry then comes a Failure.

Can you Help me?

4

In the Web ui the ID (as in my last screenshot) or GET /dataset/data/{dataSetId} API call.

1 Like
5

Thank you Peter it works fine :slight_smile:

6

I followed your PUSH format to try making changes to my row.

image.png561×575 25.2 KB

Here’s my column data

[
{
    "dataSetColumnId": 8,
    "dataSetId": 3,
    "heading": "item",
    "dataTypeId": 1,
    "dataSetColumnTypeId": 1,
    "listContent": null,
    "columnOrder": "1",
    "formula": null,
    "dataType": "String",
    "dataSetColumnType": "Value"
},
{
    "dataSetColumnId": 9,
    "dataSetId": 3,
    "heading": "price",
    "dataTypeId": 1,
    "dataSetColumnTypeId": 1,
    "listContent": null,
    "columnOrder": "2",
    "formula": null,
    "dataType": "String",
    "dataSetColumnType": "Value"
},
{
    "dataSetColumnId": 12,
    "dataSetId": 3,
    "heading": "type",
    "dataTypeId": 1,
    "dataSetColumnTypeId": 1,
    "listContent": null,
    "columnOrder": "3",
    "formula": null,
    "dataType": "String",
    "dataSetColumnType": "Value"
},
{
    "dataSetColumnId": 11,
    "dataSetId": 3,
    "heading": "discount",
    "dataTypeId": 1,
    "dataSetColumnTypeId": 1,
    "listContent": null,
    "columnOrder": "4",
    "formula": null,
    "dataType": "String",
    "dataSetColumnType": "Value"
},
{
    "dataSetColumnId": 10,
    "dataSetId": 3,
    "heading": "remarks",
    "dataTypeId": 1,
    "dataSetColumnTypeId": 1,
    "listContent": null,
    "columnOrder": "5",
    "formula": null,
    "dataType": "String",
    "dataSetColumnType": "Value"
},
{
    "dataSetColumnId": 14,
    "dataSetId": 3,
    "heading": "Others",
    "dataTypeId": 1,
    "dataSetColumnTypeId": 1,
    "listContent": "Yes, No",
    "columnOrder": "6",
    "formula": null,
    "dataType": "String",
    "dataSetColumnType": "Value"
},
{
    "dataSetColumnId": 15,
    "dataSetId": 3,
    "heading": "Status",
    "dataTypeId": 1,
    "dataSetColumnTypeId": 1,
    "listContent": null,
    "columnOrder": "7",
    "formula": null,
    "dataType": "String",
    "dataSetColumnType": "Value"
}

]

The response from Postman is same as you posted. But the row remains unchanged…

image.png1380×560 20.6 KB

I still don’t understand which part I am wrong.

7

you will want to pass that as x-www-form-urlencoded

all PUT calls should be like that.

I did not post a Edit call example, at least not in this topic.

Edit, now I did:

1 Like
8

I tried using x-www-form-urlencoded to pass data, still not work…
Is this problem related to Postman App or something else?

image.png565×569 25.4 KB

Result:

image.png1229×362 9.67 KB

And I found that even I input another {rowId} which doesn’t exist. It still responses normally…

image.png567×547 23.7 KB

9

Please, anyone can help in this issue?
I tried another dataset with only 1 column and the API works on editing its item…
I don’t understand why the response always is the id even it is not existing.

10

Could you check if you’re using correct column Ids please?
I don’t really see what else could be wrong in your call - although 293 as row is obviously incorrect, and perhaps it should validate if the row exists in this case

BEFORE:

image.png2134×106 3.61 KB

CALL:

image.png977×621 31 KB

AFTER:

image.png1965×111 3.15 KB

11

Thanks Peter for assistant.
Sure, I checked the column Ids many times before…
And they are all String value type too.

[
    {
        "dataSetColumnId": 8,
        "dataSetId": 3,
        "heading": "Item",
        "dataTypeId": 1,
        "dataSetColumnTypeId": 1,
        "listContent": null,
        "columnOrder": "1",
        "formula": null,
        "dataType": "String",
        "dataSetColumnType": "Value"
    },
    {
        "dataSetColumnId": 9,
        "dataSetId": 3,
        "heading": "Price",
        "dataTypeId": 1,
        "dataSetColumnTypeId": 1,
        "listContent": null,
        "columnOrder": "2",
        "formula": null,
        "dataType": "String",
        "dataSetColumnType": "Value"
    },
    {
        "dataSetColumnId": 12,
        "dataSetId": 3,
        "heading": "Type",
        "dataTypeId": 1,
        "dataSetColumnTypeId": 1,
        "listContent": null,
        "columnOrder": "3",
        "formula": null,
        "dataType": "String",
        "dataSetColumnType": "Value"
    },
    {
        "dataSetColumnId": 11,
        "dataSetId": 3,
        "heading": "Discount",
        "dataTypeId": 1,
        "dataSetColumnTypeId": 1,
        "listContent": null,
        "columnOrder": "4",
        "formula": null,
        "dataType": "String",
        "dataSetColumnType": "Value"
    },
    {
        "dataSetColumnId": 10,
        "dataSetId": 3,
        "heading": "Remarks",
        "dataTypeId": 1,
        "dataSetColumnTypeId": 1,
        "listContent": null,
        "columnOrder": "5",
        "formula": null,
        "dataType": "String",
        "dataSetColumnType": "Value"
    },
    {
        "dataSetColumnId": 15,
        "dataSetId": 3,
        "heading": "Status",
        "dataTypeId": 1,
        "dataSetColumnTypeId": 1,
        "listContent": null,
        "columnOrder": "7",
        "formula": null,
        "dataType": "String",
        "dataSetColumnType": "Value"
    }
]

But the response always shows “true” if the dataset id is correct.

{
    "grid": false,
    "success": true,
    "status": 200,
    "message": "Edited Row",
    "id": "29",
    "data": {
        "id": "29"
    }
}

Is it a bug of this system? I tried create a new dataset and still not able to edit it by API.
Otherwise should I reinstall the entire CMS?

12

I can take a look at your CMS and perhaps see if the postman call will work for me, if you’d want me to have a look, please send me access credentials via private message.

13

I am sorry that I am not able to allow you to access my CMS since the server is company’s property.
But the problem is still not yet solved… I tried using other application call this API, still not able to edit the row.
Maybe I should reinstall the entire CMS and upgrade to latest version too.

14

Sure, I understand.

You said this earlier

Is there anything specific about this dataSet anything different from the one that does not work for you? (apart from it having 1 column)

If you add a second column to that dataSet which did work for you and then try to change the row’s values via API does that still works?

I’m not sure about re-installing whole CMS, if it works fine and only this one specific call gives you problems, updating to 1.8.3 could be a good idea though.

1 Like
15

There’s nothing special in that dataset, that is created with only 1 column.
I tried add a second column, the API still works.
I guess that dataset might be “damaged” as I added and deleted rows/columns many times

16

By the way, that defective dataset was once added with CODE.
It is said that the CODE to be used for API, but how can we use this it?

17

The dataSet ‘code’ can be used as a filter in the GET dataSet call, that’s all there is to it really.

It should not have any impact on the calls adding/editing the data in the dataSet - just to confirm I’ve run a little test.

Perhaps there is some other problem with that specific dataSet, but it’s hard to tell at this point what it could be.

1 Like