cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chris_young
Helper III
Helper III

Two SharePoint Lists one field in common, filter on end date and display in one gallery

Hello all, 

 

I have a report that I am trying to build in powerapps. I have two sharepoint lists, with nothing in common but a field called Billing Finish in both. I am trying to make a report that brings both lists together and shows anything with a Billing Finish date within 14 days of today. 

 

I have the filter for one of the lists and it works but I now need to figure out how to add the second list into this gallery so can filter on the same criteria.

 

Gallery Items: Filter(EMPLOYEE, 'Billing Finish' >= Today() And 'Billing Finish' <= DateAdd(Today(), 14, Days))

 

Should I have two galleries? I would prefer to have them together but I could get away with two. The ultimate goal is to send an email to the manager of these people letting them know this is about to expire. 

 

Any help would be appreciated. 

1 ACCEPTED SOLUTION

Accepted Solutions
StormEAnderson
Resolver I
Resolver I

You should collect both of them into a Collection using the ClearCollect() function. Depending on how you are loading the data, it'd probably be best to put in the app OnStart() something like this:

ClearCollect(newEmployeeDataSet,'Data Source 1'); // initializes the new collection
Collect(newEmployeeDataSet,'Data Source 2'); // adds the second data set to the collection

Then you just set your Gallery.Items property to newEmployeeDataSet instead of the individual data sources.

 

However because your datasets are different schemas, you will need to standardize both datasets into the same schema in order to collec them. This can be done with ShowColumns / AddColumns:

ClearCollect(
    newEmployeeDataSet,
    ShowColumns( // Show only the new columns we created
        AddColumns( // Create the columns to be standardized between each dataset
            'Data Source 1',
            "newBillingDataCol",
            'Billing Finish',
            "employeeCol",
            'Source 1 Emp Col'
        ),
        "newBillingDataCol",
        "employeeCol"
    )
);
Collect(
    newEmployeeDataSet,
    ShowColumns(
        AddColumns( // Use the same column names but now provide the different columns from dataset 2 to map them to
            'Data Source 2',
            "newBillingDataCol",
            'Billing Finish',
            "employeeCol",
            'Source 2 Emp Col'
        ),
    "newBillingDataCol",
    "employeeCol"
);

 

Hope that helps!

 

View solution in original post

5 REPLIES 5

You can bring them together in a collection then display the collection in a gallery. Also power Automate has a union data operation that can combine multiple lists https://docs.microsoft.com/en-us/power-automate/data-operations

StormEAnderson
Resolver I
Resolver I

You should collect both of them into a Collection using the ClearCollect() function. Depending on how you are loading the data, it'd probably be best to put in the app OnStart() something like this:

ClearCollect(newEmployeeDataSet,'Data Source 1'); // initializes the new collection
Collect(newEmployeeDataSet,'Data Source 2'); // adds the second data set to the collection

Then you just set your Gallery.Items property to newEmployeeDataSet instead of the individual data sources.

 

However because your datasets are different schemas, you will need to standardize both datasets into the same schema in order to collec them. This can be done with ShowColumns / AddColumns:

ClearCollect(
    newEmployeeDataSet,
    ShowColumns( // Show only the new columns we created
        AddColumns( // Create the columns to be standardized between each dataset
            'Data Source 1',
            "newBillingDataCol",
            'Billing Finish',
            "employeeCol",
            'Source 1 Emp Col'
        ),
        "newBillingDataCol",
        "employeeCol"
    )
);
Collect(
    newEmployeeDataSet,
    ShowColumns(
        AddColumns( // Use the same column names but now provide the different columns from dataset 2 to map them to
            'Data Source 2',
            "newBillingDataCol",
            'Billing Finish',
            "employeeCol",
            'Source 2 Emp Col'
        ),
    "newBillingDataCol",
    "employeeCol"
);

 

Hope that helps!

 

View solution in original post

@StormEAnderson 

 

thank you, that worked well. 

 

Do you by chance know how to send am email to everyone in that gallery? I can do lookups for their email address, but I'm not sure how to select everyone in that gallery. ForAll?

@chris_young You can use ForAll() but I would suggest Concat() would be a better tool for this -

 

If it's just an email string that is in each data source:

Concat(newEmployeeDataSet,employeeCol&";") // first@email.com;second@email.com;

If it's a SharePoint Person field or any other type of object and not just a text field:

Concat(newEmployeeDataSet,employeeCol.Email&";")

 The Intellisense will help you with the object field in-case it's something other than "Email".

@StormEAnderson 

 

Perfect, all working. Thank you

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,009)