Passing multiple constituent codes in the List Constituents action in Power Automate

Ben Regier

The List Constituents endpoint in SKY API allows you to pass multiple constituent codes in a single request.  If your database makes extensive use of constituent codes, like ours does, this is a very useful way of narrowing down the results that the endpoint returns. 


But as far as I can tell, the List Constituent action in the Power Automate connector only allows you to pass a single constituent code.  Am I correct in that?  Or is there some sort of delimiter that would allow me to pass more than one?


Are there plans to update the connector to allow passing more than one constituent code?  I know that a workaround would be to create a list using the constituent codes and then select that list in the connector, but I'd like to be able to pass at least some of these codes dynamically.

Ben Lambert

Hi Ben,


Good observation!  You are correct in that, currently, the Power Automate connector List constituents action only supports specifying a single value for the constituent code filter, even though the underlying  ListConstituents endpoint in the SKY API supports multiple values.  I'll try to provide some explanation and possible workaround here.

OpenApi v2 (and OpenApi v3) has a mechanism to describe array query parameters (i.e., parameters that can accept a list of values).  When you define a query parameter as an array you can specify a collectionFormat property to indicate the way the array items can be expressed. There are a few options:

- csv (the default value)

- ssv (space-separated values)

- tsv (tab-separated values)

- pipes (pipe-separated values)

- multi (multiple instances)


Most (but not all) of the array parameters in SKY API are implemented using as collectionFormat: multi, which means you can specify multiple instances of the parameter like:
?constituent_code=foo&constituent_code=bar


(that may look like an odd phrasing, but it handles cases where the parameter values have a delimiter in them (like "Board, Past member").


The csv format means you can treat the parameter as a comma-separated list of values (assuming the values don't have commas in them).  So the equivalent phrasing to the above would be:
?constituent_code=foo,bar


It turns out that some of the operations in the SKY APIs actually support both techniques.  For example, you can call ListGifts and filter the list of funds using EITHER csv or multi. So both of these work and are equivalent (and the backend will interpret this as a logical OR and return gifts to either fund 6 OR fund 7:
?fund_id=6,7
?fund_id=6&fund_id=7


Currently, Power Automate connectors ONLY support csv format for array parameters.  But the implementation for this endpoint on the Blackbaud side ONLY supports multi.  So right now in the Power Automate UI you can only specify a single constituent code filter (but note that other array query parameters on that operation do support csv so you can provide comma-separated values in the UI).  In order to make it more obvious which parameters support csv, we recently updated the hint text in the connector to be of the form "Represents a comma-separated list of...".


Right now we don't have plans to change the connector definition because it would require a change to the underlying API.  But we'll keep a pulse on this, and if that stance changes in the future we'll let you know. 


In the meantime, in addition to the workaround you mentioned of creating a list that itself is pre-filtered you could also use multiple actions within the flow (where each action provides a different constituent code for the filter). and then process the aggregated results (note that you may need to account for duplicates if a constituent has multiple constituent codes).  Using a pre-filtered list is preferred if possible because you can avoid multiple calls from the flow and push that filtering/de-duplication work to the Blackbaud server instead.  But if you need to dynamically specify multiple constituent codes to filter, you'll have to use multiple actions in your flow.


(btw - we are aware of a bug in the OpenApi being emitted by our developer portal where array parameters are being reported as type: string instead of  type: array with collectionFormat details - the docs are correct though)


I hope this helps - please let us know if you have any questions, and thanks for the feedback!

Ben Regier

Thanks Ben Lambert‍, for taking the time to fill in the background.  Very helpful information, and it also made me realize that I had misunderstood the API functionality.  I thought it would return only the records that had ALL of the constituent codes I passed, but I see now that it returns the records with ANY of the constituent codes passed.  So building a list of records that have two specific codes is always going to involve two calls - whether you're using the BB connector in Automate or using the API directly.


In Automate, the relevant expression would be intersection(), which takes multiple collections and returns the items that appear in all of them.  So that's a good option to have for instances where I need to do this dynamically.


Thanks again for the clarification.