Single collection to pull records from 3 SP list

[kevingeorget]

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.

[Roverandom]

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

Customer List:

Customer Details List:

Hardship List:

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:

Is that what you need?

[kevingeorget]

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});