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

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (100,585)