How to build a Custom app in Erpnext that extends Reports with a powerful Datatable

A guide to build a custom app on Frappe that extends the core platform functionality.

6 min readJan 15, 2021

This guide is aimed at Erpnext users familiar with the platform and looking to build solutions on top of it. It covers the steps involved in building a basic Custom App (modules that extend Erpnext). And hopefully, others who have not heard of Erpnext will be sufficiently piqued to explore the platform.

Erpnext is one of the top open source ERP platforms for small to medium sized businesses. It has gained popularity due to its features that allow easy customisation with very little programming knowledge. The platform can be extended using custom apps developed on the underlying Frappe platform. In fact the entire Erpnext is itself an app plugged on the Frappe framework.

To get started with Frappe/Erpnext, all that is needed is a basic knowledge of Python and javascript. The Frappe documentation covers the platform details and also shows how to create a full fledged sample application from scratch. A good primer on Frappe coding, that includes how to setup Erpnext can also be found here .

The Problem

The reports feature in Frappe makes adding custom reports a breeze, you can create reports in minutes once you have the necessary sql query. That should be all you need for the most part. However, if you do need extra features on the data grid, say conditional styling for rows, freeze columns or rows, context menu on row/cell selection, you may have to look for a more powerful grid component that has these features.

Solution

The library of choice is Tabulator, an absolute gem of a javascript data grid, that comes packed with features and excellent documentation.

Step 1: Create a site

cd into the bench directory, usually frappe-bench and run:

Create site and install erpnext from bench folder

That should give you site with the base Frappe framework. Now you can install Erpnext to the blank site. Remember Erpnext has to the first app to be installed in any site. You need not install Erpnext, if you are going to build your own application on the frappe framework and don’t need the erp features.

Once the site is installed open up the site in your browser and go though the wizard to complete your site configuration with company, Chart Of Accounts and other settings.

Step 2: Create custom app

Fill out the information on prompt and you will have a tabulator folder created under the apps folder e.g. frappe-bench/apps/tabulator

Now all the code for your app goes under the tabulator folder. You will want to create a git repo in the folder to add your code to version control.

Step 3: Create a doctype

Enable developer_mode in site_config.json for your site

Set site config to developer_mode to be able to create Doctypes

bench --site tabulator set-config developer_mode 1

Create a doctype Tabulator Report with two fields, as much of what we need we already have in the Report Doctype of Frappe.

Report Name> Link > Report

2. Report HTML > HTML

Set autoname to field:{report_name}

More fields could be added to this doctype, like height, style etc. that would allow users to be control the appearance of each report differently. The Tabulator configuration documentation has an exhaustive list of properties that could be used to setup the table.

The controller file tabulator_report.py and the tabulator_report.js files are where the much of the coding goes.

Step 4: Create a Tab Report to for ToDo report

Go ahead and create a new Tabulator Report, select ToDo report for report_name. We will create the tabulator table in report_html field in the next step, but for now it has no content in it and all you have is a blank form.

New Tab Report for ToDo report, just a blank form

Step 5: Add the Tabulator library files to app

Get the minified js and css files for Tabulator from the links the installation page. Tabulator comes with a few themes, use the css for the theme you prefer.

https://cdnjs.cloudflare.com/ajax/libs/tabulator/4.9.3/css/tabulator.min.css

https://cdnjs.cloudflare.com/ajax/libs/tabulator/4.9.3/js/tabulator.min.js

and add them under the js and css folders in the app public folder

/frappe-bench/apps/frappe_tabulator/frappe_tabulator/public

Set the files to be loaded in desk view from the app’s hooks.py

Set the js and css assets to be loaded into the desk in hooks.py

Step 6: Load Frappe query report inside the Tabulator Report

In the first pass, just load the report from the report_name field when a Tab Report doc is opened. We will build on this later to setup filters, handle change of filters etc. Paste the code below into tabulator_report.js.

Now refresh the Tab Report created in Step 4. You can see the tabulator table created with the data from ToDo report.

ToDo report loaded into the HTML field in Tab Report

Step 6: Add a column formatter to handle html, link fields

As you can see, there are a few issues to resolve. The html values are not being rendered as html and the link column values are not links. We will fix this using the Tabulator api to create a column formatter to handle known fieldtypes like Link, Date, Currency.

Note: The frappe.utils.is_html does not do a full html check, so it is replaced with https://github.com/sindresorhus/is-html in the final version of the code.

After adding formatters for Link columns and HTML values

Let us create a new Script Report, that will have a py controller and js settings file where we can configure our report filters.

Step 7: Create Script Report

Create a script report ToDo Tabulator. Be sure to select Frappe Tabulator as the Module, so the report files are created in our module

New Script Report in Frappe Tabulator module

Copy the code from frappe/desk/report/todo/todo.py to the new controller created frappe_tabulator/report/todo_tabulator/todo_tabulator.py. Add a few filters in todo_tabulator.js

Check the frappe query report to see if the report is working

Create a Tab Report for the new Todo Tabulator report.

Step 8: Load filters and custom js from report settings.

We use the get_report_settings function from query_report.js to load the report js and page.add_field function to add filter fields.

Make a small change in todo_tabulator.py to filter results

Step 9: Use an advance Tabulator feature

Remember, we are yet to use any of the advanced features of Tabulator. Explore the documentation and add the features you require to tab_report.js. Or even make an editable grid if the need arises. For now let’s add a frozen column. Make a few changes in todo_tabulator.py to set frozen property to the owner field.

Frozen column using Tabulator frozen configuration

Conclusion

We have used the existing powerful reporting structure in Frappe that handles permissions and creating filters, queries etc. and just added a wrapper around that to use Tabulator. It should now be easy to keep adding more functionality to the grid.

The original reports are still available under the usual frappe routes /desk#query-report/<report-name> while the new tabulator reports can be accessed as /desk#Form/Tabulator Report/<report-name>