How to join 2 tables (SP lists) into single collection?

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I have 2 SP lists as below:

StaffContact:

Name

Phone

EmployeeContact:

Username

Tel

Fax

The 'Name' and 'Username' fields are both text field and describe the same thing. I need them merge together into same column. 'Phone'='Tel', too. StaffContact do not have 'Fax' field but I want the merged collection have column 'Fax' with empty content.

I tried to build a collection but keep getting error in PowerApp Studio. Could you please advise? Here is my code I put in App.OnStart:

ClearCollect(
 MergedCollect,
 ShowColumns(
 AddColumns('StaffContact', "User", Name),
 "Phone","Fax"));
Collect(
 MergedCollect,
 ShowColumns(
 AddColumns('EmployeeContact', "User", Username),
	AddColumns('EmployeeContact', "Phone", Tel),
 "User","Phone","Fax"));

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

WarrenBelz 154,399 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Not sure this is exactly what you want as I cannot see your data, but it would be something like this

ClearCollect( MergedCollect, ShowColumns( AddColumns( 'StaffContact', "User", Name, "Fax", "" ), "User", "Phone", "Fax" ) ); ForAll( 'EmployeeContact', Patch( MergedCollect, {User:UserName}, {Phone:Tel} ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

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

Like (0)

Report
Copy link

Link copied!
Studio is prompting me "Name isn't valid. This identifier isn't recognized" for 2nd line of code
ClearCollect( MergedCollect,
I look into some old discussion, saying I must define at least one column in the syntax. But in your code sample, I think "ShowColumns ...." part have already done this part.
What's the problem?

I have tried add below line at the top:
ClearCollect(MergedCollect,{User:"",Phone:"",Fax:""});
There will be no more syntax error . However my collection is empty.

Was this reply helpful? Yes No
WarrenBelz 154,399 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@Anonymous ,

My post was free-typed, so you have to look a bit on the structure - try it now - there was an extra comma.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

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

Like (0)

Report
Copy link

Link copied!
Thanks for your response. May I clarify that 2nd part:

ForAll( 'EmployeeContact', Patch( MergedCollect, {User:UserName}, {Phone:Tel} ) )

Is it means "for each of record in EmployeeContact, INSERT into MergedCollect with structure EmployeeContact.Username as User, EmployeeContact.Tel as Phone"?

After your update, there is no more syntax error. But MergedCollect only contain last row of EmployeeContact (it has 60 rows).

Was this reply helpful? Yes No
WarrenBelz 154,399 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous ,

I cannot see your data or wherever you values are, but the structure assumes that you have the same name in User as you do in UserName (for the match of records) and then inserts the Tel value in StaffContact into the field Phone in the new collection. All the values are coming from StaffContact and it was not clear in your post what was coming from the other list.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

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

Like (0)

Report
Copy link

Link copied!

Ah, I am very sorry. My bad. StaffContact & EmployeeContact are 2 lists from different departments. Therefore there are no relationship between them. StaffContact has 10 rows while EmployeeContact have 60 rows. I want to merge them into one collection with 70 rows.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 154,399 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @Anonymous ,

Try this

ClearCollect( MergedCollect, ShowColumns( AddColumns( RenameColumns( 'StaffContact', "Phone", "Tel", "Name", "UserName" ), "Fax", "" ), "UserName", "Tel", "Fax" ) ); Collect( MergedCollect 'EmployeeContact' )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No