Internal Error when applying two filters to Tulip API Table Query

Support, Troubleshooting, & Help
1

Hi, I am attempting to extract table records from tulip into node-red using the TulipAPI. When applying two filters to this I get an internal server error, status code 500. I believe the request to be properly formed and using only one filter does not throw up the same issue. Is anyone able to advise? I have pasted the request url below and the response.

https://-----/api/v3/tables/skLZrjDB5NijBdoGK/records/?limit=100&filters=[{“field”:“megyt_location”,“arg”:“Line”,“functionType”:“equal”},{“field”:“jpmfg_part_id_unlinked”,“arg”:“P_4776”,“functionType”:“equal”}]&filterAggregator=all

{“errorCode”:“InternalServerError”,“details”:“Internal Server Error”,“errorUniqueID”:“oVgs5rZC744mq9jsLiocJaACszNnOMqmHSWVu5RAipw”}

2

Hey @TomReynolds98,

I found our documentation to be lacking on this - Thanks for brining this up, I have made an action to resolve this. The way you need to add filters is below:

payload = {"limit": limit, 
    "offset": offset, 
    "includeTotalCount": "true", 
    "sortBy": "_updatedAt",
    "filters.0.field":"kzmxn_expected_time",
    "filters.0.functionType":"greaterThanOrEqual",
    "filters.0.arg":15}

Notice how I don’t pass a whole json encoded object to the filter attribute, I pass each individual filter as standard attributes.

I found this confusing (or just completely lacking of documentation) the first time I did this too. A second filter could be added with the code:

"filters.1.field":"kzmxn_line_number",
    "filters.1.functionType":"greaterThanOrEqual",
    "filters.1.arg":3

Does this resolve what you’re seeing?
Pete

3

Hi Pete,
I have placed a function node in front of the tulip api call that uses the format above,

payload =
{
“limit”: 100,
“offset”: 0,
“includeTotalCount”: “true”,
“sortBy”: “_updatedAt”,
“filters.0.field”:“megyt_location”,
“filters.0.functionType”:“equal”,
“filters.0.arg”:“Line”,
“filters.1.field”:“jpmfg_part_id_unlinked”,
“filters.1.functionType”:“equal”,
“filters.1.arg”:“P_4776”
}
return msg;

If I use payload at the start it returns the whole table, i.e. the filters aren’t present
If I use msg.filters at the start (seemed logical potential option), I get error code 400 and Invalid Query String, “Unable to parse query string: error parsing query value for filters as JSON: invalid character ‘o’ looking for beginning of value”.

image
image696×96 4.67 KB

Hope that makes sense, if you have a 2 filter set of nodes working could I potentially get the exported JSON of the flow to edit and try on mine?

Thanks,
Thomas

4

Hey @TomReynolds98,

Sorry, my code snippet was pulled out of python instead of NR. Should only need one small change.

The tulip-tables node takes the payload on the msg.body attribute. so if you change your function to this you should be good to go:

msg.body =
{
“limit”: 100,
“offset”: 0,
“includeTotalCount”: “true”,
“sortBy”: “_updatedAt”,
“filters.0.field”:“megyt_location”,
“filters.0.functionType”:“equal”,
“filters.0.arg”:“Line”,
“filters.1.field”:“jpmfg_part_id_unlinked”,
“filters.1.functionType”:“equal”,
“filters.1.arg”:“P_4776”
}
return msg;

Wanna give this a go and let me know if you still have errors?
Pete

5

Hey @TomReynolds98 -

Talked more with our hardware team (the NR masters internally). Attached is a flow you can import that is the way they would approach filtering. Turns out the tulip-tables node does some of the filter encoding automatically, so it can be done on the msg.filter attribute. So for example-

msg = { 
    "tableId": "theirTableID",
    "limit": 100,
    "offset": 0,
    "includeTotalCount": true,
    "sortBy": "_updatedAt"
};
msg.filters = [{
    "field": "megyt_location",
    "functionType": "equal",
    "arg": "Line"
}, {
    "field": "jpmfg_part_id_unlinked",
    "functionType": "equal",
    "arg": "P_4776"
}];

Apologies! Didn’t realize the nodes were smarter than the normal api calls.

Attached is a flow we just tested in the QA lab
Filter.json (2.8 KB)

6

Hi Pete,

Was just working through the first one and that unfortunately this still gives the whole table being returned.

And then, doing the second one., I’ve imported your nodes and edited for my table and this also throws up internal server error, code 500.

However when I apply this method to another table the double filter does work as intended!

I wonder if there is something the matter with the table itself that’s throwing up the issue?

I’m happy to mark the above as the solution as it does work, just not for this specific table it seems.

Thanks,
Thomas

7

In fact, It is now working on this table also so all good!

Thanks for all your efforts!
Thomas

8

Hi Pete,
How dynamic filters should apply for tables, like
msg.filters = [{
“field”: “megyt_location”,
“functionType”: “equal”,
“arg”: “Line”---------- //In arg i want to compare this with another table column.
},];

9

Hey @NitinKolhe -

Check out this thread, where I give some examples of how to do filtering on the tables api from node red.

Pete