HTML Table - format into two records per row

[farhandsome]

Hi all,

I am using Power Automate to render an HTML table based on some SQL data via a trigger from PowerApps. I've gotten the HTML table to work. It generates as such: 

I am using the "Create HTML Table" action in Power Automate. I know these are able to be formatting using the replace() command in a "Compose" action. I've done so to adjust the spacing, borders, etc. 

I was wondering if it was possible to do the same to adjust the HTML formatting and have two records of data per row. For example, the above data would look like this:

Component	Item	QtyID	ProductionOrder	Component	Item	QtyID	ProductionOrder
Horizontal Arm Group	56100	1 of 1	102490	Ceiling Mount	56050	1 of 1	102482
Ceiling Tube Group	56075	1 of 1	102486	SK Box / Plate	56400	1 of 1	102518

And so on and so forth. This is being done to save some space on the page for longer tables. I'm not the most familar with HTML so I didn't know if a two column data table was possible. Let me know!

Thanks,

Farhan

[abm]

Hi @farhandsome 

(Edited) Let me try this and let you know

Thanks

[efialttes]

Hi!

I was just thinking on a complex time-consuming way to achieve it, but I would need your help

I mean, can you share an example of the array you are assigning as input to your current 'Create HTML table'?

THanx!

[farhandsome]

@abm and @efialttes,

Thank you for your help, both of you. I have attached the HTML code here below: 

Line
56000
58000
</p>
<link rel="stylesheet" href="https://itaintboring.com/downloads/tablestyle.css"><table class="styledtable"><thead><tr><th>Component</th><th>Item</th><th>QtyID</th><th>ProductionOrder</th></tr></thead><tbody><tr><td style="padding:5px">Horizontal Arm Group</td><td style="padding:5px">56100</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102490</td></tr><tr><td style="padding:5px">Ceiling Mount</td><td style="padding:5px">56050</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102482</td></tr><tr><td style="padding:5px">Ceiling Tube Group</td><td style="padding:5px">56075</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102486</td></tr><tr><td style="padding:5px">SK-Box / Plate</td><td style="padding:5px">56400</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102518</td></tr><tr><td style="padding:5px">Ceiling Cover</td><td style="padding:5px">56425</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102522</td></tr><tr><td style="padding:5px">Monitor</td><td style="padding:5px">56325</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102508</td></tr><tr><td style="padding:5px">Wallcontrol Box</td><td style="padding:5px">56375</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102514</td></tr><tr><td style="padding:5px">VPA Module</td><td style="padding:5px">56175</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102512</td></tr><tr><td style="padding:5px">Spring Arm Group</td><td style="padding:5px">56150</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102494</td></tr><tr><td style="padding:5px">Spring Arm Group</td><td style="padding:5px">56225</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102495</td></tr><tr><td style="padding:5px">Spring Arm Group</td><td style="padding:5px">56300</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102496</td></tr><tr><td style="padding:5px">Horizontal Arm Group</td><td style="padding:5px">58100</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102492</td></tr><tr><td style="padding:5px">Ceiling Mount</td><td style="padding:5px">58050</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102484</td></tr><tr><td style="padding:5px">Ceiling Tube Group</td><td style="padding:5px">58075</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102488</td></tr><tr><td style="padding:5px">SK-Box / Plate</td><td style="padding:5px">58325</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102520</td></tr><tr><td style="padding:5px">Ceiling Cover</td><td style="padding:5px">58350</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102524</td></tr><tr><td style="padding:5px">Monitor</td><td style="padding:5px">58250</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102510</td></tr><tr><td style="padding:5px">Wallcontrol Box</td><td style="padding:5px">58300</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102516</td></tr><tr><td style="padding:5px">Spring Arm Group</td><td style="padding:5px">58150</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102500</td></tr><tr><td style="padding:5px">Spring Arm Group</td><td style="padding:5px">58225</td><td style="padding:5px">1 of 1</td><td style="padding:5px">102501</td></tr><tr><td style="padding:5px">Hardware Box</td><td style="padding:5px"> </td><td style="padding:5px"> </td><td style="padding:5px"> </td></tr></tbody></table>

This is an example of a table generated from the flow. 

Also attached is a photo of the flow: I am inserting a collection from PowerApps that is parsed and converted to JSON within flow. 

[abm]

Hi @farhandsome 

You could do this. This is what I am thinking.

Pass the JSON results to a parse json step.

This way you know how many records are there.

Next Declare a string with <HTML> tag and add the header.

Next add a foreach loop to extract the results from JSON.

