filter array

[cmegow]

I am stumped with how to filter my array with another array.  I have two arrays.  One for BuidlingList which has all of our locations and another which is CustodialReportList which has all the survey results for the last month for each buidling.  I need to filter the BuildingList and remove any locations which also appear on the CustodialReportList so that I can then union them to create a final report with all locations, even those who did not receive a custodial report that month.  I've tried a million different configurations, but come up short every time.  Can someone point me to a good tutorial?

Grateful for your help.

[Ramiz]

Hi @cmegow ,

Do you have location field in both lists? if yes then you can get all items from custodial report list and then filter the building list using location filter. If the result has some output you can delete or skip that record.

[cmegow]

@Ramiz thanks for the tip.  Yes, both arrays have a location field.  I have tried every combination I can think of to make the filter.  Do you have an example that I can see?

[Nived_Nambiar]

Hi @cmegow 

Could you share input arrays sample and also the output array format which is expected , i have some doubts so may be by looking into this, it will be cleared.

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[Ramiz]

Hi @cmegow 

I am doing something similar with dataverse connector using OData filter

Something like this might help you. Filter the buidling list based on location column from custodial report.

[cmegow]

@Nived_Nambiar 

Here is an example of Array 1 BuildingList

"body": [
        {
            "School": "A. Z. Kelley Elementary",
            "Custodial Company": "HES",
            "Entrance": null,
            "Hallways": null,
            "Cafeteria": null,
            "Classrooms": null,
            "Restrooms": null,
            "Library": null,
            "Grounds": null,
            "Comments": null
        },
        {
            "School": "Academy at Hickory Hollow",
            "Custodial Company": "HES",
            "Entrance": null,
            "Hallways": null,
            "Cafeteria": null,
            "Classrooms": null,
            "Restrooms": null,
            "Library": null,
            "Grounds": null,
            "Comments": null
        },
        {
            "School": "Dan Mills Elementary",
            "Custodial Company": "ABM",
            "Entrance": null,
            "Hallways": null,
            "Cafeteria": null,
            "Classrooms": null,
            "Restrooms": null,
            "Library": null,
            "Grounds": null,
            "Comments": null
        },
        {
            "School": "Lawson High",
            "Custodial Company": "ABM",
            "Entrance": null,
            "Hallways": null,
            "Cafeteria": null,
            "Classrooms": null,
            "Restrooms": null,
            "Library": null,
            "Grounds": null,
            "Comments": null
        }
    ]
}

Here is an example from the CustodialReportList

{
    "body": [
        {
            "School": "Dan Mills Elementary",
            "Custodial Company": "ABM",
            "Entrance": 3,
            "Hallways": 3,
            "Cafeteria": 3,
            "Classrooms": 3,
            "Restrooms": 2,
            "Library": 3,
            "Grounds": 3,
            "Responsiveness": 3,
            "Comments": "<div class=\"ExternalClass49FA630B787649309428A3139E7C504E\">We are in transition with new staff.  We need the folowing&#58; \n\nCleaning equipment for the custodian cleaning rooms 19-23. (broom, mop, vacume etc)\n\nWe need a heavy duty vacume for the upstairs.</div>"
        }
    ]
}

This would be the expected outcome:

"body": [
        {
            "School": "A. Z. Kelley Elementary",
            "Custodial Company": "HES",
            "Entrance": null,
            "Hallways": null,
            "Cafeteria": null,
            "Classrooms": null,
            "Restrooms": null,
            "Library": null,
            "Grounds": null,
            "Comments": null
        },
        {
            "School": "Academy at Hickory Hollow",
            "Custodial Company": "HES",
            "Entrance": null,
            "Hallways": null,
            "Cafeteria": null,
            "Classrooms": null,
            "Restrooms": null,
            "Library": null,
            "Grounds": null,
            "Comments": null
        },
        {
            "School": "Lawson High",
            "Custodial Company": "ABM",
            "Entrance": null,
            "Hallways": null,
            "Cafeteria": null,
            "Classrooms": null,
            "Restrooms": null,
            "Library": null,
            "Grounds": null,
            "Comments": null
        }
    ]
}

[creativeopinion · Accepted solution]

@cmegow Because your Custodial Report List already includes the location—what is reason you need to cross-reference the two lists?.. 

