@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!
|
