Yes, that's a Bleach reference.
Hi, dear Text-Blaze enthusiasts !
It's me again, following on my previous Show&Tell of March-25 : Fetch ANY data from ANY db-tables
TL;DR, 2nd tab of last script is the good one. But you'll be better off with the last link to the snippet exchange. You need to know your DB-table's ID and name beforehand.
About 'Benihikō',
For the normally socialized humans that didn't invest thousands of hours on japanese media/culture between the 00's and 10's, Benihikō is a reference to Tite Kubo's famous manga: Bleach. In which we follow the main character thru a supernatural world of Spiritual Samouraïs fighting demons with their magical swords. When they get worked up, their swords do a lot of stuff. Chaos and epicness ensue. One of them, Tōsen, has an ability/technique, Benihikō, where his sword can demultiply into hundreds of blades to fall on his ennemies.
See for yourselves in this 20sec yt extract.
So, last time
We left it at this script, which allows you to dynamically call upon any Data Tables, fetch any field of any of their rows without having to bother to learn or encode said fields somewhere into your snippet. Using a basic API call to your space thru {urlload}.
-
You still need to know your table's name, ID and space's ID beforehand, tho.
To make the script pivotal. -
And you only get one datapoint after summoning it. Which can quickly get tedious/repetitive or even slow (loading the table each time)
{note}{formtext: name=Table_id; default=22Char ID of your DB table; cols=22}{endnote}{formtoggle: name=Ready?; default=no}{urlload: https://data-api.blaze.today/api/database/fields/table/{=table_id}/; done=(res) -> ["Data":(res)]}{Resulta=lower(fromjson({=Data}))}{list=[n.name for n in resulta]}{dbselect: SELECT product_name, Color, Price, Origin, Lenght, motto FROM Mock_Data; space=id; multiple=no; menu=yes; name=db}{formtoggle: name=Show final form menu; default=no}{note}{formmenu: values={=list}; name=ch; multiple=no}{endnote}{=db[ch]; trim=yes}{endformtoggle}{endformtoggle}
Of course, putting your relevant tables' name, ID and space's ID into a table/snippet/formmenu/list of lists/whatever-you-prefer and placing the appropriate variables in your {urlload} and {dbselect} is fairly trivial but worthy of your time. As these info tend to be only a few and not change at all. But I won't go into it because dealing with multiple tables and spaces in the Community Text Editor (which is very nice, btw 
) is an adventure I'm not yet ready for. 
Also since {urlload} don't work in TB community blog (for obvious safety reasons), I'll use the following ordered list to bypass that and simulate a successful API call and let the rest of the script run its course:
["product_name", "color", "price", "origin", "lenght", "motto"]
So today, we're improving on the second shortcoming: getting as many datapoints we want instead of just one ! 
To do so we'll take advantage of:
- The fact that both {formmenu} and {dbselect} can print multiple values without any script modification, just a boolean parameter to switch
- multiselect in {formmenu} printing an ordered list that come in whichever order you clicked its items
- The {repeat} command
- And since we're at it, we'll probably wrap the whole thing into a dynamic {repeat} loop to make it truly Benihikō. Because why not ?
Let's get cooking!
First we'll expand the snippet powers horizontally (making it able to call several fields), then vertically (call several rows).
First we take a good look at the fields {formmenu} command (where we select our fields for later) to see how we can end up with a consistent output, wether we output one or several values. Final result should be something like this: {=db[ch]} where the db variable is a keyed list and the ch variable is a key.
Thing is, {formmenu} is tricky and itemformatter won't help much:
{mlist=["product_name", "color", "price", "origin", "lenght", "motto"]}Choice list={=mlist}
Single choice:
{=ch1}
{formmenu: values={=mlist}; name=ch1; multiple=no}
Multiple choices no formatter:
{=ch2}
{formmenu: values={=mlist}; name=ch2; multiple=yes}
Multiple choices WITH formatter and join() function:
{=ch2}
{=join(ch2, ", ")}
{formmenu: values={=mlist}; name=ch2; multiple=yes; formatter=(items) -> upper(items); itemformatter=(items) -> upper(items)}
It won't show in the community viewer and not even in the preview of your dashboard, you'll need to execute the snippet to see that the formatter and itemformatter settings only affect output right where your {formmenu} is. The background variable remain the same: an ordered list in the case of a multiple choices menu.
From there we'll then have to use a [list comprehension] syntax or {repeat} command to break down the field ordered list and apply it to the "row" output by our {dbselect}
.
.
.
.
I should REALLY have checked the feasability😭
I should REALLY have checked the feasability of this before making claims, wow.
In my mind, it was by far the most trivial/expeditive part of the Ultimate data snippet building.
Just add a {toggle} command, name it, and use its boolean value as a variable for the 'Multiple' parameter of the {formmenu} and the {dbselect} that only accept yes or no. From there, it will be a simple matter of handling the varying final outputs and making it consistent.
Welp, no. None of this is that simple or possible.
Despite the nice flexibility of advanced editor in {formmenu} and {dbselect}, their 'multiple' parameters don't accept variables. Sometime they don't accept any formula at all. Trust me, I tried A BUNCH of things. 
Time to improvise and trick our way out of this !
Upon failure consideration, we'll go vertically first.
This part is quite simple as a named {dbselect} command will create a keyed list output on 'single' mode (one record).
And create an ordered list of keyed lists on 'multiple' mode (several records).
So whatever solution we come up with the 'single' mode, we'll 'simply' (lol) have to wrap it in a {repeat} command when it comes to the 'multiple' mode
See below :
One record:
{dbselect: SELECT product_name, Color, Price, Origin, Lenght, motto FROM Mock_Data; space=id; menu=yes; name=db1; multiple=no}
{=db1}
Several record:
{dbselect: SELECT product_name, Color, Price, Origin, Lenght, motto FROM Mock_Data; space=id; menu=yes; name=db2; multiple=yes}
{=db2}
And with a repeat command, we get: {repeat: for x in db2}{=x}
{endrepeat}
Considering the boolean parameters can't take variable (or any formula at all in {dbselect} case, we'll use the good old {if}/{toggle} wrapper combo to make a little switcharroo and simulate a situation where we activate/deactivate the multiselect at will. See below.
(but it's actually, 2 {dbselect} Read-from-table commands appearing and hiding when convenient)
{if: not {=Multirows}}{formtoggle: name=Multirows; default=no}{endformtoggle}--------------------------------
One record:
{dbselect: SELECT product_name, Color, Price, Origin, Lenght, motto FROM Mock_Data; space=id; menu=yes; name=db1; multiple=no}
{=db1}{endif}{formtoggle: name=Multirows; default=no}--------------------------------
Several records:
{dbselect: SELECT product_name, Color, Price, Origin, Lenght, motto FROM Mock_Data; space=id; menu=yes; name=db2; multiple=yes}
{=db2}
{endformtoggle}
Then we'll focus on the multiple possible output from the script, making them compatible and legible. We have 2 main component (the field selector and row selector). Each of them can be either single choice mode or multiple choice mode, independently of the other.
Which make for 4 possible combination.
To simplify the heuristic process and reduce complexity, I'll use two fixed variable "one" and "several" two simulate the field selector interacting with the {dbselect}, then it goes like:
{mlist=["product_name", "color", "price", "origin", "lenght", "motto"]}{=mlist}
{onefield="origin"}Onefield={=onefield}
{several_fields=["color", "price"]}Several_fields={=several_fields}
{if: not {=Multi_lines}}{formtoggle: name=Multi_lines; default=no}{endformtoggle}--------------------------------
One record:
{dbselect: SELECT product_name, Color, Price, Origin, Lenght, motto FROM Mock_Data; space=id; menu=yes; name=OneRow; multiple=no}
OneRow= {=OneRow}
Dynamic output one record/single field = {=OneRow[{=onefield}]}
Dynamic output one records/several fields = {=[OneRow[n] for n in several_fields]}
Pretty with join() function = {=join([OneRow[n] for n in several_fields], ", ")}
{endif}
{formtoggle: name=Multi_lines; default=no}--------------------------------
Several record:
{dbselect: SELECT product_name, Color, Price, Origin, Lenght, motto FROM Mock_Data; space=id; menu=yes; name=MultiRowS; multiple=yes}
MultiRowS={=MultiRowS}
Dynamic output multiple records/single field = {repeat: for x in MultiRowS}{=x[{=onefield}]}, {endrepeat}
Through list manipulation= {=[n[onefield] for n in MultiRowS]}
Pretty with Join() function = {=join([n[onefield] for n in MultiRowS], ", ")}
Dynamic output multiple records/several fields =
{repeat: for x in MultiRowS}{=[x[n] for n in several_fields]}
{endrepeat}
{repeat: for x in MultiRowS}{=join([x[n] for n in several_fields], ", ")}
{endrepeat}
{endformtoggle}
Now, of course the "pretty" version is the closest we are to a finished output someone can actually work with.
But if we delay gratification for a bit and get rid of the useless print: we can make some fairly interesting pattern recognition.
Like stated earlier, a single choice {dbselect} is an ordered list of keyed lists just like a multiple choice {dbselect}. It's just the special character [ and " that are missing. It's a list of 1 list.
Looking back on the formmenu that give us our field selector, it is the same: a single choice {formmenu} is an ordered list just like a multiple choice {formmenu}. It's just the special characters [ and " that are missing. It's a list of 1 element.
See below when you pick the same single and identical item in both single and multi {formmenu} and {dbselect}:
Please pick a single identical element in BOTH "{dbselect}" (read from table) and notice how close the output are. THEN add a second one
{mlist=["product_name", "color", "price", "origin", "lenght", "motto"]}{=mlist}
{onefield="origin"}Onefield={=onefield}
{several_fields=["color", "price"]}Several_fields={=several_fields}
One record:
{dbselect: SELECT product_name, Color, Price, Origin, Lenght, motto FROM Mock_Data; space=id; menu=yes; name=OneRow; multiple=no}
OneRow={=OneRow}
one record/single field = {=OneRow[{=onefield}]}
one records/several fields = {=[OneRow[n] for n in several_fields]}
Several record:
{dbselect: SELECT product_name, Color, Price, Origin, Lenght, motto FROM Mock_Data; space=id; menu=yes; name=MultiRowS; multiple=yes}
MultiRowS={=MultiRowS}
multiple records/single field = {repeat: for x in MultiRowS}{=x[{=onefield}]}, {endrepeat}
Through list manipulation= {=[n[onefield] for n in MultiRowS]}
multiple records/several fields =
{repeat: for x in MultiRowS}{=[x[n] for n in several_fields]}
{endrepeat}
Please notice that the bracket [ ] are INSIDE the formula, creating a list automatically and making it so we don't have to use quotations marks "" that could trigger unwanted behaviors
{onefield="origin"}Onefield={=onefield}
{several_fields=["color", "price"]}Several_fields={=several_fields}
One record:
{dbselect: SELECT product_name, Color, Price, Origin, Lenght, motto FROM Mock_Data; space=id; menu=yes; name=OneRow; multiple=no}
one record/single field = {=[[OneRow[{=onefield}]]]}
one records/several fields = {=[[OneRow[n] for n in several_fields]]}
Several record:
{dbselect: SELECT product_name, Color, Price, Origin, Lenght, motto FROM Mock_Data; space=id; menu=yes; name=MultiRowS; multiple=yes}
multiple records/single field (list manipulation)= {=[[n[onefield] for n in MultiRowS]]}
multiple records/several fields (list manipulation) =
Repeat={repeat: for x in MultiRowS}{=[x[n] for n in several_fields]}{endrepeat} We're ditching the {repeat} command for another list manipulation, for consistents outputs
={=[[x[n] for n in several_fields] for x in multirows]}
So far, our script has now virtually exploded in four scenarios, running their own course. Which is error-prone, a lot of work to maintain/update. And also, quite a pain for any further processing of said data down the line. So any bottleneck, where we can converge everything back together without any conflict, would be GREATLY appreciated.
We start doing exactly that in the SECOND TAB, in the snippet above (the "Consistency" tab), we put all these outputs in same pattern by having them all be list of list. Even if it means being a list of 1 list of one element. So that the post-processing can be unique to all scenarios.
Having ALL POSSIBLE output come in a form of an ordered list of ordered lists will be especially useful for prettification (when we make the raw data into something that could fit into a text, a e-mail or textbox.
As we'll simply use a join() function nested in a join() fuction. One that will arrange the value for inside the item/row/records. And one that will arrange the rows/records. However we see fit.
But first, It's time to bring back the {urlload} that generate the fields and fields {formmenu} that gave us so much work.
We'll also make all four possible output converge to and through ONE (if possible) single [list comprehension] function.
Which mean transforming some variable back into a list and putting all our list comprehension function in coherence/consistency.
See below:
We reaching the end !
Now all that is left to do is try to converge the variables to as few as we can using some {if} command magic.
This is the last "easy to read" snippet that should work even in the Community demonstrator.
And then, in the second tab snippet, the final one, we'll bring back the little if/toggle switcharoo trick that worked so well...while putting the final output uptop, wrapping it in a {catch} command to avoid error message that might happen before the form is completed. We will also erase any useless display and collapse everything for concision.
22Char ID of your DB table
{formtext: name=Table_id; default=ID of your DB table; cols=22}
{urlload: https://data-api.blaze.today/api/database/fields/table/{=table_id}/; done=(res) -> ["Data":(res)]}
{Resulta=lower(fromjson({=Data}))}{liste=[n.name for n in resulta]}{=liste}
The {urlload command still doesn't work in community snippet (it'll work in your dashboard with your own table id) so we'll use this simulated respsonse to continue
{mlist=["product_name", "color", "price", "origin", "lenght", "motto"]}mlist={=mlist}
Bringing back the formmenus:
Single choice:
{=onefield}
{formmenu: values={=mlist}; name=onefield; multiple=no}
Multiple choices:
{=several_fields}
{formmenu: name=several_fields; multiple=yes; values={=mlist}}
["Onefield"]={list_onefield=[onefield]}{=list_onefield}
Several_fields={=several_fields}
Fields={if: count(several_fields)>0}{Fields=several_fields}{=fields}{else}{Fields={=list_onefield}}{=fields}{endif}
One record:
{dbselect: SELECT product_name, Color, Origin, Lenght, motto, Price FROM Mock_Data; space=id; menu=yes; name=OneRow; multiple=no}{list_onerow=[onerow]}
Several record:
{dbselect: SELECT product_name, Color, Price, Origin, Lenght, motto FROM Mock_Data; space=id; menu=yes; name=MultiRowS; multiple=yes}
Rows={if: count(multirows)>0}{Rows=multirows}{else}{Rows=list_onerow}{endif}
one record/single field = {=[[r[f] for f in list_onefield] for r in list_onerow]}
one records/several fields = {=[[r[f] for f in several_fields] for r in list_onerow]}
multiple records/single field = {=[[r[f] for f in list_onefield] for r in multirows]}
multiple records/several fields = {=[[r[f] for f in several_fields] for r in multirows]}
Unique output = {Output=[[r[f] for f in fields] for r in rows]}{=output}
{=catch(output, "Make sure your table ID and name is written in appropriate places and the snippet form is completed") }{note: preview=no}
{urlload: https://data-api.blaze.today/api/database/fields/table/{=table_id}/; done=(res) -> ["Data":(res)]}
{Resulta=lower(fromjson({=Data}))}{liste=[n.name for n in resulta]}{=liste}{endnote}{note}
Final form.
The multiple options always take priority over the single choice mode. But the single choice mode is the default mode where the snippet start.
If you activated the multiple option (by selecting values in the respective {formmenu and/or {dbselect but want to revert back: simply empty your selection !
Table ID : {formtext: name=Table_id; default=22Char ID of your DB table; cols=22} / Table Name: "goes here, cannot be a variable for {dbselect {{^^%F0%9F%98%94^^}}{{^^%F0%9F%98%AD^^}}"
Fields Records
{if: not {=Multi_Fields}}{formtoggle: name=Multi_Fields; default=no}{endformtoggle}
Single choice:
{formmenu: values={=liste}; name=onefield; multiple=no}
{else}{formtoggle: name=Multi_Fields; default=no}
Multiple choices:
{formmenu: name=several_fields; multiple=yes; values={=liste}}{endformtoggle}{endif} {if: not {=Multi_lines}}{formtoggle: name=Multi_lines; default=no}{endformtoggle}
One record:
{dbselect: SELECT product_name, Color, Origin, Lenght, motto, Price FROM Mock_Data; space=id; menu=yes; name=OneRow; multiple=no}
{else}{formtoggle: name=Multi_lines; default=no}
Several record:
{dbselect: SELECT product_name, Color, Price, Origin, Lenght, motto FROM Mock_Data; space=id; menu=yes; name=MultiRowS; multiple=yes}{endformtoggle}{endif}
Unique output = {Output=[[r[f] for f in fields] for r in rows]}{=output}
Check
Fields={if: count(several_fields)>0}{Fields=several_fields}{else}{Fields={=[ onefield]}}{endif}{=fields}
Rows={if: count(multirows)>0}{Rows=multirows}{else}{Rows=[ onerow]}{endif}{=rows}
{endnote}
Unfortunately, the Community editor cannot print tables and it change the format of the snippet quite drastically.
You can go to this link for a more faithful copy of the final snippet: Copy of snippet "Benihikō 11, final form"
Oooookay, wow.
I really didn't expect this part of the series to turn out so complicated 
This is already a very long post, thanks if you read my struggles so far.
I didn't get to all the promises so they will be the focus of next post:
- The prettification of the output using join() functions.
- Wrapping everything in {repeat} command to make it the true Benihikō
See you next time !