In your case, I don't believe you need to do any filtering. I think the logic in your flow needs to be adjusted. You've indicated that you would like to create a final report that lists all locations—even if they didn't have a Custodial Report. 

In this case, you should do the following. As mentioned in a previous post, you should adjust the way your flow is built. Tip: Limit the top count of items returned to a smaller number while you are building and testing your flow. It will speed up your flow runs—especially if you have a large list of items.

Apply to Each

The Apply to each action in your flow (how you've used it isn't necessary). The Get Items action returns the list of items from your SP List in an array. In the screenshot below I've used a Compose action to output the value dynamic content from the Get Items action. 

What you've done in your flow is use an Apply to each action to loop through each item returned and appended it to an array. This is unnecessary as the output is the identical to the output that the value dynamic content from the Get Items action.

Select Action

Use the Select action to pull the locations only. Note: Scope action is optional—I use them in my flows to keep things organized.

This Select action will pull the locations into an array.

Select Custodial Report Data

For your Custodial Report list, you can use the Select action to pull the values you want to return. This is optional—not required.

Apply to Each Location

Now that you have an array of locations, you can use an Apply to Each action to loop through each location. I'm assuming your Building List contains one record per location. 

This next Compose action is optional, however it'll help you to understand what is going on in your flow. Insert a Compose action to hold the current item. This will display the current location being looped through. It can help with troubleshooting.

Filter Custodial Report

I'm unsure if this is a requirement but if you would like to list the report for each school you can then use a Filter Array action to do this. To learn more about how to use the Filter Array action, please refer to this recent YT Tutorial I uploaded.

Insert the Value dynamic content from the Get Items (Custodial Report) action into the From field.

In the first value field, insert the School (or whatever the name of the dynamic content is) dynamic content, leave the operator to is equal to and insert the outputs from the Compose action above (storing the current school being looped through).

Insert a Compose action to store the number of items returned. This is optional but can help with troubleshooting.

Run a test. Verify that the Filter Array action is filtering out your items properly. 

Initialize a String Variable

Next you will need to initialize a string variable in the root of your flow. You can add this at the top after the flow trigger or right before the Apply to each action. 

Since you've indicated that you want to display buildings that may not have any custodial reports—you need a container (variable) to store report items if you have any, and another value if you do not.

Add a Condition Check

Add a Condition to your flow to check the number of items returned from the Filter Array action. Insert the outputs from the Compose action storing the count of items returned from the Filter Array action into the first value field. 

Change the operator to is not equal to and enter 0. 

In the NO branch (this means the item count is equal to 0—no items returned from the Filter Array action), you can set the variable. I've chosen to use some static text along with dynamic content (the outputs of the Compose action storing the current item — aka the current location being looped through). 

Apply to Each (Filter Array Item)

In the YES branch (meaning the item count is not equal to 0–there are items returned from the Filter Array action), add an Apply to each action. 

Use this action to loop through each item returned from the Filter Array action. 

Refer to this part of the YT Tutorial that talks about how to access the dynamic content from a Filter Array action. You'll need to use an expression to access the dynamic content from the Filter Array action. 

Append to String Variable

Since the Apply to Each action in the Yes branch will be looping through each filtered custodial item (for the current location), you will need a way to gather the details each time it loops through. 

Add an append to string variable action. This action will collect the content of the Compose action above each time it loops through.

Add a Compose action after the condition branches. If you'd like to add a heading title to the top of the list of custodial reports for the location you can. I'm not sure if you plan on sending a report via email or another way.

I've added an H2 HTML tag to wrap the current location being looped through.

Add a Set Variable action. You will want to clear out the variable each time the Apply to Each action reaches the end of the loop (before it loops through a new location). Otherwise, the append to string action will continue to append to the string which isn't what you want. 

Insert the null function to empty out the variable.

Run a test. Check the outputs of the Compose action with the variable. 

Alternatively, you can use a Create HTML Table action and do without the Apply to Each action if you prefer. 

You will need additional actions to pull everything together in a single report.

More to come....

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉Watch my tutorials on YouTube
👉Tips and Tricks on TikTok

[cmegow]

@creativeopinion thanks.  The video link gave me the information I needed to rework the flow.  Thank you.