Tag Archives: OpenERP

OpenERP7: Model is not showing in the model list

While creating a SQL backed report I faced a weird issue. Let me share the issue with you and how I solved it.

I added the model for the report to one of my existing modules and restarted the OpenERP server. I created views, window action and menu item to show the report. All worked fine. the report was showing perfectly. But when I went to Model list to set access rights to the model, I could not find the model there. I tried to find out the reason behind it… searched the web for any help… but no luck… it made me crazy. The report was running okay but the model was missing.

I thought to take the model out of the old module and create a new module for it. I did so, installed the new module and when I searched for the model in Model listing it was there. I reverted the changes by moving the model to my old module, uninstalled the old module and reinstalled it. Now the model is in the Model list.

I exactly don’t know what happened. I think when a module is installed OpenERP registers the model. As the old module was already installed, the model failed to register but the object was available to the window action and views. That’s why the report was working.

Hope this will help someone facing this issue.

Share

OpenERP 7: Using AND/OR in Advanced Search

In OpenERP 7, Advanced Search creates OR conditions by default. But you may also search using AND conditions. How? Let see.

For an example, you want to search contacts who have ‘John’ or ‘Jonathon’ in their names. So in Advanced Search for contacts if you add two conditions – one for ‘John’ and another one for ‘Jonathan’ as shown in the below image and click on the Apply button, then these conditions will be added as OR conditions, which is default.

Advanced Search - adding conditions as OR
Advanced Search – adding conditions as OR
Advanced Search - conditions added as OR
Advanced Search – conditions added as OR

But, if you select the first condition, click on Apply button, then select the second condition and click the Apply button again, the conditions will be added as AND conditions.

Advanced Search - add conditions as AND
Advanced Search – add conditions as AND
Advanced Search - conditions added as AND
Advanced Search – conditions added as AND

Isn’t this easy? Share if you know something like this :).

Share

OpenERP 7: Creating report using SQL query

OpenERP allows creating different reports easily using their interface. If you want to use SQL queries to work as back-end of your report and show it like other built in reports under a menu, here is how you can do.

To create a report backed by SQL query we will go through the following steps:

  1. Create a custom model,
  2. Create a Tree View,
  3. Create a Window Action and
  4. Create a menu entry.

Create a custom model

In our example we are going to create a new module My Report which will have the model we are going to use for creating the report. In the module folder create a file my_report.py. In the file we are going to define our model my_report_model.

In my_report.py:

from openerp.osv import fields,osv
from openerp import tools
 
class my_report_model(osv.osv):
    _name = "my.report.model"
    _description = "My Report"
    _auto = False
    _columns = {
        'child_name': fields.char('Analytic Account', size=128, readonly=True),
        'parent_name': fields.char('Analytic Account - Parent', size=128, readonly=True),
        'date_from': fields.date('Start Date', readonly=True),
        'date_to': fields.date('End Date', readonly=True),
        'planned_amount': fields.float('Planned Amount', readonly=True)
    }
    _order = 'parent_name asc, child_name asc'
 
    def init(self, cr):
        tools.sql.drop_view_if_exists(cr, 'my_report_model')
        cr.execute("""
            CREATE OR REPLACE VIEW my_report_model AS (
                SELECT cbl.analytic_account_id AS id,
                    aaap.name AS parent_name,
                    aaa.name AS child_name,
                    cbl.date_from,
                    cbl.date_to,
                    cbl.planned_amount
                FROM crossovered_budget_lines cbl
                INNER JOIN account_analytic_account aaa ON cbl.analytic_account_id = aaa.id
                LEFT OUTER JOIN account_analytic_account aaap ON aaa.parent_id = aaap.id
            )
        """)
 
my_report_model()

In this model we have defined the fields we are going to use in the report. In the init() method we have defined the SQL View which we are going to use for the report. The view name should match the class name defined in _name attribute (replace dot with underscore). Also the view columns should match the columns defined in _columns attribute. Save the model and install the newly created module. The my.report.model object will now be available.

Create a View

We will now create a tree view to use in the report. Follow these steps to create the tree view:

  1. Go to Settings > Technical > User Interface > Views.
  2. Click on the Create button.
  3. Enter my.report.tree.view as View Name (you may use name of your choice).
  4. Enter the name of the model (declared in the model using _name attribute) in Object field.
  5. Enter the XML for the tree view in Architecture field.
    <?xml version="1.0"?>
    <tree string="My Report" create="false">
      <field name="id" invisible="1"/>
      <field name="analytic_account_name" string="Analytic Account"/>
      <field name="budget_name"/>
      <field name="date_from"/>
      <field name="date_to"/>
      <field name="planned_amount" sum="Planned Amount"/>
    </tree>
  6. Save the View.

tree-view

Create a Window Action

Now we shall create a Window Action which will show the report. To create the Window Action follow the following steps:

  1. Go to Settings > Technical > Actions > Window Actions.
  2. Click on the Create button.
  3. Give an Action Name, in our case My Report.
  4. In Object field enter the model name, same as name we entered in the tree view.
  5. In View Mode field enter tree, form if you want to show both tree and form view. If you don’t want to allow the user to edit the entry the use only tree. In our case we have used tree.
  6. From the View Ref. dropdown select the tree view we have created earlier.
  7. Save the Window Action.

window-action

Create a menu entry

Now we have the report ready. But we need a menu item to allow the users to view the report. To create a menu item for this report/Window Action, do the following:

  1. Go to Settings > Technical > User Interface > Menu Items.
  2. Click on the Create button.
  3. Enter Name of the menu item, in our case it is My Report.
  4. We have selected Reporting/Accounting as the Parent Menu.
  5. From the Action dropdown we have selected ir.actions.act_window and then the selected the Window Action we have created earlier.
  6. Save the menu.
    menu-entry
  7. Refresh the window and the new menu item will be available under Reporting > Accounting menu.

report

So, the report is ready. Now if we want to define default fields for searching we shall have to create a search view and tie it with the Window Action. Follow these steps to create the Search View:

  1. Go to the Window Action we have created.
  2. Click on the Search View Ref. dropdown and click on Create and Edit… link. The new View form will be opened. The Search View Ref. field requires a view of type Search. Don’t worry if the new View form shows View Type as Tree. It will change on save.
  3. Give a name like my.report.search.view.
  4. Enter the class name we have created in Object field.
  5. Enter the XML in Architecture field as follows with the fields to be searched on by default.
    <?xml version="1.0"?>
    <search>
        <field name="analytic_account_name" string="Analytic Account"/>
        <field name="budget_name" string="Budget"/>
    </search>
  6. Save the view and then save the Window Action.

Now if you type anything in the search box, options will be given to search in fields you have entered in Search view.

search-option

Hope this will give you an idea on how we may add a report backed by SQL queries. If you have any other ideas/suggestions then share with us.

Share