cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jesuisasiatique
New Member

Implementing a mutli-value combobox in canvas app with SQL Server junction table

Background: I'm working on a canvas app connected to an Azure SQL database and would like to create a mutli-select combo box. For example, consider a scenario where I would like to capture the official languages used in each country. Some countries have no official language, some have one, and some more. Assume the following tables exist:

 

tbl_country is a table of countries and includes the following fields

  • id
  • name
  • other fields to capture country-specific details (population, capital city, currency, etc.)

tbl_language is table of languages and includes the following fields

  • id
  • name

tbl_country_language is a junction table to capture the official languages in each country. It includes the following fields

  • id (optional, if database and app allow composite key, then this may not be needed)
  • country_id
  • language_id

 

Goal: I envision an app that allows me to navigate through a list of countries to view and edit data specific to each country. I've itemized the goals below to hopefully make it easier to respond to specific aspects of the problem.

  1. The form to edit a specific country's data should include a combobox control that is connected with the tbl_country_language table.
  2. The combobox should display a list of language names from tbl_language for the user to select from.
  3. When writing to the database, the combobox should store the id of the country that is being modified as country_id and the id of any language selected as language_id in the tbl_country_language table. If multiple languages are selected for a country, there should be an equal number of rows in tbl_country_language.
  4. Users should be able to go back to any country record and edit the official languages selected. The combobox should display default values of the languages already selected (based on which records exist in tbl_country_language). If combobox selections are added, modified, or un-selected, the corresponding records in tbl_country_language should be added, updated, or removed.

Does anyone have any ideas on what the best way is to implement this (collections, patch, etc.)? Ideally, the solution should be as simple as possible since I have quite a few multiple-value fields that I'd like to implement. Specific code examples and instructions are appreciated!

 

Thanks!

0 REPLIES 0

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,271)