Collecting Part of Another Collection

[ericonline]

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!

[ericonline]

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]

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,

[wyotim]

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.

[timl]

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]

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!

[ericonline]

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!
  • 
• 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.
  • 
• 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!

[RandyHayes]

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