Postgres query result (List of lists) fills only the first column in form Table

Q&A
1

I am a new to java and Bonita (although not to BPM) and I am trying to display a sql query result in a standard Bonita UI table version 7.1 (using the Table widget). But all the data ends up in the most left column like this example below:

Result

What did I do to get to this?

I defined a postgresql dataconnector, that shows a working Array when testing:

List of Lists

The output (graphical n*n) is saved in a List type variable ‘carsandcolors’:

Java.util.list

And in the UI designer I defined a variable ‘Cars’ that receives the data from ‘carsandcolors’ through the API:

…/API/bpm/activityVariable/{{taskId}}/carsandcolors

I have made the following settings in de UI designer:

UI settings

Why are the subarrays listed in the first column only? And what can I do to fix this?

I have the feeling it has something to do with the Column Keys attribute in the UI designer, but I cannot find any information on how to use this attribute.

Any help is greatly appreciated!

2

You need to format the output data from the SQL from list array to JSON format, then everything will work…

You can look at the getting started guide Add Dummy Data for an example

regards
Sean

4

<deleted: double post>

5

Hi Stefandebruiji. Where did you write the groovy script? I defined a connector I did the query test and it works. Should I create a new variable and write the script?

6

Hi Stefandebruiji. Where did you write the groovy script? I defined a connector I did the query test and it works. Should I create a new variable and write the script?

7

Thanks stefandebruijn!
Do you guys have an idea about how to do a query depending on a UI designer selector option?

Imagine that you have a table with the fields “brand”, “color”, “year”, “type”, etc… So you want to filter the content of the table based in the option you’ve selected.

I’m thinking that I have to use the selector’s output, but I don’t know if I can do the interaction selector-connector in that way.

8

Why can’t we do that right from the connector?

9

Use this tool to check JSON: JSON validator
The variable “jsonb” must be defined after the While and also the line before of the “line” variable definition.

10

sorry, i know this post is very old, but i realy need help with de columnskey… I have the data with the json structure but i can show in a table… please help

11

In you connector is probably best:

Create a pool variable of type String
In your SQL Connector the very last dialog

newVariable takes value of (pencil) this is where you would add your code to JSON your response.

Then use newVariable in the REST call to show in the table…

regards

12

Hi,
I have the same problem with the list java variable and the tablet widget.
Ok, I understand that i have to format the output data from the SQL to JSON format, but where i put the java code to format the data? I can create a variable in the UI Designer but just for put an expression, not to put a code to format the variable.
I can put the formatted data in a Connector but I dont understand in which type of variable I have to put it.
I stuck with this problem.
Thank you for your help.

13

hi,

I tried to apply the format json to my variable, but doesn’t work :frowning:

this is what I did:

Create a new pool variable ‘milistajson’ (String type):
1

and take the values from the last of connector:
2

from this follow code: (take from Add Dummy Data excample:
3

this is the test for connector with the json format value:
4

in the UI Designer, I declared the miLista2 varible (API external):
5

and put a text to verify the value in runtime time:
6

I configured the tablet widget :
7

here the result, doesn’t work, i don´t know what are i doing wrong :frowning:
8

Thanks for your help

14

Can you post the JSON, or at least check it with a JSON Checker, for example here:

http://jsonlint.com/, or here
https://jsonformatter.curiousconcept.com/

Maybe the JSON is invalid…

If you post the JSON I can try it here in a table,

regards

15

Excellent Diagram by the way…

The JSON you posted is invalid according to the JSON Formatters…

Could this be the problem?

You are missing a {

regards

16

Hi Sean,

Thanks for your reply. However, it is still not working…

clueless

testdata: [{“brand”:“Ferrari”,“color”:“Red”},{“brand”:“Ford”,“color”:“Black”},{“brand”:“Toyota”,“color”:“White”}]

17

Thanks for your reply.

It was just a copy/paste error… Sorry.

I fixed it in the previous post.

18

thank you Sean McP for your support.

Stefandebruijin, sorry for intruding on your post. We have the same problem. :frowning:

here I export my proyect with the same problem of stefandebruijin’s project.:
link
Pass: !9M1SlvE37rG-_E_XIN290F5562uP29AHW1FZM7E12Mg

a lot of thanks.

19

Hi robin.perner and Sean McP,

I found it! The string has to be transformed to a list of map objects. You can use the groovy JsonBuilder function for it.

I got to the following result:

result

With this script:
script1

It works on an table as well, with this:
select
script2

Let me please know if it works.

Sean, many thanks for your help!

Regards, Stefan

20

Hi Stefan,

Thank you so much for your help!!. I tried many many different ways for do it, but nothing worked :(.
This time Works fine, only I have to adjust the variable type, with java.util.collection type in the process variables panel, and that’s all.

Regards Robin