Add single choice dropdown to a form, get values from different table

[castlekaybee]

I have the below database schema. I have created a form to edit "dboaggregatsliste". Can you show me an example how to set up a dropdown field to select the "ag_aggregatstyp" from the foreign table by showing the actual name ("kurzname") instead of the id?

Thanks in advance.

SCHEMA:

dbobasisaggregatstyp {
	at_id int pk
	kurzname nvarchar(100)
	bezeichnung_de nvarchar(500)
	bezeichnung_en nvarchar(500)
	kommentar nvarchar(1000)
	geaendertam datetime
	geaendertvon nvarchar(100)
}

dboaggregatsliste {
	ag_id int pk
	ag_aggregatstyp int > dbobasisaggregatstyp.at_id
	ag_aggregatsversion int > dbobasisaggregatsversion.av_id
	modellnummer int
	stuecklistebasispos int
	stuecklisteinkrement int
	kommentar nvarchar(1000)
	geaendertam datetime
	geaendertvon nvarchar(100)
	zeichnungsnummer nchar(50)
	zeichnungsdatum datetime
}

FORM:

select 'dynamic' as component, sqlpage.run_sql('shell.sql') as properties;

SELECT 'form' AS component,
    'Aggregat bearbeiten' AS title,
	'Speichern' as validate,
	'teal' as validate_color,
	
    'QueryAggregatUpdate.sql' || COALESCE('?ag_id_from_url=' || $ag_id_from_url, '') AS action

SELECT 'Aggregatstyp' AS name,
    'TRUE' AS required,
	2 as width,
    (SELECT ag_aggregatstyp FROM AggregatsListe WHERE AG_ID = $ag_id_from_url) AS value;

SELECT 'Kurzbezeichnung' AS name,
    'TRUE' AS required,
	2 as width,
    (SELECT Kurzname FROM AggregatsListe WHERE AG_ID = $ag_id_from_url) AS value;

-- button
select 
    'button' as component;
select 
    'QueryAggregatDelete.sql' || COALESCE('?ag_id_from_url=' || $ag_id_from_url, '') as link,
    'Löschen'            as title;
	
select 
    'button' as component;
select 
    'PageAggregate.sql' as link,
    'Abbrechen'            as title;

[lovasoa]

You can generate a dropdown using 'select' as type, and if you are using Mircrosoft SQL Server, you can use FOR JSON PATH to generate the list of options.

https://sql.ophir.dev/documentation.sql?component=form#component

[lovasoa]

In the end, it should look something like this:

SELECT 'Aggregatstyp' AS name,
  1 AS required,
  'select' as type,
  (SELECT
      at_id AS value,
      kurzname AS label
  FROM
      dbobasisaggregatstyp
  FOR JSON AUTO) as options;

edit: this post initially contained an error (AS id instead of AS value in the subquery)

[castlekaybee]

Thanks for the quick reply. It works beautifully.

However I cannot get the dropdown to show the value currently set in the given record. Adding

,2 as value

or any other number or string does not preselect the dropdown. It always shows the first item from the options.

SELECT 'Aggregatstyp' AS name,
  1 AS required,
  'select' as type,
  15 as value,
  (SELECT
      at_id AS value,
      kurzname AS label
  FROM
      BasisAggregatsTyp
  FOR JSON AUTO) as options;

[castlekaybee]

I have found another issue. I cannot get the selected value of the dropdown field after form submission (:Aggregatstyp).

FORM.sql:

SELECT 'form' AS component,
    'Aggregat auswählen' AS title,
	'Hinzufügen' as validate,
	'teal' as validate_color,
	'AggID' as name,
	'/UI/Maschinen/QueryMaschinenteilInsert.sql?br_id_from_url=' || $br_id_from_url AS action;

SELECT 'Aggregatstyp' AS name,
  1 AS required,
  'select' as type,
  (SELECT
      AG_ID AS id,
      Aggregat AS label
  FROM
      AbfrageAggregatsListe
  FOR JSON AUTO) as options;

QueryMaschinenteilIsert.sql:

-- debug
select 'text' as component,
:Aggregatstyp as title,
$br_id_from_url as contents;

-- INSERT INTO MaschinenTeile...

result:

[castlekaybee]

I will open a seperate thread, this seems to be a bug