cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ericonline
Community Champion
Community Champion

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
ericonline
Community Champion
Community Champion

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!

v-xida-msft
Community Support
Community Support

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.
wyotim
Resident Rockstar
Resident Rockstar

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.

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.

ericonline
Community Champion
Community Champion

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!

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!

@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 below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (2,766)