Answered

AddColumns to Collection only showing first value

(0) Share

Report

Posted on by ColoradoMike

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

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

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

Was this reply helpful? Yes No
ColoradoMike 11 on at

Like (0)

Report

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 ID Name
1234 John
1234 Joe
1234 Jenny
1235 Shaun
1235 Sam
1235 Sarah

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

Like (0)

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

Was this reply helpful? Yes No
ColoradoMike 11 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

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

Was this reply helpful? Yes No
ColoradoMike 11 on at

Like (1)

Report

That did it! Thank you very much for your time

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

Like (0)

Report

@ColoradoMike

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

Was this reply helpful? Yes No