Forms. How to make a search for a field, i.e. a list with a search through a database access (autocomplete)

In the form column settings, specify List-search.

In GetItem, the field is extracted as the entity name, not the key.

Implementing a procedure like fm_formCode_fieldCode_dict (editing via the interface).

CREATE PROCEDURE [dbo].[fm_isp_region_dict]
  @username nvarchar(256),
  @itemID nvarchar(128),
  @q nvarchar(128) = ''

AS
BEGIN
    if(isnull(@q, '')='') begin  -- (for usual select)
        select ' Not selected ' Text, 0 Value, 0 Selected
        union
        SELECT gr.name Text, gr.id Value, 0 Selected
        FROM dbo.as_geo_regions gr        
        order by Text
    end
    else begin
        select ' Not selected ' Text, 0 Id, getdate() date, '' [desc], 0 type
        union
        SELECT gr.name Text, gr.id id, getdate() date, '' [desc], 0 type
        FROM dbo.as_geo_regions gr
    where name like '%'+@q+'%'
    order by Text
    end
END

An important difference is that the optional parameter @q nvarchar (128)=’’.

When saving the SaveItem field, we are passed the entity name (not the foreign key)):

CREATE PROCEDURE [dbo].[fm_profile_saveItem]
   @username nvarchar(256),
   @itemID  nvarchar(256),
   @fieldemail nvarchar(256) = '',
   @fieldphone nvarchar(256) = '',
   @fieldcity nvarchar(256) = '' -- на входе текст а не ключ-число
AS
BEGIN
    update as_users
    set
        email=@fieldemail,
        phone=@fieldphone,
        regionID=(select top 1 id from as_geo_regions where name=@fieldcity)
    select 1 Result, 'Сохранено' Msg
END

As a result, the field can be set through the search for values in the form and adjust how data will be generated in this search.