cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
virakones
Responsive Resident
Responsive Resident

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

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?

problem.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

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:

004.gif

Best Regards,

Bof

View solution in original post

2 REPLIES 2
v-bofeng-msft
Community Support
Community Support

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:

004.gif

Best Regards,

Bof

@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!

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,762)