cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WillPage
Impactful Individual
Impactful Individual

Filtering help please..

Hello.

I'm trying to filter a list of records based on a property within a record. It's the subscribedskus query in the Microsoft Graph API. Sample data below; you can see in every record within value there are a number of top level properties such as id and skupartNumber, but within those are servicePlans.

 

I need to display a list of items within value (column skuPartNumber for example), but filtered such that only items that have "exchange" in value.servicePlans.servicePlanName are displayed in the list

 

To complicate things a bit more I also need to substitute the skuID with a friendly name. I have got something going which seems pretty hacky:

 

AddColumns(con_skus.value, "friendlySku", Substitute(Substitute(skuID, "c7df2760-2c81-4ef7-b578-5b5392b571df", "Office 365 E5"), "cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46", "Microsoft 365 Business"))

I'm not convinced maintaining a list of skuID and friendly names in a horrible nested Substitute function is really what I want to be doing beyong an example like there where there are only a couple of subscribed licenses so any suggestions on how to manage this (maybe a sharepoint list?) would be apprecieted.

 

What's more pressing for me though, is the ability to filter so I can show a list of licenses where servicePlans.servicePlanName contains "exchange". Sample data below. I have this in a context varaible I'm working from, so the data soure is con_skus.value.

 

I've tried to mess around with the Filter and Search fuctions but I can't get anything to go.

{
    "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#subscribedSkus",
    "value": [
        {
            "capabilityStatus": "Enabled",
            "consumedUnits": 24,
            "id": "dcd219dd-bc68-4b9b-bf0b-4a33a796be35_c7df2760-2c81-4ef7-b578-5b5392b571df",
            "skuId": "c7df2760-2c81-4ef7-b578-5b5392b571df",
            "skuPartNumber": "ENTERPRISEPREMIUM",
            "appliesTo": "User",
            "prepaidUnits": {
                "enabled": 25,
                "suspended": 0,
                "warning": 0
            },
            "servicePlans": [
                {
                    "servicePlanId": "33c4f319-9bdd-48d6-9c4d-410b750a4a5a",
                    "servicePlanName": "MYANALYTICS_P2",
                    "provisioningStatus": "PendingProvisioning",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "b1188c4c-1b36-4018-b48b-ee07604f6feb",
                    "servicePlanName": "PAM_ENTERPRISE",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "3fb82609-8c27-4f7b-bd51-30634711ee67",
                    "servicePlanName": "BPOS_S_TODO_3",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "e212cbc7-0961-4c40-9825-01117710dcb1",
                    "servicePlanName": "FORMS_PLAN_E5",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "6c6042f5-6f01-4d67-b8c1-eb99d36eed3e",
                    "servicePlanName": "STREAM_O365_E5",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "8e0c0a52-6a6c-4d40-8370-dd62790dcd70",
                    "servicePlanName": "THREAT_INTELLIGENCE",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "8c7d2df8-86f0-4902-b2ed-a0458298f3b3",
                    "servicePlanName": "Deskless",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "07699545-9485-468e-95b6-2fca3738be01",
                    "servicePlanName": "FLOW_O365_P3",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "9c0dab89-a30c-4117-86e7-97bda240acd2",
                    "servicePlanName": "POWERAPPS_O365_P3",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "57ff2da0-773e-42df-b2af-ffb7a2317929",
                    "servicePlanName": "TEAMS1",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "8c098270-9dd4-4350-9b30-ba4703f3b36b",
                    "servicePlanName": "ADALLOM_S_O365",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "4de31727-a228-4ec3-a5bf-8e45b5ca48cc",
                    "servicePlanName": "EQUIVIO_ANALYTICS",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "9f431833-0334-42de-a7dc-70aa40db46db",
                    "servicePlanName": "LOCKBOX_ENTERPRISE",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "34c0d7a0-a70f-4668-9238-47f9fc208882",
                    "servicePlanName": "EXCHANGE_ANALYTICS",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "a23b959c-7ce8-4e57-9140-b90eb88a9e97",
                    "servicePlanName": "SWAY",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "f20fedf3-f3c3-43c3-8267-2bfdd51c0939",
                    "servicePlanName": "ATP_ENTERPRISE",
                    "provisioningStatus": "Success",
                    "appliesTo": "Company"
                },
                {
                    "servicePlanId": "4828c8ec-dc2e-4779-b502-87ac9ce28ab7",
                    "servicePlanName": "MCOEV",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "3e26ee1f-8a5f-4d52-aee2-b81ce45c8f40",
                    "servicePlanName": "MCOMEETADV",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "70d33638-9c74-4d01-bfd3-562de28bd4ba",
                    "servicePlanName": "BI_AZURE_P2",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "882e1d05-acd1-4ccb-8708-6ee03664b117",
                    "servicePlanName": "INTUNE_O365",
                    "provisioningStatus": "PendingActivation",
                    "appliesTo": "Company"
                },
                {
                    "servicePlanId": "b737dad2-2f6c-4c65-90e3-ca563267e8b9",
                    "servicePlanName": "PROJECTWORKMANAGEMENT",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "bea4c11e-220a-4e6d-8eb8-8ea15d019f90",
                    "servicePlanName": "RMS_S_ENTERPRISE",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "7547a3fe-08ee-4ccb-b430-5077c5041653",
                    "servicePlanName": "YAMMER_ENTERPRISE",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "43de0ff5-c92c-492b-9116-175376d08c38",
                    "servicePlanName": "OFFICESUBSCRIPTION",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "0feaeb32-d00e-4d66-bd5a-43b5b83db82c",
                    "servicePlanName": "MCOSTANDARD",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "efb87545-963c-4e0d-99df-69c6916d9eb0",
                    "servicePlanName": "EXCHANGE_S_ENTERPRISE",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "5dbe027f-2339-4123-9542-606e4d348a72",
                    "servicePlanName": "SHAREPOINTENTERPRISE",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "e95bec33-7c88-4a70-8e19-b10bd9d0c014",
                    "servicePlanName": "SHAREPOINTWAC",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                }
            ]
        },
        {
            "capabilityStatus": "Enabled",
            "consumedUnits": 2,
            "id": "dcd219dd-bc68-4b9b-bf0b-4a33a796be35_725422ed-e205-400e-ab0a-3899d8a398ca",
            "skuId": "725422ed-e205-400e-ab0a-3899d8a398ca",
            "skuPartNumber": "SCHOOL_DATA_SYNC_P2",
            "appliesTo": "User",
            "prepaidUnits": {
                "enabled": 2,
                "suspended": 0,
                "warning": 0
            },
            "servicePlans": [
                {
                    "servicePlanId": "113feb6c-3fe4-4440-bddc-54d774bf0318",
                    "servicePlanName": "EXCHANGE_S_FOUNDATION",
                    "provisioningStatus": "Success",
                    "appliesTo": "Company"
                },
                {
                    "servicePlanId": "500b6a2a-7a50-4f40-b5f9-160e5b8c2f48",
                    "servicePlanName": "SCHOOL_DATA_SYNC_P2",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                }
            ]
        },
        {
            "capabilityStatus": "Enabled",
            "consumedUnits": 2,
            "id": "dcd219dd-bc68-4b9b-bf0b-4a33a796be35_90d8b3f8-712e-4f7b-aa1e-62e7ae6cbe96",
            "skuId": "90d8b3f8-712e-4f7b-aa1e-62e7ae6cbe96",
            "skuPartNumber": "SMB_APPS",
            "appliesTo": "User",
            "prepaidUnits": {
                "enabled": 25,
                "suspended": 0,
                "warning": 0
            },
            "servicePlans": [
                {
                    "servicePlanId": "39b5c996-467e-4e60-bd62-46066f572726",
                    "servicePlanName": "DYN365BC_MS_INVOICING",
                    "provisioningStatus": "PendingProvisioning",
                    "appliesTo": "User"
                },
                {
                    "servicePlanId": "199a5c09-e0ca-4e37-8f7c-b05d533e1ea2",
                    "servicePlanName": "MICROSOFTBOOKINGS",
                    "provisioningStatus": "Success",
                    "appliesTo": "User"
                }
            ]
        }
    ]
}
1 ACCEPTED SOLUTION

