QGIS – Using NULL for Attribute Form Constraints with Mergin Maps

constraintmergin-mapsqgis

At 3.24.2, I have a Geopackage table containing two fields:

  • "interior_observed" is a Boolean, with a Checkbox widget type, true or false.
  • "egg_number" is an integer, with a Range widget type.

The widget types will be used with the Mergin Input app, as follows:

  • If field staff view the interior of a bird nesting box, they record the number of eggs (greater than or equal to zero) therein.
  • If the field staff do not view the interior, the number of eggs is unknown, and its resulting value must therefore be NULL.

Unfortunately, every constraint expression that I've tried has no effect on "egg_number". That is, any value (including NULL) can be applied to "egg_number", regardless of whether "interior_observed" is true or false – as if no constraint exists. The situation is complicated with additional widget options such as NULL enforcement and default values.

The seemingly most straightforward expression I've tried is
nullif("interior_observed" , 'false')

I've also experimented with variations on the answers at Attribute form constraint and and QGIS constraint not null depending on another field value

In summary, how to constrain "egg_number" to NULL when "interior_viewed" = 'false'?

Here's a screenshot of the constraints for "egg_number":

enter image description here

Here's a screenshot of the "interior_observed" widget settings:

enter image description here

Best Answer

It does not work the way we imagined. We falsly assumed that the return-value from the expression nullif("interior_observed", false) would be assigned to the field content. My guess now is that the return-value is only evaluated as a boolean, merely to decide if the field is valid.

To assign a field value, you need to use the Default value of the widget.

It's a bit messy but the following, finally, works for me (that is, in QGIS):

A. In the egg_number widget:

  1. Add this Constraint expression:

if("interior_observed"=false and "egg_number" is null, true, false)
OR
if("interior_observed"=true and "egg_number" is not null, true, false)

  1. Tick the Enforce constraint checkbox

  2. Add this Default value expression:

if("interior_observed"=false,NULL,NULL)

  *This looks weird, as it always returns NULL, but I think this is needed
  so the value is re-evaluated and re-applied on each update. (Just
  guessing)
  1. Tick the Set Apply default value on update checkbox

B. On the interior_observed widget:

  1. Untick the not null constraint.



Related Question