cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Collecting Part of Another Collection

I have 3 Collection columns which may or may not contain multiple values separated by ";". I want to grab each of the values in another collection for display purposes.

Example:

  • Values in Collection1:
    • Column1: "Go to work; Pick up parts"
    • Column2: "Dec. 16 2018"
    • Column3: "Eric Online; Sam Offline"

I want to collect these columns into Collection2 as:

  • ID: 1, Action: Go to work, Action Date: Dec. 16 2018, ResponsibleParty: Eric Online
  • ID: 2, Action: Pick up parts, Action Date:                   , ResponsibleParty: Sam Offline

Having trouble figuring out how to approach this. 

Any ideas?

Thank yoU!

7 REPLIES 7
Super User
Super User

Re: Collecting Part of Another Collection

What sayeth @wyotim? Perhaps the good rev. dr. @timl?

Have you guys collected part of a collection and split it up before? I know its a weird one but the UI and denormalized Sharepoint lists dictates a hack!

Community Support Team
Community Support Team

Re: Collecting Part of Another Collection

Hi @ericonline,

Could you please share a bit more about your scenario?

Based on the needs that you mentioned, I have made a test on my side. I afraid that there is no way to achieve your needs in PowerApps currently.

Currently, within PowerApps, there is no available functions supported to achieve your needs.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Collecting Part of Another Collection

I haven’t done anything quite like that but I am intrigued by the scenario. I’ll try to mock something up either today or tomorrow and see what I can come up with.
Super User
Super User

Re: Collecting Part of Another Collection

Hi @ericonline

I'm confident that we can definitely accomplish this in PowerApps!

Just a couple of questions. Will Collection1 only ever contain a single record? If this is the case, it'll make this job simpler. I also hope that Collection1.Column1 will always contain a full list of actions. This logically makes sense to me, because there must always be an Action against which we can assign an ActionDate and Responsible Party.

 

If this is the case, we can Split First(Collection1).Column1 by ";".

ClearCollect(colCollection1Split, 
             Split(First(Collection1).Column1,";")
)

This would provide us with the base table on which we can loop over with a ForAll. Something like this would provide us with the sequential ID "RowNumbers".

 

ForAll(colCollection1Split, 
       Collect(colCollection1Numbered,
               Last(FirstN(AddColumns(colCollection1Split,
                                     "RowNumber",
                                      CountRows(colCollection1Numbered)+1
                                      ), 
                           CountRows(colCollection1Numbered)+1
                     )
                )    
         )
)

Within this loop, we can incorporate this snippet that @CarlosFigueira posted today to extract the corresponding values from Column2 and Column3.

 

https://powerusers.microsoft.com/t5/General-Discussion/Get-Nth-Item-from-the-result-of-Split-functio...

 

I'm a bit busy right now and unfortunatly don't have too much time to look into this in much detail.  But these are the pieces that I would use to solve this. Hope that's of some use to you.

Super User
Super User

Re: Collecting Part of Another Collection

Hi @timl,

RE: Will Collection1 only ever contain a single record?: No, it could consist of multiple records.

RE: Will Collection1.Column1 always contain an "Action": Yes

Thank you for taking time to put your expertise into this!
I will work through your answer and follow up. 

Yay!

Super User
Super User

Re: Collecting Part of Another Collection

Ugh! Still hammering away on this silly thing.

Problem:

  • Users may add 0 or 100 values to a single Sharepoint column (using a Gallery with "+" in the app)
  • I'm these 0 or multiple semicolon-separated values to a single Sharepoint column rather than doing the right thing which is to create a second list.
  • Then I'm allowing the user to save the "form" and come back later. (NOTE: I don't use Form controls for this, but rather Galleries and such)
  • This results in a many-to-one going into the data source and a one-to-many transformation coming out when a Saved "form" is loaded.

User Experience + App Functions:

  • User selects a partially completed form. OnSelect of the button:
    • ClearCollect(colPartialForm, 
              Filter(sharepointList, 
                  Status = "Incomplete"
              )
          );
      
      ClearCollect(colSubGalleryInForm,
          {
              action: If(!IsBlank(First(colPartialForm).actions), Split(First(colPartialForm).actions, ";")),
              schedule: If(!IsBlank(First(colPartialForm).schedule), Split(First(colPartialForm).schedule, ";")),
              party: If(!IsBlank(First(colPartialForm).party), Split(First(colPartialExercise).party, ";"))
          }
      )
  • Problem here: Nested table + all columns are doubled!
    • image.png
  • Once the saved "form" is loaded, the Collection appears correctly:
    • Gallery fields use: First(ThisItem.action).Result, First(ThisItem.schedule).Result, First(ThisItem.party).Result
    • I'm even able to hit the trash can set for: Remove(colSubGalleryInForm, ThisItem) and delete the whole thing.
    • image.png
  • Problem here is: If user wants to add a new Action/Schedule/Party in this partially completed form, I can't Patch into the nested tables of the Collection.
  • I get the "Incompatible type. The item you're trying to put into a collection has a type that is not compatible with the collection." error

I'll attack it again tomorrow!!
Cheers!

Super User
Super User

Re: Collecting Part of Another Collection

@ericonlinethis look somewhat similar to this post that @Mr-Dang-MSFT solved masterfully!

Not sure it completely relates, but if you missed that post - definitely a formula to put in your back pocket.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 51 members 4,928 guests
Please welcome our newest community members: