Forms. How to make dependent fields in a table (Country, City)

That is, when updating one field, the other field must also change its state.

  1. Setting the column field Dependent fields (dependentCols)comma-separated list (specify the fields that should be updated when this field is changed).
  2. In the procedureThe source will be an optional @fields DictionaryParameter parameter that passes the current state of the form (it should be optional as in the example).  IMPORTANT! It is only used in dependent columns. You don't need to specify it in normal items with a selection from the list.

Lead query (Countries). fields can be omitted from the parameters here (if you use it by default).

CREATE PROCEDURE [dbo].[fm_user_country_dict]
  @username nvarchar(256)
  @itemID int
AS
BEGIN
    select 'Unspecified' Text, 0 Value, 0 Selected
    union
    select name Text, id Value, 0 Selected from as_geo_regions where typeID = 1
    order by Text

END

Slave request (cities). It is important to observe all the nuances of using fields+ to search both by id and by name using or as in the example.

CREATE PROCEDURE [dbo].[fm_user_country_dict]
  @username nvarchar(256)
  @itemID int
  @fields DictionaryParameter READONLY

AS
BEGIN
    declare @country nvarchar(128)
    select @country = Value from @fields where [key] = 'country'
    exec dbo.as_print @str = @country

        select 'Не выбрано' Text, 0 Value, 0 Selected
    union
    select name Text, id Value, 0 Selected from as_geo_regions where typeID = and
        (cast(parentID as nvarchar) = @country or isnull(@country, '') = '' or parentID 
		in (select id from as_geo_regions where name = @country))
    order by Text
END
  1. Markup in the form of any, you can use for example this:
    <label>Country/City </label> {colcontrol-country} {colcontrol-city}
    

  1. In the GetItem procedure, set values for both fields.
isnull((select name from as_geo_regions
    where id = (select parentID from as_geo_regions where id = regionID)), 'Unspecified') country,
isnull((select name from as_geo_regions
    where id = regionID), 'Unspecified') city 

  1. As a result the fields will work as dependent fields.

Note:

Dependent columns work not only for list items, but also for text and other fields.

To do this, we also specify dependent fields and set a non-list value value store, which must return 1 string with Text and Value:

  1. Text-sets the element value.
  2. Value - if 0, hides the element.

Example:

CREATE PROCEDURE [dbo].[fm_user_email_dict]
   @username nvarchar(256),
   @itemID nvarchar(128),
   @fields DictionaryParameter READONLY --- if you need to make dependent columns (in
                                            collections are passed the state of all columns)
AS
BEGIN
        
        DECLARE @skype nvarchar(128)
        SELECT @skype = Value FROM @fields WHERE [Key] = 'skype'
       
        if(@skype<>'')BEGIN
            SELECT 'I have Skype' Text, 1 Value, 0 Selected
        END else BEGIN
            SELECT '' Text, 0 Value, 0 Selected
        END
END

INPORTANT. Hiding visibility will not work for list fields!

Falcon Space is a functional web development platform on a narrow stack MS SQL/Bootstrap. Falcon Space Gettting started
{sp-shortDemostandLinks}

SQL-tool for creating personal accounts on the site

The essence of the approach and the history of the creation of Falcon Space
Web platform for creating personal accounts

Falcon Space Platform

This is a reduction in the cost of ownership

at the expense of fewer people to support

This is a quick change

while using the program

This is a modern interface

full adaptation for mobile devices

MS SQL web applications. Affiliate program for developers and web studios

You can develop on your own or collaborate with us on Falcon Space web development using only SQL and HTML.
See examples with SQL code
Platform documentation
Working on MS SQL Server
Note