Reference fields in grid

[Wouter L.]

Py4web version: 1.20250908.3

In 'edit' and 'new' mode, the referenced fields are still displayed as IDs. The expected behaviour is a dropdown box with names for the referenced table fields.

'View' mode works correctly.

 Regards

[laundmo]

Hi,

You can use validators like IS_IN_DB or IS_IN_SET to make the form show a dropdown. You can either set Field(... requires=IS_IN_DB(...)) in your db model definition globally, or set it during a request like this:
db.mytable.myfield.requires = IS_IN_SET([1, 2, 3], ['first', 'second', 'third')
ofc both work with either validator. To make the select dropdown accept empty/null/none values, you can surround your validator in IS_EMPTY_OR() like this: 
IS_EMPTY_OR(IS_IN_DB(query, zero="Null option string"), null="")
zero="label" is what the null field will show, and null=None sets how null is stored in the DB. null=None is the default, meaning it will actually store NULL in the DB. You could set this, for example, to an empty string like i've done above or anything else.

[Wouter L.]

Hi,

Your suggestion is perfect for creating a selection/query or manually designing a form. However, my point is that the value displayed in 'Edit' and 'New' should always be the field value from the referenced table, not the ID-field.

This was the behavior before the grid was refactored, and it is also the default behavior of SQLFORM.grid in web2py.

Op donderdag 11 september 2025 om 13:06:32 UTC+2 schreef laundmo:

[Massimo DiPierro]

The problem is that when mapping an id to a string we use the table._format function (or string) and this is a one way function. We cannot reverse map the string into the ID. The only way is to make a dropdown.

We could give reference fields a default IS_EMPTY_OR(IS_IN_DB(....)) validator so we always get the dropdown. Problem is the performance on existing apps with large referenced tables.

I am going to think one more day about this option.

Massimo

[Massimo DiPierro]

Turns out this is a big can of worms. It can be done but at the expense of a great complexity in the code that will bit us later.

the problem is this: in 

db.define_table(... Field(....)...))

Field is evaluated before define_table. At that time the Field object has no knowledge of db. The IS_IN_DB validator needs a db. So we would need to defer setting that default until define_table is called. Notice it may never be called because you can have a Form([Field(...)] and not you get a deferred default validator that is never set resulting in inconsistent behavior.

I think I am going to leave this one alone. But I will keep thinking in case a better idea comes to mind.

Bottom line is that for now you have to explicitly set requires=IS_EMPTY_OR(IS_IN_DB(db, "tablename", db.tablename._format))

[laundmo]

Hi Massimo,

Wouldn't it be possible to set the IS_IN_DB validator when referenced fields are resolved, in Table._create_references ?
Since thats the exactly location where referenced fields are resolved, the main missing step is a way to check if the requires of the field is default or was overwritten by the user. 

[Massimo DiPierro]

Yes. It would be possible. It is just that would add a bit of complexity

--
You received this message because you are subscribed to the Google Groups "py4web" group.
To unsubscribe from this group and stop receiving emails from it, send an email to py4web+un...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/py4web/ecf5b93a-81a7-4b90-94c2-5e389354a8fbn%40googlegroups.com.

[Massimo DiPierro]

This is now done as you suggest

[laundmo]

The change is flawed: it does not properly *detect* whether a default validator is needed. It only attempts to track it with a boolean.
This fails with the following script, resetting custom validators

from pydal import DAL

from pydal.objects import Table

from pydal.validators import IS_IN_SET

from py4web import Field

db = DAL("sqlite:memory")

field = Field("person")

field.requires = IS_IN_SET(["a", "b"])

db.define_table("test", field)

print(db.test.person.requires)

The issue is this:
1. i create a field without a requires, _has_default_validators is set to True
2. i assign a validator to field.requires, _has_default_validators is NOT changed to False, stays True even though I have set a custom validator
3. Creating a Table with this field calls field.bind which, if _has_default_validators is True, overwrites the validator i've set 

I recommend not using a boolean at all, but instead having some sort of dummy value which users won't ever want to set, but allows you to check if a validator has been set. 

[Massimo DiPierro]

Ouch you are right. I will fix today

To view this discussion visit https://groups.google.com/d/msgid/py4web/97ed7abb-ac5f-4d87-b891-d406598d1509n%40googlegroups.com.

[Massimo DiPierro]

posted a fix. Please double check. :-)

[Jorge Salvat]

Hi, since release  1.20250922.1 

