How to build dynamic sub-collection from 1-table Collection?

[virakones]

Hello, 

I have a powerapp that currently restricts me to only use 1 table of data (in the form of a csv) that I have populating to a single dataverse table via power automate flow.

From that single dataverse table, I have different records that will fill specific fields and columns into either a "Memo" or a "Product", ex. a record containing a memo will only fill the memo field, however, all of these records have a line Id that is included with the data to be able to sort by line id order.

In my powerapp I've collected this single table into a single collection but would like to break up each collection of Memos into dynamic "sub collections" of child Product records. Because it's a single table, the challenge is that I do not have a lookup to refer to whether a line is a One to Many, Memo > Product.

It's challenging becuase I want the formula to stop before the next "Memo" field and so I thought about a ForAll(collection, collect(subcollection, Not(IsBlank(Memo)) && 'Line Id' > thisrecord.lineid)) but not sure exactly if this is the solution to get it to collect the groups.

What are some ways I can solve this?

[v-bofeng-msft · Accepted solution]

Hi @virakones :

Do you want to group the records in the table in order?

If so,I've made a test for your reference:

1\I assume there is a table

ClearCollect(
   TheTable,
   {Memo:"Memo 1",'Line Id':1},
   {Product:"Product1",Description:"Description",'Line Id':2},
   {Product:"Product2",Description:"Description",'Line Id':3},
   {Product:"Product3",Description:"Description",'Line Id':4},
   {Memo:"Memo 2",'Line Id':5},
   {Product:"Product4",Description:"Description",'Line Id':6},
   {Product:"Product5",Description:"Description",'Line Id':7},
   {Memo:"Memo 3",'Line Id':8}
)

2\Add a button and set it's OnSelect property to:

ClearCollect(
    Thetargettable,
    With(
        {
            ParentTable: ShowColumns(
                Filter(
                    TheTable,
                    !IsBlank(Memo)
                ),
                "Memo",
                "Line Id"
            )
        },
        ForAll(
            Sequence(CountRows(ParentTable)),
            {
                Memo: Last(
                    FirstN(
                        ParentTable,
                        Value
                    )
                ).Memo,
                'Line Id': Last(
                    FirstN(
                        ParentTable,
                        Value
                    )
                ).'Line Id',
                'Sub-Collection(Product)': Filter(
                    TheTable,
                    'Line Id' < Last(
                        FirstN(
                            ParentTable,
                            Value + 1
                        )
                    ).'Line Id' && 'Line Id' > Last(
                        FirstN(
                            ParentTable,
                            Value
                        )
                    ).'Line Id'
                )
            }
        )
    )
)

The Result:

Best Regards,

Bof

[virakones]

@v-bofeng-msft 

Wow! Thank you for the helpful formula - really appreciate it. I'll try nailing this down using your reference. 

If it's not too much trouble, just wondering if you could clarify in simple language some of the functions used and why? Specifically, the With and Sequence functions and their purpose in the formulas.

Again, thank you for the help!