Adding text to columns in nested collection

[lmheimendinger]

I created a nested collection with (changed some of the names for this posting:

ClearCollect(colGrp,GroupBy(Filter(Orders, ShipDate=varDeliverDate),"Locations", "grpSO"));

and it gets me exactly what I want.  when i used nested galleries, I get a list of locations and within each location I get rows for each order.  So why am I posting about something that works?  Because I need more.

If I view the colletion colGrp, it has its first colmn as "Location" and the second as a table, grpSO.  What I need to do with each row in colGRP and each row in each table of grpSO is to add HTML to wrap the contents to be able to create a pdf document; this would generate a table within a table in HTML.

For example, suppose colGrp has its first row:

 Seattle           (table of orders for Seattle)

And then the table of orders for Seattle has two rows:

Order 1    Customer 1      06/10/2022    

Order 2    Customer 2       06/10/2022

I want to create another collection, say colPDF using similar GroupBy so that colPDF will have as its first row

 <tr><td>Seattle </td></tr>           (table of orders for Seattle)

and its nested table would have  two rows

<tr><td>Order 1</td>    <td>Customer 1</td>     <td> 06/10/2022</td></tr>    

<tr><td>Order 2</td>    <td>Customer 2</td>      <td>06/10/2022</td></tr>

of course I might - and likey will - have more HTML code to add including adding more rows of HTML but i hope that turns out to be easier than trying to figure this out. 

To summarize, I am seeking how to modify the ClearCollect at the top of this post to add the HTML text to the rows in the parent and subtable.  If the way to achieve that is to modify the collection (Patch?) after it is created, that is equally fine.

Thanks.

[EddieE]

@lmheimendinger 

Something like this 

ClearCollect(
    Orders,
    {Locations: "Seattle", Order: "Order 1", Customer: "Customer 1", ShipDate: Date(2022,10,06)},    
    {Locations: "Seattle", Order: "Order 2", Customer: "Customer 2", ShipDate: Date(2022,10,06)}
);

ClearCollect(colGrp,GroupBy(Orders, "Locations", "grpSO"));

Clear(colPDF);
ForAll(
    Ungroup(colGrp, "grpSO") As grpData,
        Collect(
            colPDF,
                $"<tr><td> {grpData.Locations} </td></tr>
                <tr><td> {grpData.Order} </td><td> {grpData.Customer} </td><td> {grpData.ShipDate} </td></tr>"
        )
)  

yields this

Is this what you are looking for?

I usually use a HTML text control to do this rather than a collection

[lmheimendinger]

It's going to move the ball forward.  I realized that i also need to insert text into the FIRST row of subcollection (I think) that has <table style....> ...  </table>to nest the tables in order to nest the tables.

FYI I do use HTML text.  Something like "& Concat(colHTMLShipTo, Value) &" will insert multiple rows into the HTML text.  I haven't had a chance to play with your suggestion yet, but it looks like I will have to combine the two collections in such a way that they mimic what power Apps does with nested galleries", the way I want the pdf to look.

Location 1

   Order 1

   Order 2

Locatin 2

   Order 3

   Order 4

   Order 5

[lmheimendinger]

Well I am stuck again trying to nail down a final result.  Let me try and explain.

My Order dataspource has many columns I am not interested in to create the pdf.  So I figured I would just use a ShowColumns within my GroupBy to trim down the columns.  But I can't get a proper expression that combines

• ShowColumns
• GroupBY
• Filter

In words, what I want the ClearCollect to yield is

Only orders that match the delivery location, and

GroupBy location name, and

with Col1, Col2, Col3, ... the only columns from orders to end up the table of order records for a location row.

When I can do that, I can then try adding the html code perhaps like you suggested.

And pending what I hear back from the business unit, I may have a new requirement:  the line items for each order, so it would be

Location --> Table of orders  --> for each order  in table of orders, table of line items

Location

    Order 1

          Line Item 1

           Line Item 2

   Order 2

           Line Item 1

           Line Item 2

           Line Item 3

.....

[EddieE]

@lmheimendinger 

This will create a collection in the format you want, not sure how to get from here to PDF tho ...

ClearCollect(
    Orders,
    {Locations: "Seattle", Order: "Order 1", Customer: "Customer 1", ShipDate: Date(2022,10,06)},    
    {Locations: "Seattle", Order: "Order 1", Customer: "Customer 2", ShipDate: Date(2022,10,06)},
    {Locations: "Seattle", Order: "Order 2", Customer: "Customer 5", ShipDate: Date(2022,10,06)},    
    {Locations: "Seattle", Order: "Order 2", Customer: "Customer 6", ShipDate: Date(2022,10,06)},
    {Locations: "New York", Order: "Order 1", Customer: "Customer 3", ShipDate: Date(2022,10,06)},    
    {Locations: "New York", Order: "Order 1", Customer: "Customer 4", ShipDate: Date(2022,10,06)}
);

ClearCollect(colGrp,
        GroupBy( 
            GroupBy(
                Orders, "Locations", "Order", "grpOrders"
            ),
            "Locations", 
            "grpSO"
            )
);

[lmheimendinger]

not really as I cannot have specific values.  they have to come from a data source.  I had no problems getting the groupby collection; what i am now trying to do is to both use showbycolumns to limit columns in subsollection and also add the html text during the clearcollect.  thanks

[EddieE]

@lmheimendinger 

I realise that you'll have a data source to collect from, I'm just showing you how the GroupBy data can be collected with some dummy data.

Can you share the code in a code window that you are trying. This will help to progress this further

[lmheimendinger · Accepted solution]

The actual solutio was a two step process.  After GroupBY() to get the parent table and associated rows, i then had to get new collections based on them:

ClearCollect(colGrp,GroupBy(Filter(SalesOrders, ShipDate=varDelDate),"Driver_x0020_Name", "grpSO"));
ClearCollect(colSO, Filter(SalesOrders, ShipDate=varDelDate));
ClearCollect(colDrivers, colGrp.Driver_x0020_Name)

The HTML I then used is

<table>
 "& Concat(RenameColumns(colDrivers, "Driver_x0020_Name", "Driver"), "<tr class=""parent""><td><h4>" & Driver & "</h4></td></tr>" & "</table>"
  &  Concat(SortByColumns(Filter(colSO, 'Driver Name'=Driver),"Delivery_x0020_Seq_x0020_No"),
     "<tr class=""child""><td><h6>("
     & 'Delivery Seq No' & ") </td></h6><td><h6>"
     &  RefNumber & "</td><td>" & " - "
     & CustomerRef_FullName & "</td></h6>"
     & "</td></tr></h6>")
     & "</table" )  
  &"</table>"

he 

The HTML rendering is not yet perfect but tweaking it.  My main goal was getting the data organized properly and this did that.