{"id":2355,"date":"2024-10-08T08:54:28","date_gmt":"2024-10-08T08:54:28","guid":{"rendered":"https:\/\/vm-automatisierung.com\/?p=2355"},"modified":"2024-10-08T08:54:28","modified_gmt":"2024-10-08T08:54:28","slug":"how-to-multiselect-from-combobox-and-relate-it-to-sql-table-in-powerapps","status":"publish","type":"post","link":"https:\/\/vm-automatisierung.com\/?p=2355","title":{"rendered":"How to multiselect from Combobox and relate it to SQL Table in Powerapps"},"content":{"rendered":"\n
My specific Task at hand: <\/p>\n\n\n\n
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.<\/p>\n\n\n\nPrerequisites<\/summary>\n
\n
Gateway with connection to the SQL Database established<\/li>\n\n\n\n
Sharepoint List to store and afterwards use the information gained<\/li>\n<\/ul>\n<\/details>\n\n\n\n
<\/div>\n\n\n\n
Create a Powerapp from the SharePoint-List you want to save into<\/strong><\/p>\n\n\n\n
Fill it with the columns you wish to fill<\/p>\n\n\n\n
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<\/p>\n\n\n\n
<\/div>\n\n\n\n
Connect Combobox with onprem Dataset<\/strong><\/p>\n\n\n\n
Data –> Add data –> SQL Server
You will need verify this connection (I used windows credentials of a ServiceUser with permissions to access the DB)<\/em><\/p>\n\n\n\n<\/figure>\n\n\n\n
Select a Combobox<\/strong> from the Card you want to multiselect out of “Unlock to Change Properties” and under Item<\/strong> establish your SQL Connection<\/p>\n\n\n\n<\/figure>\n\n\n\n
<\/div>\n\n\n\n
Show and Save active directory and Mail-list<\/strong><\/p>\n\n\n\n
Create two more Cards –> one to show and save the active directory and one to show and save the mail-list<\/p>\n\n\n\n
here is the tricky part:<\/strong> 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<\/strong> 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.<\/p>\n\n\n\n
<\/div>\n\n\n\n
Solution<\/strong><\/p>\n\n\n\n
We want to use an ForAll <\/strong>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 <\/strong>function which returns the AD or Mail Value for each Group selected in a Concat<\/strong> function. –> this returns a string of all the outputs and can be used in ForAll.
To get the final table<\/strong> we Concat everything and then use the Split <\/strong>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:<\/p>\n\n\n\n
![\"\"](\"https:\/\/vm-automatisierung.com\/wp-content\/uploads\/2024\/10\/image-1.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/vm-automatisierung.com\/wp-content\/uploads\/2024\/10\/image-3.png\")
<\/figure>\n\n\n\n<\/div>\n\n\n\n![\"Logo-w\/oBack\"](\"https:\/\/vm-automatisierung.com\/wp-content\/uploads\/2023\/12\/Logo-ohne-hintergrundbild-2-83x98.png\"){kind=logo}
<\/a><\/div>\n\n\n