Unanswered

Building 1 Data Collection using 3 SharePoint Lists (with different columns)

(0) Share

Report

Posted on by marcgiag

Hi,

My company as restructured and Are looking to make a one stop shop for multiple applications throughout the company. I am trying to create 1 Data Collection that appends 3 different SharePoint Lists that have different columns... or i need to fake some data into a column.

I hope the below scenario will help visualize what I'm trying to build.

SharePoint List A

Cable	Quantity	Color	Status	Length	Value	Created on	Created By
5512	3	Blue	Open	55”	$300	5/12/2021	Jason
5486	2	Red	Completed	22”	250	5/18/2021	Kelly

SharePoint List B

Help Type	Assigned To	Problem	Status	Created on	Created By
Software	Jack	Won’t turn on	Open	5/1/2021	Jeff
Hardware	Kevin	Won’t Load	Completed	5/3/2021	Jason

SharePoint List B

Request Type	Assigned To	Status	Created on	Created By
Checklist Review	Jack	Completed	5/11/2021	Jeff
Document Review	Beth	Open	5/13/2021	Sam

I want to collect List A If status is <> “Completed”
- New Column Request Type = “CABLE”
- New column Assigned To = “Parts”
- Import Status column value
- Import Created On Value (SP’s auto generated column)
- Import Created By Name Value (SP’s auto generated column)

I want to collect List B and append it to List A collection (above) If status is <> “Completed”
- Import Column Help Type value and add it’s value to Request Type
- Import Assigned To value
- Import Status column value
- Import Created On Value (SP’s auto generated column)
- Import Created By Name Value (SP’s auto generated column)

I want to collect List C and append it to List A collection (above) If status is <> “Completed”
- Import Column Request Type
- Import Assigned To value
- Import Status column value
- Import Created On Value (SP’s auto generated column)
- Import Created By Name Value (SP’s auto generated column)

With all this combined I want to make a Gallery that will show

Request Type Assigned To Status Created on Created By

CABLE Parts Open 5/12/2021 Jason

Software Jack Open 5/1/2021 Jeff

Document Review Beth Open 5/13/2021 Sam

Categories:

Building Power Apps

All responses (5)

Answers (0)

Sort by

TheRobRush 11,128 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

You will want to learn how to use AddColumn and other things related to table construction in powerapps, check out this MS Doc

AddColumns, DropColumns, RenameColumns, and ShowColumns functions in Power Apps
marcgiag 92 on at

Like
a
(0)

Report
Copy link

Link copied!

@TheRobRush I have reviewed that document and it helps with the columns but not putting the 3 lists together... do you think to start I should make 3 collections 1 for each sharepoint list and then make a final collection to combine (still don't know how to do that)
C-Papa 1,836 on at

Like
a
(0)

Report
Copy link

Link copied!

hi, you might have to adjust the filter slightly but this should get you close

ClearCollect(col_combined_list,Filter(ListA, status is <> “Completed”),Filter(ListB, status is <> “Completed”),Filter(ListC, status is <> “Completed”))
TheRobRush 11,128 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

I would try collecting 3 separate lists, then doing a ForAll on two of the collections to compare their records to the primary collection and addcolumns as needed. and remove those items from the secondary and tertiary lists as it finishes adding them.
marcgiag 92 on at

Like
a
(0)

Report
Copy link

Link copied!

@TheRobRush ...I'm still struggling here ... I've tried 2 scenarios below but It's still not adding the new columns with the default data

1st try
AddColumns(ClearCollect(CADRequest,Filter('CAD Drawing Request Form',Progress.Value <> "Complete")),"AssignedTo", "CAD", "RequestType", "CAD Request")

2nd try
//get CAD data
ClearCollect(CADRequest,Filter('CAD Drawing Request Form',Progress.Value <> "Complete"));
AddColumns(CADRequest,"AssignedTo", "CAD", "RequestType", "CAD Request");
RenameColumns(CADRequest,"Progress","Status");
//Get Service ticket data
ClearCollect(ServiceTickets,Filter(Serviceticket,Status <> "Completed"));
RenameColumns(ServiceTickets,"Assigned_x0020_To","AssignedTo");
Collect(OpenRequests,CADRequest,ServiceTickets)