Getting the result automatically when only one match with Value relation in QGIS

Question

I have a project that comprises three crucial layers:

• A SpatiaLite layer named 'HABITATS_POLY'
• A layer without geometry named 'CORRESPONDANCES'
• A database named "BD_CORINE"

When a code is entered in the "EUNIS_1" field of the 'HABITATS_POLY' layer, it is automatically copied to the "EUNIS" field in the 'CORRESPONDANCES' layer.

Within the 'CORRESPONDANCES' layer, there is a field: "CHOIX_CORINE", which utilizes a value-relation widget with the 'BD_CORINE' layer. This field lists the matches between the entered EUNIS code and the corresponding "CORINE" code(s) found in the "Label_CORINE" field of the 'BD_CORINE' layer.

How can I ensure that the result of the relationship is automatically populated when only one result is possible?

For instance, if the EUNIS code is "G1.31," and there is only one matching code, say "44.61," I would like this result to be automatically selected.

Currently, I find myself manually selecting the results even when there is only one possible match. How can I streamline this process?

Answer 1

I am working with SQL triggers in this project, so I used another SQL trigger to update my field the way I want.

    CREATE TRIGGER update_eunis
AFTER INSERT ON CORRESPONDANCES
FOR EACH ROW
BEGIN
    SELECT COUNT(*) 
    FROM BD_CORINE
    WHERE "Habitat EUNIS12" = NEW.EUNIS
    LIMIT 2;

    UPDATE CORRESPONDANCES
    SET "CORINE" =
        CASE
            WHEN (SELECT COUNT(*) FROM BD_CORINE WHERE "Habitat EUNIS12" = NEW.EUNIS) = 1 THEN (SELECT "Habitat CORINE" FROM BD_CORINE WHERE "Habitat EUNIS12" = NEW.EUNIS)
        WHEN (SELECT COUNT(*) FROM BD_CORINE WHERE "Habitat EUNIS12" = NEW.EUNIS) = 0 THEN '-'            
        ELSE 'Multiple'
        END
    WHERE EUNIS = NEW.EUNIS;
END;

I get the information "Multiple" when multiple result are found (>2). I have a second field with a value relation widget that populates a list of correspondences.

When a change is made in this field (CHOIX_CORINE"), the field "CORINE" is automatically updated with this trigger:

CREATE TRIGGER update_chx_corine
BEFORE UPDATE ON CORRESPONDANCES
FOR EACH ROW
WHEN NEW."CHOIX_CORINE" IS NOT NULL
BEGIN
    UPDATE CORRESPONDANCES
    SET "CORINE" = 
        CASE
            WHEN instr(replace(replace(NEW."CHOIX_CORINE", ' --', ' '), '''', ''), ' ') > 0
            THEN substr(replace(replace(NEW."CHOIX_CORINE", ' --', ' '), '''', ''), 1, instr(replace(replace(NEW."CHOIX_CORINE", ' --', ' '), '''', ''), ' ') - 1)
            ELSE NEW."CORINE"
        END
    WHERE ROWID = NEW.ROWID;
END;

Everything is working well and fast (for now).

The only issue is that I have the same database used in QGIS (for value-relation widget) and in DB Manager (as database for my trigger).