Filtering Custom Fields

Bob Rickards

I have created an Awards Tile that look at our custom fields. I am using a Power Automate flow to populate the tile, but am struggling to figure out how to filter the tile based on a custom field category. Here is the tile:

The flow seems to be working but I just cant filter on the category. I am using a flow with a scope that and a condition that will post the custom fields if the count is greater than zero.

Since I am not using an array, I can't filter an array. Is there a way to filter the data before it goes to the tile? Thanks for the help.

Alex Wong

@Bob Rickards
going to need to see a little more of your flow to know how to help you better.

however, assuming your tile is in constituent page, meaning you are using the constituent record id as context to get constituent custom fields for a single constituent. RIGHT after your call to get custom fields for the constituent, you will use the Filter Array action to filter the array of custom fields that is returned, conditioning on category = the award custom field category. THEN you will have a condition on the length of the filter array action, if greater than 0, then process to create html table.

Bob Rickards

@Alex Wong I am borrowing from another flow that creates a tile. There is no array variable used. Here is more of the flow.

Using a variable to grab the systemrecordid and then use that to grab the custom records with same id. Is this not the best way to go?

Alex Wong

@Bob Rickards
Array are everywhere =D

you did not define and used an array variable, however, array are returned when you call List constituent custom fields. it returns an array (a list of) custom field(s) of a constituent.

AFTER List constituent custom fields action, use Filter array action, and provide it the value dynamic content returned by the List constituent custom fields action (which is the array/list of custom fields). and filter on category = your custom field category name for the award custom field.

then follow the rest of what i replied earlier (condition action on length(filter array) > 0 etc

when “yes” (length filter array greater than 0), you will do the create html table, but you provide the filter array output, not the list constituent custom fields output.

by the way, to clarify, you do not need the “for each” action.

Bob Rickards

@Alex Wong Seems like every time I drop in the Filter Array it creates a ‘For each’. When I pull out the filter from the for each and delete the for each..it just spins. I believe I am close…

Alex Wong

@Bob Rickards
Make sure the value you select has the description below that say “The set of items included in the response.” instead of the value that has the description that say “The value of the custom field”

if you mouse over the value dynamic content, it should show: outputs('List_constituent_custom_fields')?['body/value']

There is also a problem with your Create HTML table - Awards action, the From should ONLY be the body of the filter array, no value. and the value (category, value, date) should be from the Filter array action dynamic content, not from List constituent custom fields action

You do not need to do Type^of^Award as the Header, Power Automate doesn't allow you to type the “space” character but actually does allow it, so if you type it in notepad or anywhere else, you can paste in “Type of Award” directly without dealing with replacing the ^ with space character.

Lastly, i'm sure you want to have a good formatted display of date: formatDateTime(item()?['date'], 'd')

‘d' give you a short date format, but if you want other format, see link below

https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings

Bob Rickards

@Alex Wong That worked! Thanks so much….one last question…with the Filter Array…can I make multiple selections? Really appreciate the help.

Alex Wong

@Bob Rickards
You can do complex filtering using the “Edit in advanced mode”. However, this mode is VERY hard to type in (the physical typing of the characters are very difficult. So I recommend you do the typing in notepad then copy and paste over when you are done.

For example:

Filter on Category=Award, if you click on Edit in advanced mode, you see:

equals(item()?['category'], 'Award')

copy this out.

Then go back to “Edit in basic mode” and change your condition

click on Edit in advanced mode again, you will see:

equals(item()?['category'], 'Honor')

If you want or condition (Category is Award or Honor), you will do

or(equals(item()?['category'], 'Award'),equals(item()?['category'], 'Honor'))

while the post appears as @ space or(, there is NO SPACE between @or, just the post formating the color caused it.

or will only take 2 parameter of conditions, so if you have more, you will need to do multiple or

i.e.
@or(or(equals(item()?['category'], 'Award'), equals(item()?['category'], 'Honor')), equals(item()?['category'], 'Prize'))

Bob Rickards

@Alex Wong Worked like a charm! Note pad really does help and it is very picky about the verbiage. Thanks again.