Accepted Solutions
WillPage
Impactful Individual
Impactful Individual

If anyone's vaguely interested in this or find this thread trying to solve the same problem:

 

ForAll(AddColumns(con_skus.value, "servicePlansFiltered",  LookUp(servicePlans, "exchange" in Lower(servicePlanName))), If(Not(IsBlank(servicePlansFiltered)), skuPartNumber ))

Phew!

View solution in original post

3 REPLIES 3
WillPage
Impactful Individual
Impactful Individual

Bumping this - I'm really stuck. I can create a Radio picker with the value as the Items property, then have a data table with the Items set to Radio1.Selected.servicePlans and then filter on the results, which is cool, but I'd like to do this all in one control so I only display the records that have certain data in servicePlans.servicePlanName.

 

I've been trying out ForAll function and Concat to try and get into the records to evaluate the contents but I just can't figure it out.

WillPage
Impactful Individual
Impactful Individual

I think I'm half way there with this:

 

ForAll(con_skus.value.servicePlans, LookUp(servicePlans, "exchange" in Lower(servicePlanName)))

but I can only display the columns within servicePlans in a data table, not the "parent" value column. If I nest this in another ForAll(con_skus.value, ForAll(.... etc then the result is a table of records I can't figure out how to split apart into columns.

 

WillPage
Impactful Individual
Impactful Individual

If anyone's vaguely interested in this or find this thread trying to solve the same problem:

 

ForAll(AddColumns(con_skus.value, "servicePlansFiltered",  LookUp(servicePlans, "exchange" in Lower(servicePlanName))), If(Not(IsBlank(servicePlansFiltered)), skuPartNumber ))

Phew!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,951)