Unanswered

Collecting Part of Another Collection

(0) Share

Report

Posted on by Community Power Pla...

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Community Power Pla... on at

Like (0)

Report

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!

Was this reply helpful? Yes No
v-xida-msft on at

Like (1)

Report

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,

Was this reply helpful? Yes No
wyotim 2,545 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
timl 36,387 Super User 2025 Season 2 on at

Like (2)

Report
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-function/m-p/211457#M67502

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.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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!

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

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

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

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

Was this reply helpful? Yes No