Manipulation list of lists from sql query before listview

[gipsea]

Dear all,

I've checked the community without luck about similar question.

I'd like to use listview to show the records from a query which I believe (still new with AI2) is a list of lists
every single list shows [ list ]. My list has these fields:

• id

[ABG]

You're in luck.

I just finished testing a collapsible listView structure browser, all in blocks.

You can adjust your copy to load your JSON or structure.

Sample run:

Source:
collapsibleStructView.aia (14.2 KB)

Blocks:

[gipsea]

Hey, @ABG ,

this was quick. I by mistake posted it and before finishing to edit I already have some options.

I'll take a look and revert in case further questions

Appreciate your help!!!

[ABG]

Also see

FAQ Section: JSON and Dictionaries

The new Dictionaries docs - MIT The Web block to extract a dictionary from JSON (draggable) [component_method] Blocks based JSON Browser - ABG Blocks based JSON Filter and Lookup (2018, needs dictionary update) - ABG Extracting data from JSON text (2016, needs refesh for dictionaries) JSONTools extension - Luke Gackle Web Component – JSON Response On App Inventor - Sajal Dutta JSON Path Finder - @Luc_Volders How to Prettify JSON with JavaScript - @Kevinkun Collapsible JSON WebViewer -…

for more tools.

[gipsea]

OK.

I gave a quick look at the project and it is not exavtly wat I was after.

At the moment, after query, letsay the listview shows:

[1,name1,name2]
[2,name3,name4]
etc

and I'd like to see somethinig like:

name1 BY name2
name3 BY name4
etc.

the point is that I will still need the id of the selection to be stored in tinydb.

As shortcut I can also accept to show something like:

1: name1 BY name2
2: name3 BY name4

and split the text after selction using " : " generating a list of 2 element (this is my guess but never done in AI before so I can be completely wrong

Thanks

[ABG]

You can do that using a single advanced list block

Feed it the table (list of lists), and emit a text JOIN of the selected items from each row, with boiler plate between them.

[gipsea]

I saw it. I'm not too familiar with this block.

By all mean I could see the way how it could change the list format, evenctually not showing the ID but still keeping in the information.

My aim, if it make sense, is that:

1. every entry of the query result is shown with my format, let say nameX by nameY

2. when I select one of the option the selction will still be the original list so I use each fields as it is required

I'm considering using "somehow" a for each loop but I don't know how to show the text formatted and select the list

[ABG]

(draggable)

Set ListView1.Elements to Elements(your table)

[gipsea]

WOW!!!

starting from my limited knowledge of AI your example has opened a new universe in the procedure block!!!!

I will be able to check later but as quick feedback, using this procedure then there will be no link between ID of the query result (let say row[1] ) to the new list used by listview beside each position in the first list has the same position in the second. Correct?

So far this is a great achevement for me, thanks a lot again!!!

[gipsea]

@ABG ,
This is the outcome with the blocks from above.

clear view without errors

view with errors

Not sure what and how to fix or investigate the errors

cheers

[ABG]

Apply a Do It to that global variable query_listview_list

From the error message, it looks like it had short rows (less than 3 items per row).

You might need to use the value if/then block to pad out short (length of list < 3) rows.

Do It does not work inside procedures, but there is nothing to stop you from taking copies of the procedure internals and feeding them from a global variable for debugging via Do It.

Also, don't leave procedures without immediately renaming them. The Blocks Editor gets confused if you don't.

[gipsea]

Hi everybody, hi @ABG.

Apply a Do It to that global variable query_listview_list
doit doesn't show anything on the variable but it shows at quary block:

Do It Result: ([1, default, 24] [2, default, 24] [3, default, 24] [4, default, 24] [5, default, 24] [6, default, 24])

and I assume it is a correct list format otherwise we are wrong to start. Can someone confirm? Can we say for sure that the result of a query is a list and not, let say, a dictioinary? The table the ID is set as unique but I don't think it effect the result, only the insert

From the error message, it looks like it had short rows (less than 3 items per row).

Correct, as I mentioned above the each row is made of 3 fields in total:

 ID, name, value 

but at the moment the total rows is 5. Which one should I manage with if/then block? How should I pad the rows out?

What trigger me is the error msg:

.... the arguments: , [[1,default,24]], [1]

I tried to debug the procedure but not at that level yet so I implement a loop (old fashion) receiving the same error so it is not the programming but to me is how I consider and want to manipulate the query result.

I've reached a dead end due probably of lack of knowledge with how AI2 generate,store and require to manipulate queries results

Further suggestions are welcome

EDIT: after further investigation I figured out that the query generate a list but the rows are not lists nor a dictionary or a string.
What AI2 shows with [...]? I'm running out of options and how to access it?

[ABG]

It's time for you to learn JSON.

Also, there are a bunch of blocks you will need to test query results:

Is text?

Is a list?

Is number?

Length of list

Web1.JSONTextDecode

FAQ Section: JSON and Dictionaries

The new Dictionaries docs - MIT The Web block to extract a dictionary from JSON (draggable) [component_method] Blocks based JSON Browser - ABG Blocks based JSON Filter and Lookup (2018, needs dictionary update) - ABG Extracting data from JSON text (2016, needs refesh for dictionaries) JSONTools extension - Luke Gackle Web Component – JSON Response On App Inventor - Sajal Dutta JSON Path Finder - @Luc_Volders How to Prettify JSON with JavaScript - @Kevinkun Collapsible JSON WebViewer -…

[gipsea]

I did do the test with "is a ...." and:

1. the query result is a list (confirmed) and I can pick single value by item number
2. the query values are NOT list, string or dictionary

From this thread:
https://community.appinventor.mit.edu/t/text-in-label-enclosed-with-square-brackets/74228
@ABG wrote

The (square brakets) wrapper is the default conversion in AI2 when taking an entire list and converting it to text.

but when I do "is a string" check result is false

Said the above, I can check Json but what exactly related to the query, list and conversion of list item to ?

I keep on reading

[ABG]

If you are submitting different queries asynchronously it helps to leave a note for yourself to help know how to process the completion message when it arrives.

Was I updating?

Was I selecting?

[gipsea]

@ABG
thanks for mentioning json and the FAQ. this is the way to go and finally fixed it.

with these blocks I can now select the items of the returned json converted list

I couldn't guess the block would return a json "entity"

Now, beside how to manipulate json entities, why and in which occasion a block switch to json? this is for further reference for all newbee as me

Thanks a lot and let keep now crashing on coding!!!!

[ABG]

I am not familiar with the various SQLite extensions and their outputs.

I recommend reading the docs where you got the extension..

I can't comment on your code for that

[ABG]

@gipsea

and in which occasion a block switch to json?

JSON is intended for packaging for transport of complex data structures, where you need to label the data to help unpack it on arrival.

[gipsea]

@gipsea

I couldn't guess the block would return a json "entity"

Just a quick feedback.
Continuing learning AI2, to answer this question there is in the project set up a dedicated option about which format to use for list, JSON vs Lisp.

one more little drop in the ocean of knowledge