ModelView in form

Support Developer
1

I want to display some data from different database tables to the user. Because the data is in different database tables and I want to display them together. To do that I was thinking of adding a new class which only inherits from ModelView. The base class:

class WorkInstruction(ModelView, ModelSQL, DeactivableMixin):
    "Work instructions for projects"
    __name__ = 'project.work.workinstruction'

    tools = fields.One2Many('project.work.used.tools', 'workinstruction', 'Tools')

The tools field is connected to the ModelView only class. The data can be accessed from a table which resides between them.

class MoveResources(ModelSQL, ModelView):
    'Resources linked to a move'
    __name__ = 'move.resources'

    resource = fields.Reference('Resource', selection='get_resource')
    move = fields.Many2One('stock.move', 'Move', ondelete='CASCADE',
        select=True,
        states={
            'readonly': Eval('move_state') != 'draft'
            },
        depends=['move_state'])


class WorkTools(ModelView):
    'List of tools used on a work instruction'
    __name__ = 'project.work.used.tools'

    workinstruction = fields.Many2One('project.work.workinstruction', 'WorkInstruction')
    product = fields.Many2One('product.product', 'Product')

    @classmethod
    def search(cls, clause, order):
        pool = Pool()
        Resource = pool.get('move.resources')
        res = []
        for search in clause:
            if search[0] == 'workinstruction':
                reffield = ['project.work.workinstruction,%s' % search[2][0] ]
                wi = Resource.search([('resource', 'in', reffield)])
                for i in wi:
                    res.append({
                        'workinstruction': search[2][0],
                        'product': i.move.product,
                     })
        return res

Because the ModelView doesn’t have a search function, I added one myself. I’m just collecting the data from the database and put that into an array of dictionaries. When I add the tools field to the form XML, I always get an AttributeError

Traceback (most recent call last):
  File "/lib/python3.6/site-packages/trytond/protocols/dispatcher.py", line 176, in _dispatch
    result = rpc.result(meth(*c_args, **c_kwargs))
  File "/lib/python3.6/site-packages/trytond/model/modelsql.py", line 809, in read
    getter_result = field.get(ids, cls, fname, values=result)
  File "/lib/python3.6/site-packages/trytond/model/fields/one2many.py", line 141, in get
    origin_id = getattr(target, self.field).id
AttributeError: 'dict' object has no attribute 'workinstruction'

What am I missing here?

2

Clients call read after a search (as search only return ids for them). So you are missing to override also the read method.

BTW, you should probably use the table_query functionality. Are you aware of it?

3

But the problem is in the field, not in the search. The read function isn’t even reached.
I also have tried to create the tools field as a fields.Function(),

tools = fields.Function(
    fields.One2Many('project.work.used.tools', 'workinstruction', 'Tools'),
    'get_tools' )

the tools are added, but the client then says that the work instruction has been changed, which isn’t, but the list of tools is filled. Is there a way to overcome this?

Yes I’m aware of table_query, but that function is defined on ModelSQL which I want to avoid. I don’t want an extra table in the database.

4

To display a model in the client (excepted for wizard), you must inherit at least of ModelStorage.

A ModelSQL with a table_query does not create a table in the database.

5

I didn’t know that. So I added the ModelSQL and the table_query. One thing I’m struggling with is the WHERE clause of the query.
For example I want all the tools for a work instruction with an <ID> of 5. The query will look something like

query = select(*columns)
query.where = (resource.resource == 'project.work.workinstruction,<ID>')

Where do I get the <ID> from? I want to search in another database table for that particular work instruction.

6

You can use two where clauses:

  1. For the module name: resource.resource like ‘project.work.workinstruction%’
  2. Another for the id which uses, substring and cast to convert the string part of an id to an integer type. Once you have this expression you can use any = or in operator to do your filtering.

I normaly look at the reference’s field convert_domain function as an example of this king of clauses

7

I think I wasn’t clear enough because I’m also a bit confused :cry:
But I have

tools = fields.One2Many('project.work.used.tools', 'workinstruction', 'Tools')

I assume when opening the form where the list of tools should show up, the form will call project.work.used.tools with it’s id (id of the record in the form).
In other words the form says “Hey project.work.used.tools! I need a list of tools which have work_instruction_id = 5”.
So how get I the work_instruction_id = 5 in my table_query?

8

Why do you want to restrict the table_query to a single work_instruction. You must create a SQL query which has workinstruction field filled with the proper id. The One2Many will filter them on workinstruction field.

9

I’m thinking way to complex, this is new for me.

But I have now the problem of casting because the id of the work instruction should be gotten from a Reference field.

   project.work    =>     project.work.used.tools
fields.One2Many    =>    fields.Reference
          5        =>    'project.work.workinstruction,5'

sycopg2.ProgrammingError: operator does not exist: character varying = integer
LINE 1: ..." = 'out'))) AS "a" WHERE (("a"."workinstruction" IN (5))) O...

I think it should be something like:

AS "a" WHERE (("a"."workinstruction" ILIKE ("project.work.workinstruction,5"))) O...

No idea how to get that working :frowning_face:. So if you can give me a direction or some examples would be nice.

10

If you fill workinstruction with reference values, you must define it as a Reference field and not a Many2One.

11

Thanks! that did the trick, renaming the Many2One to a Reference and the tools appears in the list :smile:

But we are not out of the woods yet. Because tools can be referenced to a project or a work instruction. And work instructions can be referenced to a project (but not needed). So when I open a project

  1. I want to see a list of tools used on that project (this already works)
  2. In that same list I also want to see the tools used on the work instructions which are associated with that project.

What I did: Both One2Many fields on project.work and project.work.workinstruction are referenced to the Reference field on project.work.used.tools. This works nicely for point 1.
I also modified the search function to add the work instructions to the domain, but I don’t get anything back. I get a KeyError

  File "/lib/python3.6/site-packages/trytond/model/modelsql.py", line 809, in read
    getter_result = field.get(ids, cls, fname, values=result)
  File "/lib/python3.6/site-packages/trytond/model/fields/one2many.py", line 142, in get
    res[origin_id].append(target.id)
KeyError: 4

I changed the domain from

[('project', 'in', ['project.work,2'])]

to

[['OR', ('project', 'in', ['project.work,2']), ('workinstruction', 'in', ['project.work.workinstruction,4'])]]
12

After stepping back for a few moments, I decided to drop everything and start again.
I now use the move.resources table directly with some Function fields to get the necessery data.

The ids for the move.resources are provided by the tools field on the work instruction or the project. The tools field is also a Function field which creates a list of resource ids.

It’s now working like I wanted to and saved me some extra code. But I learned a lot!

13

Hi:

I know you are looking for a different solution but just wanted to comment: have you considered using PowerBI or Qlick?

I’m using PowerBI together with Tryton in order to make some reports and show info from different tables as you comment.

Regards

14

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.