Edit form and select type

[jhautefeuille]

Hello,

I can try to edit data with a form :

status is TEXT sqlite type
quotient is REAL sqlite type

SELECT 
    'Statut'                     as name,
    'select'                     as type,
    'Choisir un statut...'       as empty_option,
    '[{"label": "Présentiel", "value": "Présentiel"}, {"label": "Télétravail", "value": "Télétravail"}, {"label": "Congé", "value": "Congé"}, {"label": "Maladie", "value": "Maladie"}]' as options,
    TRUE                         as required,
    (SELECT status FROM workflow WHERE id=CAST($id AS INT)) AS value;

SELECT 
    'Quotité'                    as name,
    'select'                     as type,
    'Choisir une quotité...'     as empty_option,
    '[{"label": "Demi-journée", "value": "0.5"}, {"label": "Une journée", "value": "1.0"}]' as options,
    TRUE                         as required,
    (SELECT quotient FROM workflow WHERE id=CAST($id AS INT)) AS value;

The second select type doesn't print "Demi-journée" or "Une journée" according to select value.

I have to forget something.

Thank you for advices.

[lovasoa]

The issue is how the form options were written.

You had this:

[{"label": "Demi-journée", "value": "0.5"}]

That "0.5" is a string, not a number. But the database gives back a real number like 0.5, not a string.

So the form couldn’t match them. It was comparing number 0.5 to string "0.5" and those aren’t the same.

The fix:

Write numbers as numbers, not strings:

[{"label": "Demi-journée", "value": 0.5}]

No quotes around the number. Just like you wouldn’t write "1.0" when you mean the number 1.0.

Now the form sees:

• 0.5 from the DB
• 0.5 in the options

→ They match. Problem solved.

---

SELECT 
    'Quotité'                    as name,
    'select'                     as type,
    'Choisir une quotité...'     as empty_option,
    '[{"label": "Demi-journée", "value": 0.5}, {"label": "Une journée", "value": 1.0}]' as options,
    TRUE                         as required,
    (SELECT quotient FROM workflow WHERE id=CAST($id AS INT)) AS value;

[jhautefeuille]

Thank you, indeed, I hadn't noticed my mistake. 👍
I tried casting the data type, but without success.

[lovasoa]

To avoid having to manually maintain the json arrays, what I would recommend is keeping a table with the option lists, and generating the json dynamically, at least for the status field.

CREATE TABLE quota (
    label TEXT PRIMARY KEY,
    value REAL
);
CREATE TABLE status (
    label TEXT PRIMARY KEY
);
CREATE TABLE workflow (
    id INTEGER PRIMARY KEY,
    status TEXT REFERENCES status(label) ON DELETE CASCADE,
    quota TEXT REFERENCES quota(label) ON DELETE CASCADE
);

and then use sqlite's json functions to generate the options

SELECT 'form' AS component;

SELECT 
    'Statut' AS name,
    'select' AS type,
    'Choisir un statut...' AS empty_option,
    (SELECT json_group_array(json_object('label', label, 'value', label)) FROM status) AS options,
    TRUE AS required,
    status AS value
FROM workflow
WHERE id = CAST($id AS INT);

SELECT 
    'Quotité' AS name,
    'select' AS type,
    'Choisir une quotité...' AS empty_option,
    (SELECT json_group_array(json_object('label', label, 'value', label)) FROM quota) AS options,
    TRUE AS required,
    quota AS value
FROM workflow
WHERE id = CAST($id AS INT);

Screencast.From.2025-08-05.12-48-38.mp4

[jhautefeuille]

Thank you, that is indeed a more dynamic solution. Thank you for the tip. I am gradually discovering the possibilities of sqlpage, but it has been years since I last used sql. I need to refresh my memory.

Thank you for your very informative answers, which have helped me understand how to use sqlpage.