How to multiselect from Combobox and relate it to SQL Table in Powerapps

My specific Task at hand:

The objective is to develop a PowerApp that enables users to select specific groups within the organizational structure and retrieve the corresponding email addresses of all individuals stored in the Active Directory for the selected group. This solution will streamline the process of accessing and managing contact information within internal directory system.

Prerequisites

• Gateway with connection to the SQL Database established
• Sharepoint List to store and afterwards use the information gained

Create a Powerapp from the SharePoint-List you want to save into

Fill it with the columns you wish to fill

Allow multiselect for the column that you want to select the input out of
Create at least two more columns for active directory and e-mails

Connect Combobox with onprem Dataset

Data –> Add data –> SQL Server

You will need verify this connection (I used windows credentials of a ServiceUser with permissions to access the DB)

Select a Combobox from the Card you want to multiselect out of “Unlock to Change Properties” and under Item establish your SQL Connection

Show and Save active directory and Mail-list

Create two more Cards –> one to show and save the active directory and one to show and save the mail-list

here is the tricky part:
since we want every single one of the possibly selections from the group field to give us one or more active directory with inturn gives us one or more mail-addresses

we have two n:m connections which is something that requires many workarounds in powerapps , which is the reason for my blogpost.

For datasets with fewer than 500 entries, the “in” operator can be utilized effectively. However, since our dataset is growing and already exceeds 500 entries, we need to implement a non-delegable solution. Additionally, it is best practice to avoid relying on such fragile methods to ensure scalability and robustness in handling larger datasets.

Solution

We want to use an ForAll function to iterate through the the SelctedItems of the DataCard.

This poses the problem that ForAll doesn’t allow tables to be returned for each of the entries (it’s a n:1 connection).
To make it n:m we need to use a workaround again:

Nest the Filter function which returns the AD or Mail Value for each Group selected in a Concat function. –> this returns a string of all the outputs and can be used in ForAll.

To get the final table we Concat everything and then use the Split function over the entire Code. I also used the Distinct function to ensure every entry is only represented one time.

In my case the result looks like this:

Distinct(
Split(Concat(
        ForAll(
            Split(
                Concat(
                    DataCardValue2.SelectedItems,
                    Value,
                    ","
                ),
                ","
            ),
            Concat(
                Distinct(
                    Filter(
                        VM-Automatisierung_Groups,
                        Service = Value
                    ),
                    AD_Gruppe
                ),
                Value,
                ","
            )
        ),
        Value,
        ","),","
),Value)