# ModelView in form **URL:** \ **Category:** Developer\ **Created:** [October 18, 2019, 11:46am UTC](https://discuss.tryton.org/t/modelview-in-form/1857 "2019-10-18T11:46:35Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![edbo](https://discuss-cdn.tryton.org/letter_avatar_proxy/v4/letter/e/f14d63/32.png) [@edbo](https://discuss.tryton.org/u/edbo)\ **Post date:** [October 18, 2019, 11:46am UTC](https://discuss.tryton.org/t/modelview-in-form/1857/1 "2019-10-18T11:46:35Z")
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: ```auto 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. ```auto 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` ```auto 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? ---
**Author:** ![pokoli](https://discuss-cdn.tryton.org/user_avatar/discuss.tryton.org/pokoli/32/22_2.png) [@pokoli](https://discuss.tryton.org/u/pokoli)\ **Post date:** [October 18, 2019, 12:16pm UTC](https://discuss.tryton.org/t/modelview-in-form/1857/2 "2019-10-18T12:16:52Z")
> [@edbo](#): > > What am I missing here? 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](http://docs.tryton.org/projects/server/en/latest/ref/models/models.html#trytond.model.ModelSQL.table_query) functionality. Are you aware of it? ---
**Author:** ![edbo](https://discuss-cdn.tryton.org/letter_avatar_proxy/v4/letter/e/f14d63/32.png) [@edbo](https://discuss.tryton.org/u/edbo)\ **Post date:** [October 18, 2019, 12:51pm UTC](https://discuss.tryton.org/t/modelview-in-form/1857/3 "2019-10-18T12:51:06Z")
> [@pokoli](#): > > Clients call read after a search (as search only return ids for them). So you are missing to override also the read method. 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()`, ```auto 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? > [@pokoli](#): > > BTW, you should probably use the [table\_query](http://docs.tryton.org/projects/server/en/latest/ref/models/models.html#trytond.model.ModelSQL.table_query) functionality. Are you aware of it? 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. ---
**Author:** ![ced](https://discuss-cdn.tryton.org/user_avatar/discuss.tryton.org/ced/32/1237_2.png) [@ced](https://discuss.tryton.org/u/ced)\ **Post date:** [October 18, 2019, 1:03pm UTC](https://discuss.tryton.org/t/modelview-in-form/1857/4 "2019-10-18T13:03:08Z")
> [@edbo](#): > > 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? To display a model in the client (excepted for wizard), you must inherit at least of `ModelStorage`. > [@edbo](#): > > 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. A `ModelSQL` with a `table_query` does not create a table in the database. ---
**Author:** ![edbo](https://discuss-cdn.tryton.org/letter_avatar_proxy/v4/letter/e/f14d63/32.png) [@edbo](https://discuss.tryton.org/u/edbo)\ **Post date:** [October 19, 2019, 11:59am UTC](https://discuss.tryton.org/t/modelview-in-form/1857/5 "2019-10-19T11:59:04Z")
> [@ced](#): > > A `ModelSQL` with a `table_query` does not create a table in the database. 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 `` of 5. The query will look something like ```auto query = select(*columns) query.where = (resource.resource == 'project.work.workinstruction,') ``` Where do I get the `` from? I want to search in another database table for that particular work instruction. ---
**Author:** ![pokoli](https://discuss-cdn.tryton.org/user_avatar/discuss.tryton.org/pokoli/32/22_2.png) [@pokoli](https://discuss.tryton.org/u/pokoli)\ **Post date:** [October 19, 2019, 1:39pm UTC](https://discuss.tryton.org/t/modelview-in-form/1857/6 "2019-10-19T13:39:51Z")
> [@edbo](#): > > 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 `` of 5. The query will look something like > > ```auto > query = select(*columns) > query.where = (resource.resource == 'project.work.workinstruction,') > > ``` > > Where do I get the `` from? I want to search in another database table for that particular work instruction. 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](https://hg.tryton.org/trytond/file/bfc90c50b591/trytond/model/fields/reference.py#l184) as an example of this king of clauses ---
**Author:** ![edbo](https://discuss-cdn.tryton.org/letter_avatar_proxy/v4/letter/e/f14d63/32.png) [@edbo](https://discuss.tryton.org/u/edbo)\ **Post date:** [October 19, 2019, 8:22pm UTC](https://discuss.tryton.org/t/modelview-in-form/1857/7 "2019-10-19T20:22:02Z")
I think I wasn’t clear enough because I’m also a bit confused 😢 But I have ```auto 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? ---
**Author:** ![ced](https://discuss-cdn.tryton.org/user_avatar/discuss.tryton.org/ced/32/1237_2.png) [@ced](https://discuss.tryton.org/u/ced)\ **Post date:** [October 19, 2019, 9:52pm UTC](https://discuss.tryton.org/t/modelview-in-form/1857/8 "2019-10-19T21:52:57Z")
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. ---
**Author:** ![edbo](https://discuss-cdn.tryton.org/letter_avatar_proxy/v4/letter/e/f14d63/32.png) [@edbo](https://discuss.tryton.org/u/edbo)\ **Post date:** [October 22, 2019, 10:02am UTC](https://discuss.tryton.org/t/modelview-in-form/1857/9 "2019-10-22T10:02:38Z")
I’m thinking way to complex, this is new for me. > [@ced](#): > > You must create a SQL query which has `workinstruction` field filled with the proper id. The `One2Many` will filter them on `workinstruction` field. But I have now the problem of casting because the `id` of the work instruction should be gotten from a `Reference` field. ```auto 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: ```auto AS "a" WHERE (("a"."workinstruction" ILIKE ("project.work.workinstruction,5"))) O... ``` No idea how to get that working ☹. So if you can give me a direction or some examples would be nice. ---
**Author:** ![ced](https://discuss-cdn.tryton.org/user_avatar/discuss.tryton.org/ced/32/1237_2.png) [@ced](https://discuss.tryton.org/u/ced)\ **Post date:** [October 22, 2019, 10:15am UTC](https://discuss.tryton.org/t/modelview-in-form/1857/10 "2019-10-22T10:15:42Z")
If you fill `workinstruction` with reference values, you must define it as a `Reference` field and not a `Many2One`. ---
**Author:** ![edbo](https://discuss-cdn.tryton.org/letter_avatar_proxy/v4/letter/e/f14d63/32.png) [@edbo](https://discuss.tryton.org/u/edbo)\ **Post date:** [October 22, 2019, 3:48pm UTC](https://discuss.tryton.org/t/modelview-in-form/1857/11 "2019-10-22T15:48:35Z")
> [@ced](#): > > If you fill `workinstruction` with reference values, you must define it as a `Reference` field and not a `Many2One` . Thanks! that did the trick, renaming the `Many2One` to a `Reference` and the tools appears in the list 😄 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` ```auto 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 ```auto [('project', 'in', ['project.work,2'])] ``` to ```auto [['OR', ('project', 'in', ['project.work,2']), ('workinstruction', 'in', ['project.work.workinstruction,4'])]] ``` ---
**Author:** ![edbo](https://discuss-cdn.tryton.org/letter_avatar_proxy/v4/letter/e/f14d63/32.png) [@edbo](https://discuss.tryton.org/u/edbo)\ **Post date:** [October 23, 2019, 11:46am UTC](https://discuss.tryton.org/t/modelview-in-form/1857/12 "2019-10-23T11:46:03Z")
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 `id`s 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! ---
**Author:** ![Unai](https://discuss-cdn.tryton.org/letter_avatar_proxy/v4/letter/u/53a042/32.png) [@Unai](https://discuss.tryton.org/u/Unai)\ **Post date:** [October 31, 2019, 5:54am UTC](https://discuss.tryton.org/t/modelview-in-form/1857/13 "2019-10-31T05:54:02Z")
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 ---
**Author:** ![system](https://discuss-cdn.tryton.org/uploads/default/original/1X/c6f8ec0a40525cdcd50058c734283450a4b3d38b.png) [@system](https://discuss.tryton.org/u/system)\ **Post date:** [November 30, 2019, 5:54am UTC](https://discuss.tryton.org/t/modelview-in-form/1857/14 "2019-11-30T05:54:11Z")
Closed on November 30, 2019, 5:54am UTC This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.