cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kevingeorget
Resolver I
Resolver I

Single collection to pull records from 3 SP list

I have a requirement where i need to populate a collection with details from 3 SP list.

Image attached has the details. Please let me know how i can do this. The list- Customer Details would be the Primary source of data. The list are connected using the ID fields. There are similar post in this forum, but I just can't get my head around this. thanks in advance.HardshipRequirement.png

2 REPLIES 2
Roverandom
Solution Supplier
Solution Supplier

If you still can't solve it, you can try this.
I tried to make the lists based on your description:

Customer List:

Roverandom_0-1660673753472.png

 

Customer Details List:

Roverandom_1-1660673778459.png

 

Hardship List:

Roverandom_2-1660673803642.png

 

In the App.OnStart property, I defined my collection like this:

ClearCollect(
    ColHardship,
    RenameColumns( // I used the RenameColumns function because I used the Title column as Status. In your case, you may not need to use it.
        ShowColumns(
            AddColumns(
                CustomerDetails,
                "Customer Name",
                LookUp(
                    Customer,
                    ID = CustomerID
                ).Title,
                "IAM",
                LookUp(
                    Hardship,
                    ID = CustomerID
                ).IAM,
                "File Name",
                LookUp(
                    Hardship,
                    ID = CustomerID
                ).Title
            ),
            "AccountNumber",
            "Customer Name",
            "IAM",
            "File Name",
            "Title"
        ),
        "Title",
        "Status"
    )
)

 

The result is:

Roverandom_4-1660674140443.png

 

Is that what you need?



Hi! I hope I was helpfull. Please always mark the answer that helped you, so others who need it will find it faster.



Roverandom is a book by J.R.R Tolkien. I'm a big fan of his works and as Frodo, Aragorn or Gollum are already widely used, I decided to use this nickname.
kevingeorget
Resolver I
Resolver I

Thanks a lot for your reply @Roverandom . I'm sure the workaround you have provided will work. However, the logic seemed to be too complicated for a beginner like me.

I went with an alternative logic as below.

1. Created 3 collections.

2. Created dummy columns and then deleted its value so that i could create the required columns in the collection (using Collect and RemoveIf).

3. Use updateIf to iterate through collection and use Lookup to get the dependent values.

//Create a collection with Hardship list
ClearCollect(colViewHardshipRecords,ShowColumns('Hardship Tracker',"ID","ImpairedAssetManagerName","SMSFileName"));
//Create a collection with Customer list
ClearCollect(colViewCustomersRecords,ShowColumns(Customer,"Title","HardshipTrackerID","ID"));
//Create new column within the collection with a Sample value and then delete it
Collect(colViewCustomersRecords,{SMSFileName:"SAMPLE"});
RemoveIf(colViewCustomersRecords,SMSFileName="SAMPLE");
Collect(colViewCustomersRecords,{IAM:"SAMPLE"});
RemoveIf(colViewCustomersRecords,IAM="SAMPLE");
//Update the new columns with the values from the parent collection
UpdateIf(colViewCustomersRecords,IsBlank(SMSFileName),{SMSFileName:LookUp(colViewHardshipRecords,ID=Value(HardshipTrackerID)).SMSFileName});
UpdateIf(colViewCustomersRecords,IsBlank(IAM),{IAM:LookUp(colViewHardshipRecords,ID=Value(HardshipTrackerID)).ImpairedAssetManagerName.DisplayName});
//Create a collection with Customer Details list
ClearCollect(colViewCustomersDetailsRecords,ShowColumns('Customer Details',"Title","ReportingHardshipStatus","CustomerID"));
//Create new column within the collection with a Sample value and then delete it
Collect(colViewCustomersDetailsRecords,{CustomerName:"SAMPLE"});
RemoveIf(colViewCustomersDetailsRecords,CustomerName="SAMPLE");
Collect(colViewCustomersDetailsRecords,{IAM:"SAMPLE"});
RemoveIf(colViewCustomersDetailsRecords,IAM="SAMPLE");
Collect(colViewCustomersDetailsRecords,{SMSFileName:"SAMPLE"});
RemoveIf(colViewCustomersDetailsRecords,SMSFileName="SAMPLE");
Collect(colViewCustomersDetailsRecords,{HardShipTrackerID:"SAMPLE"});
RemoveIf(colViewCustomersDetailsRecords,HardShipTrackerID="SAMPLE");
//Update the new columns with the values from the parent collection
UpdateIf(colViewCustomersDetailsRecords,IsBlank(CustomerName),{CustomerName:LookUp(colViewCustomersRecords,ID=Value(CustomerID)).Title});
UpdateIf(colViewCustomersDetailsRecords,IsBlank(IAM),{IAM:LookUp(colViewCustomersRecords,ID=Value(CustomerID)).IAM});
UpdateIf(colViewCustomersDetailsRecords,IsBlank(SMSFileName),{SMSFileName:LookUp(colViewCustomersRecords,ID=Value(CustomerID)).SMSFileName});
UpdateIf(colViewCustomersDetailsRecords,IsBlank(HardShipTrackerID),{HardShipTrackerID:LookUp(colViewCustomersRecords,ID=Value(CustomerID)).HardshipTrackerID});

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,902)