cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
virakones
Resolver II
Resolver II

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

View solution in original post

@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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,523)