Note: In my examples I use Title instead of SiteID
Something like this could work, but you will run in to delegation issues if you have more than 2000 LiveSites:
Filter(
VTS_UserRolesList,
UserRole = "Security"
&& UserName = "PowerAppsUser"
&& Title in Filter(VTS_LiveSites, 'End Date' >= Today()).Title
)
Alternatively, this will work until your users have been active in more than 2000 sites:
With(
{MySites: Filter(VTS_UserRolesList, UserRole = "Security" && UserName = "PowerAppsUser")},
Filter(
MySites As MS,
LookUp(VTS_LiveSites As LS, MS.Title = LS.Title).'End Date' >= Today()
)
)
You could even cache the Active sites in a collection:
ClearCollect(colActiveSites, Filter(VTS_LiveSites, 'End Date' >= Today()))
Then use it in your Combobox:
With(
{MySites: Filter(VTS_UserRolesList, UserRole = "Security" && UserName = "PowerAppsUser")},
Filter(
MySites As MS,
MS.Title in colActiveSites.Title
)
)
I might be wrong, but I don't think you can fully delegate this kind of query with SharePoint. I hope one of the above is suitable in your use case, though
