cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ColoradoMike
New Member

AddColumns to Collection only showing first value

Hello. I'm attempting to add a column from one Sharepoint List to a collection "joined" by a 'Case ID' to be used in searching. The problem is the new collection is only pulling the first item in the column rather than all associated items based on the Case ID. If there is a better way to do this please let me know as I'm stumped. Thank you

 

 

ClearCollect(viewCollection, (AddColumns('Basic Case Information',"Name",LookUp('Name Information', 'Case ID'='Basic Case Information'[@'Case ID'],'Name'))));

 

 

Example:
Basic Case Information

Case ID: 1234

Case ID: 1235

 

Name Information

Case ID: 1234 / Name: John, Joe, Jenny

Case ID: 1235 / Name: Shaun, Sam, Sarah 

 

viewCollection:
Case ID: 1234 / Name: John

Case ID: 1235 / Name: Shaun

1 ACCEPTED SOLUTION

Accepted Solutions

@ColoradoMike 

Ooops...my bad, I meant to do a rename columns function not the add columns.

Here is the revised:

ClearCollect(viewCollection, 
    AddColumns('Basic Case Information',
        "Name", Concat(
                     Filter(
                         RenameColumns('Name Information', "Case ID", "caseID"), 
                         caseID = 'Case ID'
                     ),
                     Name & ","
                 )
     )
)

 

Sorry about that...was moving too quick.

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

7 REPLIES 7
RandyHayes
Super User III
Super User III

@ColoradoMike 

Can you describe further what you mean when you show the Name value with three different names.

Or, more importantly, in your SharePoint List (Name Information) what kind of column is Name?  Is it a Text column?

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

The Name Column is a text column. So in the Name Information list I have a text column for Case ID and a text column for Name. There are multiple records associated with each Case ID with different names. But the AddColumns/Lookup combination is only pulling the first record associated with the Case ID: 1234/John and 1235/Shaun (bolded below), rather than all names associated. 

 

Name Information List

Case IDName
1234John
1234Joe
1234Jenny
1235Shaun
1235Sam
1235Sarah

 

@ColoradoMike 

So then, if you want to get all the names in a coma separated format, then you can use the following formula:

ClearCollect(viewCollection, 
    AddColumns('Basic Case Information',
        "Name", Concat(
                     Filter(
                         AddColumns('Name Information', "caseID", 'Case ID'), 
                         caseID = 'Case ID'
                     ),
                     Name & ","
                 )
     )
)

 

This will get all the records from the 'Name Information' List that matches the caseID.  Notice that we add a column to the datasource with a duplicated Case ID.  Although there are several "workarounds" to doing this, some testing lately has found inaccurate results, so I didn't mention them here but simply gave the tried-and-true method - which is to add a column and use that to avoid ambiguity.   This is all then used in a Concat function which will iterate on the Name columns and separate with comas.

 

See how that works for you.

 

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Thanks, @RandyHayes .

Progress! But now it appears to be adding all names to each record in viewCollections rather than separating them out. I tried tinkering a bit, but no luck. 

For clarity, when I view the Name column in viewCollections it shows John,Joe,Jenny,Shaun,Sam,Sarah in each 1234 and 1235 records. 

@ColoradoMike 

Ooops...my bad, I meant to do a rename columns function not the add columns.

Here is the revised:

ClearCollect(viewCollection, 
    AddColumns('Basic Case Information',
        "Name", Concat(
                     Filter(
                         RenameColumns('Name Information', "Case ID", "caseID"), 
                         caseID = 'Case ID'
                     ),
                     Name & ","
                 )
     )
)

 

Sorry about that...was moving too quick.

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

View solution in original post

That did it! Thank you very much for your time

@ColoradoMike 

Any time!  And welcome to the forum.  Feel free to post any other issues you might have.

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors
Users online (1,916)