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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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