create_new and Multi-Step Form #1002
Replies: 4 comments 3 replies
-
|
Hello and welcome to SQLPage ! Could you please create a minimal reproduction of your issue ? Ideally a single self-contained sql file that clearly reproduces your issue without any unrelated code. |
Beta Was this translation helpful? Give feedback.
-
|
@lovasoa First, thank you for your answer and suggestions. Below, I include the complete code to reproduce my issue. The problem is related to the 'site_name' field, also labeled 'Site Name,' which allows creating new entries in the table Site. Regarding splitting my form on different screens, I am somewhat hesitant to do so, as the data entry process is already a bit too long, where the user must go through Field Registration -> Rainfall Configuration -> Soil Sample Analysis -> Runoff Simulation -> Review & Submit. CREATE TABLE IF NOT EXISTS temp_site (
id INTEGER PRIMARY KEY,
loc_name TEXT NOT NULL,
country_code TEXT NOT NULL
);
WITH src(id, loc_name, country_code) AS (
VALUES
(1, 'Petzenkirchen', 'AT'),
(2, 'Wieselburg', 'AT'),
(3, 'Tartu', 'EE'),
(4, 'Brasília', 'BR'),
(5, 'Hohenheim', 'DE'),
(6, 'Rothamsted', 'GB')
)
INSERT INTO temp_site (id, loc_name, country_code)
SELECT id, loc_name, country_code FROM src
ON CONFLICT (id) DO UPDATE
SET loc_name = EXCLUDED.loc_name,
country_code = EXCLUDED.country_code;
-- Render as a SQLPage table
SELECT
'table' AS component,
'Sites (Demo)' AS title,
'tblsite' AS id;
SELECT
id AS "ID",
loc_name AS "Site",
country_code AS "Country"
FROM temp_site
ORDER BY id;
SELECT
'form' AS component,
'' AS title,
'field_plot' AS id,
CASE
WHEN $action = 'Edit' THEN 'Update field'
WHEN :no_tillage IS NULL THEN 'Next'
ELSE 'Add field'
END AS validate,
CASE
WHEN $action = 'Edit' AND $id IS NOT NULL THEN 'actions/update_plot.sql'
WHEN :no_tillage IS NULL THEN ''
ELSE 'actions/insert_field_plot.sql'
END as action,
'Reset' as reset;
SELECT 'header' AS type,
'Location Information' AS label;
-- SET variables FIRST, with proper type casting
SET existing_site_id = (
SELECT id
FROM site
WHERE id = CASE
WHEN :site_name IS NOT NULL AND :site_name ~ '^[0-9]+$' THEN CAST(:site_name AS INT)
ELSE NULL
END
);
SET site_loc_name = (
COALESCE((SELECT loc_name
FROM site
WHERE id = CAST($existing_site_id AS INTEGER))::TEXT,:site_name) -- Cast to INTEGER for comparison
);
-- Site Name dropdown
-- Here is the problem
SELECT
'site_name' AS name,
'Site Name' AS label,
'select' AS type,
TRUE as create_new,
TRUE AS dropdown,
TRUE AS required,
'Type the Site Name' as empty_option,
3 AS width,
NOT ($action IN ('Edit', 'INSERT')) AS disabled,
$site_loc_name AS value,
(SELECT json_agg(json_build_object('label', loc_name, 'value', id) ORDER BY loc_name) FROM temp_site) AS options;
-- Country dropdown
SELECT 'site_country' AS name,
'Country' AS label,
'select' AS type,
3 AS width,
TRUE AS required,
TRUE AS dropdown,
'Select a Country' AS empty_option,
NOT ($action IN ('Edit', 'INSERT')) AS disabled,
COALESCE(
NULLIF(:site_country, ''),
(
SELECT v.country_code
FROM (
VALUES
(1, 'AT'), -- Petzenkirchen
(2, 'AT'), -- Wieselburg
(3, 'EE'), -- Tartu
(4, 'BR') -- Brasília
) AS v(site_id, country_code)
WHERE v.site_id = CASE
WHEN :site_name ~ '^[0-9]+$' THEN CAST(:site_name AS INT)
ELSE NULL
END
)
) AS value,
(
SELECT json_agg(
json_build_object(
'label', v.short_name_en || ' (' || v.id || ')',
'value', v.id
)
ORDER BY v.short_name_en
)
FROM (
VALUES
('AT', 'Austria'),
('EE', 'Estonia'),
('BR', 'Brazil'),
('DE', 'Germany'),
('GB', 'United Kingdom')
) AS v(id, short_name_en)
) AS options;
-- Display fields
SELECT
'site_name_valeu' AS name,
'Site Name' AS label,
'text' AS type,
3 AS width,
$site_loc_name AS value;
SELECT
'site_id_valeu' AS name,
'Site ID' AS label,
'text' AS type,
3 AS width,
$existing_site_id AS value;
select 'number' as type, 'no_tillage' as name, 'Number of Primary tillage operation' as label, 'How many tillage operation?' as placeholder, 1 as min, 10 as max,
coalesce(NULLIF(:no_tillage, '')::int, 1) as value,
:no_tillage is not null as readonly,
4 as width
;
select 'number' as type, 'no_tillage_2' as name, 'Number of Secondary tillage operation' as label, 'How many tillage operation?' as placeholder, 0 as min, 10 as max,
coalesce(NULLIF(:no_tillage_2, '')::int, 0) as value,
:no_tillage_2 is not null as readonly,
4 as width
;
WITH RECURSIVE
params AS (
SELECT
GREATEST(0, COALESCE(NULLIF(:no_tillage, '')::int, 0)) AS n_primary,
GREATEST(0, COALESCE(NULLIF(:no_tillage_2, '')::int, 0)) AS n_secondary
),
primary_ids AS (
SELECT 1 AS id FROM params p WHERE p.n_primary > 0
UNION ALL
SELECT id + 1 FROM primary_ids, params p WHERE id + 1 <= p.n_primary
),
secondary_ids AS (
SELECT 1 AS id FROM params p WHERE p.n_secondary > 0
UNION ALL
SELECT id + 1 FROM secondary_ids, params p WHERE id + 1 <= p.n_secondary
),
tillage_ids AS (
SELECT id, 'Primary'::text AS tillage_type FROM primary_ids
UNION ALL
SELECT id, 'Secondary'::text AS tillage_type FROM secondary_ids
),
kinds AS ( -- generate rows per slot in this order
SELECT 'select'::text AS kind, 0 AS ord
UNION ALL SELECT 'date', 1
UNION ALL SELECT 'depth', 2 -- number input (Primary only)
)
SELECT
CASE WHEN k.kind = 'select' THEN 'select'
WHEN k.kind = 'date' THEN 'date'
ELSE 'number'
END AS type,
CASE
WHEN k.kind = 'select' AND t.tillage_type = 'Primary' THEN 'primary_tillage_' || t.id || '[]'
WHEN k.kind = 'select' THEN 'secondary_tillage_' || t.id || '[]'
WHEN k.kind = 'date' AND t.tillage_type = 'Primary' THEN 'primary_tillage_management_date_' || t.id || '[]'
WHEN k.kind = 'date' THEN 'secondary_tillage_management_date_' || t.id || '[]'
WHEN k.kind = 'depth' AND t.tillage_type = 'Primary' THEN 'primary_plough_depth_cm_' || t.id || '[]'
ELSE 'secondary_plough_depth_cm_' || t.id || '[]'
END AS name,
CASE
WHEN k.kind = 'select'
THEN t.tillage_type || ' Tillage operation ' || t.id
WHEN k.kind = 'date'
THEN 'Operation date ' || t.id
ELSE 'Tillage Depth ' || t.id || ' [cm] '
END AS label,
CASE WHEN k.kind = 'select' THEN TRUE ELSE NULL END AS dropdown,
CASE WHEN k.kind = 'select' THEN TRUE ELSE NULL END AS multiple,
CASE WHEN k.kind = 'select' THEN 5
WHEN k.kind = 'date' THEN 3
ELSE 4
END AS width,
CASE WHEN k.kind = 'select'
THEN 'Select ' || lower(t.tillage_type) || ' tillage operation'
ELSE NULL
END AS empty_option,
NOT ($action IN ('Edit', 'INSERT')) AS disabled,
-- number-specific attributes (only used for depth rows)
CASE WHEN k.kind = 'depth' THEN 'any' ELSE NULL END AS step,
CASE WHEN k.kind = 'depth' THEN 0 ELSE NULL END AS min,
CASE WHEN k.kind = 'depth' THEN 100 ELSE NULL END AS max,
/* Keep value as TEXT across all rows */
CASE
/* --- Selected operation defaults (Primary) --- */
WHEN k.kind = 'select' AND t.tillage_type = 'Primary' AND t.id = 1 THEN
COALESCE(
NULLIF(:primary_tillage,'')::int,
(
SELECT pms.operation_id
FROM plot_management_schedule pms
JOIN management_operations mo ON pms.operation_id = mo.id
WHERE pms.plot_id = CAST($id AS INTEGER)
AND mo.operation_type = 'Primary'
ORDER BY pms.operation_date DESC
LIMIT 1
)
)::text
WHEN k.kind = 'select' AND t.tillage_type = 'Primary' THEN
(
SELECT pms.operation_id
FROM plot_management_schedule pms
JOIN management_operations mo ON pms.operation_id = mo.id
WHERE pms.plot_id = CAST($id AS INTEGER)
AND mo.operation_type = 'Primary'
ORDER BY pms.operation_date DESC
OFFSET (t.id - 1) LIMIT 1
)::text
/* --- Selected operation defaults (Secondary) --- */
WHEN k.kind = 'select' AND t.tillage_type = 'Secondary' AND t.id = 1 THEN
COALESCE(
NULLIF(:secondary_tillage,'')::int,
(
SELECT pms.operation_id
FROM plot_management_schedule pms
JOIN management_operations mo ON pms.operation_id = mo.id
WHERE pms.plot_id = CAST($id AS INTEGER)
AND mo.operation_type = 'Secondary'
ORDER BY pms.operation_date DESC
LIMIT 1
)
)::text
WHEN k.kind = 'select' AND t.tillage_type = 'Secondary' THEN
(
SELECT pms.operation_id
FROM plot_management_schedule pms
JOIN management_operations mo ON pms.operation_id = mo.id
WHERE pms.plot_id = CAST($id AS INTEGER)
AND mo.operation_type = 'Secondary'
ORDER BY pms.operation_date DESC
OFFSET (t.id - 1) LIMIT 1
)::text
/* --- Management date defaults (both types) --- */
WHEN k.kind = 'date' THEN
COALESCE(
NULLIF(
CASE
WHEN t.tillage_type = 'Primary' AND t.id = 1 THEN :primary_tillage_management_date
WHEN t.tillage_type = 'Secondary' AND t.id = 1 THEN :secondary_tillage_management_date
ELSE NULL
END,''),
(
SELECT pms.operation_date::text
FROM plot_management_schedule pms
JOIN management_operations mo ON pms.operation_id = mo.id
WHERE pms.plot_id = CAST($id AS INTEGER)
AND mo.operation_type = t.tillage_type
ORDER BY pms.operation_date DESC
OFFSET (t.id - 1) LIMIT 1
)
)
/* --- Tillage Depth defaults (now for BOTH types) --- */
WHEN k.kind = 'depth' AND t.tillage_type = 'Primary' AND t.id = 1 THEN
COALESCE(
(NULLIF(:plough_depth_cm,'')::real)::text,
(
SELECT COALESCE(pms.plough_depth_cm, 0)::text
FROM plot_management_schedule pms
JOIN management_operations mo ON pms.operation_id = mo.id
WHERE pms.plot_id = CAST($id AS INTEGER)
AND mo.operation_type = t.tillage_type
ORDER BY pms.operation_date DESC
LIMIT 1
),
'0'
)
WHEN k.kind = 'depth' AND t.tillage_type = 'Primary' THEN
COALESCE(
(
SELECT COALESCE(pms.plough_depth_cm, 0)::text
FROM plot_management_schedule pms
JOIN management_operations mo ON pms.operation_id = mo.id
WHERE pms.plot_id = CAST($id AS INTEGER)
AND mo.operation_type = t.tillage_type
ORDER BY pms.operation_date DESC
OFFSET (t.id - 1) LIMIT 1
),
'0'
)
ELSE NULL
END AS value,
CASE WHEN k.kind = 'select' THEN
(
SELECT json_agg(
json_build_object('label', mo.operation_name, 'value', mo.id::text)
ORDER BY mo.operation_name
)
FROM management_operations mo
WHERE mo.operation_type = t.tillage_type -- applies to 'Primary' and 'Secondary'
)
ELSE NULL::json END AS options
FROM tillage_ids t
CROSS JOIN kinds k
WHERE NOT (k.kind = 'depth' AND t.tillage_type = 'Secondary')
ORDER BY
CASE t.tillage_type WHEN 'Primary' THEN 0 ELSE 1 END,
t.id,
k.ord;
|
Beta Was this translation helpful? Give feedback.
-
|
This is not a minimal reproduction that I can easily run on my side. But a quick look seems to indicate you populate the form field with the url parameter |
Beta Was this translation helpful? Give feedback.
-
|
@lovasoa The code got a bit long, sorry for that. I set $site_loc_name parameter to try to force the field to return the value stored on the "Site Name". When I debug, I can see that the I had a last try and I loaded my code online. I hope the link is not dynamic: https://1c3789b0.ca.datapage.app/sample.sql?v=1756456982360 Ps.: I just noticed that the link will not work after 5 min. I will find another way to make my example available online. So far, thank you very much for your help. |
Beta Was this translation helpful? Give feedback.
-
|
And if I may give a suggestion: you probably should split your form into multiple steps, on different screens. No more than a handful of fields should be displayed at the same time. |
Beta Was this translation helpful? Give feedback.
-
|
Hi @EdbertoLima ! Ok, I had a look at your code. select 'form' as component;
select
'select' as type,
true as create_new,
true as dropdown,
'2' as value,
'[{"label": "A", "value": 1}, {"label": "B", "value": 2}]' as options;The problem is that the value in The best solution in your case is probably to pass option values as strings too: SELECT
'site_name' AS name,
'Site Name' AS label,
'select' AS type,
TRUE as create_new,
TRUE AS dropdown,
TRUE AS required,
'Type the Site Name' as empty_option,
3 AS width,
NOT ($action IN ('Edit', 'INSERT')) AS disabled,
$site_loc_name AS value,
(SELECT json_agg(json_build_object('label', loc_name, 'value', id::text) ORDER BY loc_name) FROM temp_site) AS options;In the future, I encourage you to try and create a minimal reproduction of your issues ! Not only does it make easier for others to help you, but it often makes the problem so obvious that you can solve it yourself ! |
Beta Was this translation helpful? Give feedback.
-
|
I updated the form component to avoid this footgun. Starting with v0.37, the SQLPage form component will consider numbers and their string representation as equal when comparing the |
Beta Was this translation helpful? Give feedback.
Uh oh!
There was an error while loading. Please reload this page.
Uh oh!
There was an error while loading. Please reload this page.
-
Hi all,
I am developing a system for entering scientific experiments using SQL pages, and I have been struggling to find a solution to one issue I am facing. I have a multi-step form where the user can select the number of operations and click next, and the form displays as many fields as necessary. The problem is that when we press next, the element of one of my fields, which is a select type and create_new is TRUE, gets set to null, and I have to re-type to continue to the next page.
If someone has an opinion or could help me find a solution, I would appreciate it.
register_field.sql
form_site.sql
insert the var
After press next
Beta Was this translation helpful? Give feedback.
All reactions