ERROR:root:Traceback (most recent call last):
  File "c:\wwwroot\py4web-env\venv\Lib\site-packages\py4web\core.py", line 1059, in wrapper
    ret = func(*func_args, **func_kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\wwwroot\py4web-env\venv\Lib\site-packages\py4web\core.py", line 1044, in wrapper
    raise exception
  File "c:\wwwroot\py4web-env\venv\Lib\site-packages\py4web\core.py", line 1020, in wrapper
    context["output"] = func(*args, **kwargs)
                        ^^^^^^^^^^^^^^^^^^^^^
  File "C:\wwwroot\py4web-env\apps\ainmo\controllers.py", line 821, in revisiones
    amount_column('revisiones', 'renta_mensual'),
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\wwwroot\py4web-env\apps\ainmo\controllers.py", line 78, in amount_column
    column = Column(tit,
             ^^^^^^^^^^^
TypeError: Column.__init__() got an unexpected keyword argument 'represent'

INFO:Rocket.Requests:127.0.0.1 - "GET /ainmo/revisiones HTTP/1.1" - 500 541

In controllers.py

def amount_column(tab, col):

    tit = col.replace("_", " ").title()

    column = Column(tit,

                    represent=lambda r: AMOUNT_REPRESENT(r[tab][col]) if tab in r else AMOUNT_REPRESENT(r[col]),

                    required_fields=[db[tab][col]],

                    orderby=db[tab][col],

                    td_class_style='grid-cell-type-decimal')

    return column

@action("revisiones")

@action.uses("parent.html", auth.user, T)

def revisiones():

    path = Grid.parse(request.query)["mode"]

    search_queries = [

        GridSearchQuery(

            "x Alquiler",

            lambda val: db.revisiones.alquiler == val,

            requires=IS_NULL_OR(

                IS_IN_DB(

                    db(

                        db.alquileres.id.belongs(

                            db(db.revisiones.id > 0)._select(

                                db.revisiones.alquiler, distinct=True

                            )

                        )

                    ),

                    "alquileres.id",

                    zero="x Alquiler (todos)",

                )

            ),

        ),

    ]

    queries = [(db.revisiones.id > 0)]

    search = GridSearch(search_queries, queries)

    fields = [

        db.revisiones.alquiler,

        db.revisiones.fecha_inicio,

        #db.revisiones.renta_mensual,

        amount_column('revisiones', 'renta_mensual'),

    ]

    orderby = [~db.revisiones.fecha_inicio, db.revisiones.alquiler]

    left = [db.alquileres.on(db.revisiones.alquiler == db.alquileres.id)]

    deletable=False

    if path == "edit":

        deletable=True

    gd = copy.deepcopy(GRID_DEFAULTS)

    grid = Grid(

        search.query,

        fields=fields,

        left=left,

        orderby=orderby,

        create=False, deletable=deletable,

        search_form=search.search_form,

        T=T, **gd, # type: ignore

    )

    attrs = {

        "_onclick": "location.href='%s'; return false;" % URL('revisiones'),

        "_class": "button is-default",

    }

    grid = set_formatts_buttons(grid, attrs)

    grid.process()

    return dict(grid=grid)

In models.py

DATE_REPRESENT = (

    lambda x: x.strftime("%d.%m.%Y")

    if isinstance(x, date)

    else x[8:]+x[4:8]+x[:4]

    if x else ""

)

AMOUNT_REPRESENT = (

    lambda x: locale.format_string("%.2f", float(x.replace(',','.')), grouping=True)

    if isinstance(x, str)

    else locale.format_string("%.2f", x, grouping=True)

    if x else ""

)

db.define_table(

    "revisiones",

    Field("alquiler", "reference alquileres",

          requires=IS_IN_DB(db, 'alquileres.id', lambda r: db.inmuebles[r.inmueble].apodo + '_' + r.identif,

                            error_message='Seleccionar'),

          represent=lambda v, _: db.inmuebles[db.alquileres[v].inmueble].apodo + '_' +

                              db.alquileres[v].identif),

    Field("fecha_inicio", "date", requires=IS_DATE(error_message='Entrar fecha'),

          represent=lambda x, _: DATE_REPRESENT(x)),

    Field("renta_mensual", "decimal(12,2)",

          requires=IS_DECIMAL_IN_RANGE(1, None, dot=',', error_message='Indicar importe'),

          represent=lambda x, _: AMOUNT_REPRESENT(x)),

    Field("notas", "text"),

    format="%(alquiler)s",

)

db.revisiones.id.readable = db.revisiones.id.writable = False

Maybe there's a fix pending, or do we need to modify our code ?

Py4web Documentation is outdated

[Jorge Salvat]

Hi, after release 1.20250922.1

ERROR:root:Traceback (most recent call last):
  File "c:\wwwroot\py4web-env\venv\Lib\site-packages\py4web\core.py", line 1059, in wrapper
    ret = func(*func_args, **func_kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "c:\wwwroot\py4web-env\venv\Lib\site-packages\py4web\core.py", line 1044, in wrapper
    raise exception
  File "c:\wwwroot\py4web-env\venv\Lib\site-packages\py4web\core.py", line 1020, in wrapper
    context["output"] = func(*args, **kwargs)
                        ^^^^^^^^^^^^^^^^^^^^^
  File "C:\wwwroot\py4web-env\apps\ainmo\controllers.py", line 821, in revisiones
    amount_column('revisiones', 'renta_mensual'),
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\wwwroot\py4web-env\apps\ainmo\controllers.py", line 78, in amount_column
    column = Column(tit,
             ^^^^^^^^^^^
TypeError: Column.__init__() got an unexpected keyword argument 'represent'

INFO:Rocket.Requests:127.0.0.1 - "GET /ainmo/revisiones HTTP/1.1" - 500 541

in controllers.py

def amount_column(tab, col):

        amount_column('revisiones', 'renta_mensual'),

    ]

    orderby = [~db.revisiones.fecha_inicio, db.revisiones.alquiler]

    left = [db.alquileres.on(db.revisiones.alquiler == db.alquileres.id)]

    deletable=False

    if path == "edit":

        deletable=True

    gd = copy.deepcopy(GRID_DEFAULTS)

    grid = Grid(

        search.query,

        fields=fields,

        left=left,

        orderby=orderby,

        create=False, deletable=deletable,

        search_form=search.search_form,

        T=T, **gd, # type: ignore

    )

    attrs = {

        "_onclick": "location.href='%s'; return false;" % URL('revisiones'),

        "_class": "button is-default",

    }

    grid = set_formatts_buttons(grid, attrs)

    grid.process()

    return dict(grid=grid)

in models.py

Is there a fix in project, or do we need to modify our code?

py4web documentation does not help any more ...

[Massimo DiPierro]

This is now fixed in Master and I will release a new version with the fix.