Hello,
In my flow I am receiving in data from an excel file through my REST connector. This data may contain x columns with y rows of unknown names. I want to be able to display this data to the user in a table. As an example, I have been testing with the below data. I have figured out the objects directly under data are my rows, and under the rows are columns, and under them is the data at those set points. I am able to extract the rows from the data, but I cannot seem to perform any functions on them without running into errors. Is there an easy way to display this data? Or a series of logic that could transform my data into an acceptable format to then display considering I do not know the format beforehand?
(I am guessing: “data” table with 3 rows, each row containing a “value” table with table in first row having 3 rows (column “value” = 1, 2 ,3), table in the second row having 2 rows (column “value” = 4, 5) and table in the third having 1 row (column “value” = 6))
I did a post about dynamically adding content to a data grid. It might be helpful to you. But as @ivstde mentioned, you have to get the data into a workable format using flowscript first. Nonetheless, here is the link to that post.
The downside (of flow tables in general) is that the table “size” in which to place data cant be really dynamic, here i have set up 9 columns so you can add more if you need (but it will work with fetching less, in this example just 3)
And my naming is column1,2,3… (becouse i needed it while using a counter) but you can combine it with Daniels post and rename them when showing them in datagrid.
Another solution for “flattening” your sub tables would be something like the Parsing Example from here Programs | Flow Help
P.S. Could you maybe share your flow and REST connector config for reading Excel files?
(sounds really useful and sounds like it could save someone some time when the need for reading Excel files pops up…and there will always be Excel files to read )
Thanks for the help! This will be useful.
Here is my configuration for getting data from excel. I removed the authorization scheme since it may include sensitive information. read excel.ncrcp (35.5 KB)
Hello,
I am trying to implement your solution but am running into an issue. Although the table you created and the table, I am testing with are identical in the output, the flow will not run due to an error in the script step to “make it nice”. As you can see in the screenshots below, row.value is not recognized, although it would make since that it is. Row. wants to autofill to row.table however this does not work either. Any further help would be appreciated. I figured out I can go further in the last screenshot. However, “Val” does not work. Novacura tells me it expects a type. I tried with table(value) and a few other iterations, but this does not work and results in the same error message.
from above it looks like your data structure is now different to what was in the first screenshots?
(looks like the “value” table is replaced with a “table” table)
But i think Daniel might be right in that case, “table” would be treated as a reserved word, wouldnt be accepted as a name of a table.
So you would need to rename it in the output of the REST connector.
But is that the case, how does your “data” table look now?
No, the data structure between the screenshots here is not changed. Novacura script does not seem to recognize value as the name of the table. I believe Novacura does not like nesting tables/records of the same name. I am unsure of how to change this since the data is coming from excel which is creating these names, and using a model transformation has not worked for me.
i am confused…don’t understand why it wouldn’t work if the data structures match…(its not version related, just installed that version and my example still works there).
Maybe you can try and leave my script for creating the structure then just add an assignment step after to empty the values (but the data table structure should still be initialized)
Then add your REST call step after and see if the “Make it nice” script step gives you errors?
(or if it would be the REST call machine step that would complain about something)
Weeeell…i guess its just one of those things…no idea why it does what it does: output from that rest call is kind of a Schrödinger’s variable (called both table and value and kind of none too) but… a stupid problem deserves a stupid solution
So here is mine: TEST 2.flow (89.1 KB)
Basically i am keeping my original manually created structure as a Type (data), then fetching the excel (data2) but turning it into a JSON string and then JSON decoding to a table (data3) using the string from the REST call and the Type from the beginning
I don’t see any differences between data2 and data3 but…if its stupid and it works… (its still stupid but you just got lucky )
This is so outrageous… that I will absolutely use this in my complex rest projects from now on.
Honestly, the best solution might be to have a single text input in the flow that accepts the json payload, then use the JSON.Decode module to handle it as (in my opinion) that module handles data very well.
)
.