Here use a counter inside the loop increment by 1. 

only append the string if you have more than one record in the loop <tr><td>1</td><td>2</td><td>11</td><td>22</td</tr>

The trick here is for the second row record you need to use the index+1 to use as an expression for mappings. Yes tricky bit.

If no more record then append with <tr><td>1</td><td>2</td></tr>

Above under each <td> map the dynamic results.

Think you got the idea of it.

Thanks

[efialttes]

Hi!

Can you share the "Parse JSON" content in text format?

I mean all the

[
 {
   "ConfigQty" : "3"... 

from your screenshot, just copypaste and share

Thanx!

[farhandsome]

@efialttes ,

Content:

[
{
"ConfigQty": "3",
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 63,
"ExpectedStartDate": "2020-03-25",
"Material": "FT 3210",
"ParentID": "60127851_56000",
"ParentLineNo": "56000",
"ProdOrder": "102490",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Arms",
"SystemID": "60127851_56100",
"SystemQty": "1",
"Type": "Horizontal Arm Group",
"Workstation": "Horizontals"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 92,
"ExpectedStartDate": "2020-03-25",
"Material": "FT 3210",
"ParentID": "60127851_56000",
"ParentLineNo": "56000",
"ProdOrder": "102482",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Power Supply",
"SystemID": "60127851_56050",
"SystemQty": "1",
"Type": "Ceiling Mount",
"Workstation": "Power Supply"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 92,
"ExpectedStartDate": "2020-03-21",
"Material": "FT 3210",
"ParentID": "60127851_56000",
"ParentLineNo": "56000",
"ProdOrder": "102486",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Power Supply",
"SystemID": "60127851_56075",
"SystemQty": "1",
"Type": "Ceiling Tube Group",
"Workstation": "Power Supply"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 92,
"ExpectedStartDate": "2020-03-25",
"Material": "SK 0000",
"ParentID": "60127851_56000",
"ParentLineNo": "56000",
"ProdOrder": "102518",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Power Supply",
"SystemID": "60127851_56400",
"SystemQty": "1",
"Type": "SK-Box / Plate",
"Workstation": "Power Supply"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 92,
"ExpectedStartDate": "2020-03-23",
"Material": "FT 3210",
"ParentID": "60127851_56000",
"ParentLineNo": "56000",
"ProdOrder": "102522",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Power Supply",
"SystemID": "60127851_56425",
"SystemQty": "1",
"Type": "Ceiling Cover",
"Workstation": "Power Supply"
},
{
"ConfigQty": "1",
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 21,
"ExpectedStartDate": "2020-03-26",
"Material": "FT 3210",
"ParentID": "60127851_56000",
"ParentLineNo": "56000",
"ProdOrder": "102504",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "LH's",
"SystemID": "60127851_56250",
"SystemQty": "1",
"Type": "Lighthead",
"Workstation": "Cardanics"
},
{
"ConfigQty": "1",
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 72,
"ExpectedStartDate": "2020-03-25",
"Material": "FT 3210",
"ParentID": "60127851_56000",
"ParentLineNo": "56000",
"ProdOrder": "102508",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "UDM",
"SystemID": "60127851_56325",
"SystemQty": "1",
"Type": "Monitor",
"Workstation": "Flat Panel"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 6,
"ExpectedStartDate": "2020-03-25",
"Material": "FT 3210",
"ParentID": "60127851_56000",
"ParentLineNo": "56000",
"ProdOrder": "102514",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Wallbox",
"SystemID": "60127851_56375",
"SystemQty": "1",
"Type": "Wallcontrol Box",
"Workstation": "Wall Box"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 160,
"ExpectedStartDate": "2020-03-21",
"Material": "FT 3210",
"ParentID": "60127851_56000",
"ParentLineNo": "56000",
"ProdOrder": "102512",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "VPA",
"SystemID": "60127851_56175",
"SystemQty": "1",
"Type": "VPA Module",
"Workstation": "VPA"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"ExpectedStartDate": "2020-03-21",
"Material": "FT 3210",
"ParentID": "60127851_56000",
"ParentLineNo": "56000",
"ProdOrder": "102494",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Spring Arm",
"SystemID": "60127851_56150",
"SystemQty": "1",
"Type": "Spring Arm Group",
"Workstation": "Spring Arm"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"ExpectedStartDate": "2020-03-21",
"Material": "FT 3210",
"ParentID": "60127851_56000",
"ParentLineNo": "56000",
"ProdOrder": "102495",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Spring Arm",
"SystemID": "60127851_56225",
"SystemQty": "1",
"Type": "Spring Arm Group",
"Workstation": "Spring Arm"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"ExpectedStartDate": "2020-03-21",
"Material": "FT 3210",
"ParentID": "60127851_56000",
"ParentLineNo": "56000",
"ProdOrder": "102496",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Spring Arm",
"SystemID": "60127851_56300",
"SystemQty": "1",
"Type": "Spring Arm Group",
"Workstation": "Spring Arm"
},
{
"ConfigQty": "2",
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 48,
"ExpectedStartDate": "2020-03-25",
"Material": "FD 2203",
"ParentID": "60127851_58000",
"ParentLineNo": "58000",
"ProdOrder": "102492",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Arms",
"SystemID": "60127851_58100",
"SystemQty": "1",
"Type": "Horizontal Arm Group",
"Workstation": "Horizontals"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 78,
"ExpectedStartDate": "2020-03-25",
"Material": "FD 2203",
"ParentID": "60127851_58000",
"ParentLineNo": "58000",
"ProdOrder": "102484",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Power Supply",
"SystemID": "60127851_58050",
"SystemQty": "1",
"Type": "Ceiling Mount",
"Workstation": "Power Supply"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 78,
"ExpectedStartDate": "2020-03-21",
"Material": "FD 2203",
"ParentID": "60127851_58000",
"ParentLineNo": "58000",
"ProdOrder": "102488",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Power Supply",
"SystemID": "60127851_58075",
"SystemQty": "1",
"Type": "Ceiling Tube Group",
"Workstation": "Power Supply"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 78,
"ExpectedStartDate": "2020-03-25",
"Material": "SK 0000",
"ParentID": "60127851_58000",
"ParentLineNo": "58000",
"ProdOrder": "102520",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Power Supply",
"SystemID": "60127851_58325",
"SystemQty": "1",
"Type": "SK-Box / Plate",
"Workstation": "Power Supply"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 78,
"ExpectedStartDate": "2020-03-23",
"Material": "FD 2203",
"ParentID": "60127851_58000",
"ParentLineNo": "58000",
"ProdOrder": "102524",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Power Supply",
"SystemID": "60127851_58350",
"SystemQty": "1",
"Type": "Ceiling Cover",
"Workstation": "Power Supply"
},
{
"ConfigQty": "1",
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 21,
"ExpectedStartDate": "2020-03-26",
"Material": "FD 2203",
"ParentID": "60127851_58000",
"ParentLineNo": "58000",
"ProdOrder": "102506",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "LH's",
"SystemID": "60127851_58175",
"SystemQty": "1",
"Type": "Lighthead",
"Workstation": "Cardanics"
},
{
"ConfigQty": "1",
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 72,
"ExpectedStartDate": "2020-03-25",
"Material": "FD 2203",
"ParentID": "60127851_58000",
"ParentLineNo": "58000",
"ProdOrder": "102510",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "UDM",
"SystemID": "60127851_58250",
"SystemQty": "1",
"Type": "Monitor",
"Workstation": "Flat Panel"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 6,
"ExpectedStartDate": "2020-03-25",
"Material": "FD 2203",
"ParentID": "60127851_58000",
"ParentLineNo": "58000",
"ProdOrder": "102516",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Wallbox",
"SystemID": "60127851_58300",
"SystemQty": "1",
"Type": "Wallcontrol Box",
"Workstation": "Wall Box"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"ExpectedStartDate": "2020-03-21",
"Material": "FD 2203",
"ParentID": "60127851_58000",
"ParentLineNo": "58000",
"ProdOrder": "102500",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Spring Arm",
"SystemID": "60127851_58150",
"SystemQty": "1",
"Type": "Spring Arm Group",
"Workstation": "Spring Arm"
},
{
"Customer": "GOOD SAMARITAN HOSP",
"ExpectedStartDate": "2020-03-21",
"Material": "FD 2203",
"ParentID": "60127851_58000",
"ParentLineNo": "58000",
"ProdOrder": "102501",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Spring Arm",
"SystemID": "60127851_58225",
"SystemQty": "1",
"Type": "Spring Arm Group",
"Workstation": "Spring Arm"
}
]

Schema:

{

  "type": "array",

  "items": {

    "type": "object",

    "properties": {

      "ConfigQty": {

        "type": "string"

      },

      "Customer": {

        "type": "string"

      },

      "CycleTime": {

        "type": "integer"

      },

      "ExpectedStartDate": {

        "type": "string"

      },

      "Material": {

        "type": "string"

      },

      "ParentID": {

        "type": "string"

      },

      "ParentLineNo": {

        "type": "string"

      },

      "ProdOrder": {

        "type": "string"

      },

      "QtyID": {

        "type": "string"

      },

      "SalesNo": {

        "type": "string"

      },

      "Subassembly": {

        "type": "string"

      },

      "SystemID": {

        "type": "string"

      },

      "SystemQty": {

        "type": "string"

      },

      "Type": {

        "type": "string"

      },

      "Workstation": {

        "type": "string"

      }

    },

    "required": [

      "Customer",

      "ExpectedStartDate",

      "Material",

      "ParentID",

      "ParentLineNo",

      "ProdOrder",

      "QtyID",

      "SalesNo",

      "Subassembly",

      "SystemID",

      "SystemQty",

      "Type",

      "Workstation"

    ]

  }

}

[efialttes]

So, lets take the first object as example.

{
"ConfigQty": "3",
"Customer": "GOOD SAMARITAN HOSP",
"CycleTime": 63,
"ExpectedStartDate": "2020-03-25",
"Material": "FT 3210",
"ParentID": "60127851_56000",
"ParentLineNo": "56000",
"ProdOrder": "102490",
"QtyID": "1 of 1",
"SalesNo": "60127851",
"Subassembly": "Arms",
"SystemID": "60127851_56100",
"SystemQty": "1",
"Type": "Horizontal Arm Group",
"Workstation": "Horizontals"
}

In order to build your current HTML table, you are using:

Component -> mapped from  "Type": "Horizontal Arm Group"

Item -> mapped from "ParentLineNo": "56000"

QtyID ->mapped from "QtyID": "1 of 1"

ProductionOrder -> mapped from "ProdOrder": "102490"

Is my assumption right?

As soon as you confirm if the mapping above is correct, I will elaborate a bit more the flow design.

Just as an overview, I would calculate current array length, if odd add a final empty object

Then split current array in two, both of them half of original arrays length, let's call them auxArrayOdd, auxArrayEven

Then initialize an integer index variable, lets call it i, assign as initial value 0

Then initialize an array variable, lets call it myOutputArray, assign as initial value []

Then Apply to each to iterate all though all items from any of the two auxiliary arrays.Now inside the Apply to each:

-Append to array, variable name 'myOutputArray', assign the following expression:

setProperty(setProperty(json('{}'),'ComponentOdd',auxArrayOdd[variables('i')]?['Type']),'ComponentEven',auxArrayEven[variables('i')]?['Type']))

-Increment variable, name i, value 1

In this overview I just consolidated Component values, the idea is to consolidate all columns you need

In order to proceed, please confirm if my mapping assumption was right.

Thanx!

[farhandsome]

@efialttes ,

Your method makes sense! and your assumptions are correct. How will you rectify tables with an odd number of rows?

[efialttes]

Hi!

So I assigned your Parse JSON example to a variable called myInputArray. This is the way I plan to guarantee our array have an even nr of elements

I just tested with your example but it has 22 items, can you test it with an odd nr of elements?

Time for dinner with my kids

Thanx!

[farhandsome]

Thanks for all the help so far, @efialttes. It indeed does work with an odd number of elements. The last element is just a blank row as described. 

[efialttes · Accepted solution]

Hi again!

Next steps, in this screenshot. Instead of handling two aux arrays, I am just gonna handle one (with the last half of the elements) and iterate through it

Am I missing something?

Thanx!

[farhandsome]

@efialttes,

I think I understand your process from here. I can take over! I very much appreciate all of your help.''

Thanks,

Farhan

[efialttes]

---

@farhandsome wrote:

@efialttes,

I think I understand your process from here. I can take over! I very much appreciate all of your help.''

Thanks,

Farhan

---

 GReat! Please let us know if you face any new issue!

In the meantime, may I suggest the following steps?

1.- If you succeed, please mark this topic as "Solved" by clicking "Accept as a solution". This way other community members facing this same problem in the future have the chance to find a solution faster. Please note you can even select several replies as as 'Solution', even from different community members!

2.- (OPTIONAL) Click on 'Thumbs up' in any answer you found valuable, or even in all of them 😉.

Even though this 2nd step is OPTIONAL, please remember it is the cheapest, easiest way to say thanks to somebody that simply tried to help. And just between you and me, everytime an inspiring answer receive a kudo, an ewok escapes from The Emperor menace  😉

Thanx for your contribution to keep this community great!