Frappe and ERPNext: Leveraging ERP Capabilities for Business Solutions (Part II)

In the previous article (here) touched on the overview of Frappe and ERPNext and how they’re connected. We also learnt that ERPNext can be leveraged to solve challenges in business with a suite of tools at our disposal.

We’ll continue with using Frappe and ERPNext to carry out more functions for business solutions like customisation, data management in ERPNext, etc.

Dashboards in ERPNext offer a powerful solution for visualising data and tracking key performance indicators (KPIs) in real-time. By customising these dashboards, businesses can tailor the display to meet their specific needs, ensuring that important metrics are easily accessible and relevant. Whether you’re monitoring sales performance, project progress, or inventory levels, a well-designed dashboard can provide valuable insights with just a glance.

In addition to its dashboard functionality, ERPNext excels in data management. Effective data organisation is the backbone of any ERP system, and ERPNext provides a comprehensive suite of tools to manage customer records, inventory data, financial transactions, and employee details efficiently. With ERPNext, all data is captured, structured, and readily accessible, enabling organisations to make informed decisions based on real-time information.

Using Frappe Framework for Customization

Here’s a detailed guide on how to use the Frappe framework for customization.

Setting Up Development Environment

These are the prerequisites you will need to set up your development environment. Versions are current as of this publication, and are subject to change.

Python 3.6+
Node.js 14+
Redis 5 (caching and real time updates)
MariaDB 10.3.x / Postgres 9.5.x (to run database driven apps)
yarn 1.12+ (js dependency manager)
pip 20+ (py dependency manager)
wkhtmltopdf (version 0.12.5 with patched qt) (for pdf generation)
Cron (bench’s scheduled jobs: automated certificate renewal, scheduled backups)
NGINX (proxying multitenant sites in production)

Installation

Here I’m using Linux (ubuntu 22.04) to install frappe.

Install Bench:

Initialise a Bench Directory:

To initialise a frappe bench you’ll need to use the following command

Syntax (note that the version name parameter is optional, but if you use that parameter, a version name value is required):

bench init <bench_name> [version_name(optional)]

So to set this up, you will use:

bench init my-bench

Create a New Site

1 2	cd my-bench bench new-site my-site.local

Run

1	Bench start

Then go to https://127.0.0.1:8000/#login, and you should see the Frappe login screen:

If you get a 404 error such as this below

Use the command below to set the newly created site to default (of course this assumes the site has been properly configured. If this does not remedy the issue, you will need to look at the other settings and configuration that you have used.)

bench use my_site.local

Get ERPNext:

bench get-app erpnext

Install ERPNext on Your Site:

bench install-app erpnext

Start the Bench:

bench start

Creating a New App

Create a New App:

1	bench new-app my_custom_app

Install the App on Your Site

1	bench --site my-site.local install-app my_custom_app

Add Your App to Bench

1	bench --site my-site.local set-config apps ["erpnext", "my_custom_app"]

Customizing DocTypes

DocTypes in ERPNext are the fundamental building blocks that define the structure and behaviour of the system’s various forms, reports, and data tables. Customizing DocTypes allows you to attune the data model to fit your business processes better. This can involve modifying existing DocTypes or creating new ones to capture unique data specific to your operations.

Customizing DocTypes is essential when the default fields and behaviours provided by ERPNext do not fully meet your business requirements. By customising DocTypes, you can:

Add fields specific to your business operations that are unavailable in the standard DocTypes.
Implement custom validations, default values, and triggers that align with your operational workflows.
Customise the layout and structure of your forms to ensure that data is organised and displayed in a way that makes sense for your users.

Creating a Custom Doctype

In this section I will cover creating a custom Doctype.

Create a New Doctype

To create a new Doctype simply go to the build section

Click on the Doctype to navigate to the Doctype section

Click on the add Doctype button to create your custom Doctype

Define Fields in the DocType

Open the generated:

1	my_custom_app/my_custom_app/doctype/my_custom_doctype/my_custom_doctype.json file.

Add fields as needed.

{

"fields": [

{

"fieldname": "field1",

"fieldtype": "Data",

"label": "Field 1"

{

"fieldname": "field2",

"fieldtype": "Int",

"label": "Field 2"

}

]

}

Migrate Changes

1	bench --site my-site.local migrate

Creating Custom Scripts

Client-Side Scripts

Client-side scripts allow you to enhance the UI and add custom behaviours.

Add a Custom Script

Go to Custom Script in the ERPNext UI.

Create a new script for the desired DocType.

frappe.ui.form.on('My Custom DocType', {

refresh: function(frm) {

// Custom behavior

frm.add_custom_button(__('Click Me'), function() {

frappe.msgprint(__('Button Clicked'));

});

}

});

Server-Side Scripts

Server-side scripts allow you to extend backend functionality using Python.

Add Server Script

Create a new Python file in your app, e.g.,

my_custom_app/my_custom_app/doctype/my_custom_doctype/my_custom_doctype.py.

import frappe

def before_save(doc, method):

# Custom logic before saving the document

doc.custom_field = "Value set in before_save"

Hook the Script to the DocType

Add the hook in my_custom_app/hooks.py.

doc_events = {

"My Custom DocType": {

"before_save": "my_custom_app.my_custom_app.doctype.my_custom_doctype.my_custom_doctype.before_save"

}

Managing Data within ERPNext

ERPNext provides robust tools for managing data, generating reports, and performing analytics to help businesses make informed decisions. Here’s a detailed guide on managing data and generating reports and analytics using ERPNext.

Effective data management is crucial for any ERP system, and ERPNext provides a comprehensive suite of tools to help organisations manage their data efficiently. Whether you’re dealing with customer records, inventory data, financial transactions, or employee details, ERPNext ensures that all data is captured, organised, and accessible in a structured way.

Importing Data

ERPNext allows you to import data from various sources, such as CSV files, directly into the system.

Prepare Data for Import

Create a CSV file with the data to be imported. Ensure that the columns match the fields in the target DocType.

Use the Data Import Tool

Go to Data Import in the ERPNext UI.

Select the DocType you want to import data into.

Upload the CSV file

Map the CSV columns to the DocType fields.

Validate the data to check for errors.

Import the data.

Managing Records

ERPNext provides comprehensive forms and views to manage records for each DocType.

List View

The list view displays all records for a DocType. You can filter, search, and sort records.

Form View

The form view allows you to create, read, update, and delete individual records. Each form is tailored to the specific DocType.

Bulk Operations

Perform bulk operations such as mass updates, deletions, and email notifications directly from the list view.

Generating Reports and Analytics

ERPNext offers built-in tools for generating various types of reports and performing analytics.

Standard Reports

ERPNext comes with a set of standard reports for common business needs, such as sales, purchases, inventory, and financials.

Access Standard Reports

Go to the module (e.g., Sales, Accounting) and select Reports.

Choose from the list of available reports (e.g., Sales Order Summary, General Ledger).

Customise Standard Reports

Use filters to customise the report view (e.g., date range, customer, status).

Export reports to CSV or Excel for further analysis.

Custom Reports

Create custom reports to meet specific business requirements.

Create a Custom Report:
Go to Custom Reports.
Click New Report.
Choose the DocType and the type of report (e.g., Query Report, Script Report).

Define Report Parameters

Select fields to include in the report.
Apply filters and conditions.

Write SQL Query or Script

For Query Reports, write SQL queries to fetch data.
For Script Reports, write Python scripts to generate data.

Example SQL Query for a Report

SELECT name, customer, grand_total

FROM `tabSales Order`

WHERE transaction_date BETWEEN %(from_date)s AND %(to_date)s

Example Python Script Report:

Then you use the query in code like this:

import frappe

def execute(filters=None):

columns = [

{"fieldname": "name", "label": "Name", "fieldtype": "Data", "width": 100},

{"fieldname": "customer", "label": "Customer", "fieldtype": "Data", "width": 100},

{"fieldname": "grand_total", "label": "Grand Total", "fieldtype": "Currency", "width": 100}

]

data = frappe.db.sql("""

SELECT name, customer, grand_total

FROM `tabSales Order`

WHERE transaction_date BETWEEN %(from_date)s AND %(to_date)s

""", filters)

return columns, data

Dashboards

ERPNext provides dashboards for a visual representation of key metrics and KPIs.

Access Dashboards

Location in ERPNext UI: Dashboards can be accessed from the main menu in the ERPNext user interface. Look for the Dashboard option under the relevant module or section (e.g., Sales, Accounts, or Projects).

Dashboard Types: ERPNext provides both default dashboards for various modules (like Sales, Accounting, or Human Resources) and the ability to create custom dashboards tailored to your specific needs.

Exploring Default Dashboards

Once you open a dashboard, you’ll see a collection of pre-configured charts, graphs, and data summaries. These dashboards are designed to give you an immediate overview of the most critical data for that module, such as sales performance, financial health, or project status.

Customise Dashboards

Thes are the steps to customize your dashboards.

1. Adding New Components:

You can add various visualisations to your dashboard, such as bar charts, line graphs, and pie charts. To do this, select “Dashboard Settings” and “Add Chart.” You’ll need to choose a data source (such as a specific DocType) and define what the chart should display (e.g., sales by month or top-selling products).
Add key performance indicators (KPIs) or summary metrics to the dashboard, like total revenue, number of new customers, or outstanding invoices. These metrics provide quick, actionable insights.

2. Configuring Data Sources and Filters:

Data Sources: Each chart or metric on your dashboard pulls data from specific sources within ERPNext, such as DocTypes, reports, or custom queries. When adding a new component, you’ll specify the source, ensuring the data displayed is accurate and up-to-date.
Filters: Apply filters to narrow down the data shown on your dashboard. For example, you can filter sales data by region, product category, or period. This allows you to focus on specific areas of interest without cluttering your dashboard with irrelevant information.

Analytics

ERPNext provides different built-in analytics tools that allow you to extract valuable insights from your data. These tools are designed to help you visualise trends, monitor key performance indicators (KPIs), and make data-driven decisions without complex setups or additional software. While ERPNext’s analytics tools cover many standard use cases, more advanced analytics might require integration with external platforms like Tableau or PowerBI.

Using Built-In Analytics Tools in ERPNext

Pivot Table

Pivot tables in ERPNext are powerful tools that allow you to summarise and analyse large datasets by organising data into a matrix format. This feature is similar to pivot tables in Excel, but it’s integrated directly into the ERPNext interface, allowing you to work with your data without needing to export it.

Create pivot tables to summarise and analyse data.

Go to Pivot Table in the ERPNext UI.

Choose the DocType (which represents the data type you want to analyse, such as Sales Orders, Invoices, or Employee Records).

Select the fields you want to include in the pivot table. For instance, you might choose “Sales Region” as rows, “Product Category” as columns, and “Total Sales” as the values to aggregate.

Customise the layout to display data highlighting the necessary insights, such as sorting, filtering, and applying calculated fields.

Chart Builder

The Chart Builder in ERPNext enables you to create custom charts that visualise your data in various formats, such as bar charts, line graphs, or pie charts. These visualisations are crucial for identifying patterns, trends, and outliers in your data.

Build custom charts to visualise data.

Go to Chart Builder

Select the DocType, fields, and chart type

Choose the relevant DocType and fields to visualise. For example, you might want to create a line chart showing monthly sales revenue. (bar, line, pie).

Select the proper chart type based on the data you want to represent. Bar charts are excellent for comparing quantities, line charts for styles over time, and pie charts for proportions.

Customise the chart by adding labels, adjusting colours, and setting filters to focus on specific data segments.

Advanced Analytics

While ERPNext’s built-in tools are robust, some organisations may require more advanced analytics capabilities. Integration with tools like Tableau or PowerBI might be necessary for complex data analysis.

Why Use External Tools?

Tools like Tableau or PowerBI can handle more complex data models and offer advanced features like machine learning integrations, predictive analytics, and interactive dashboards.
These platforms can scale to analyse massive datasets across various sources, providing deeper insights and more detailed visualisations.
Advanced tools allow for more granular customization of reports and dashboards, enabling a more tailored analysis experience.

Integration with ERPNext: ERPNext can be integrated with these tools through APIs or by exporting data to be imported into the analytics platform. This allows you to sustain an efficient workflow while leveraging powerful external analytics capabilities.

Conclusion

ERPNext is a versatile platform you can leverage to solve various business challenges, from improving operational efficiency to enhancing customer relationships. By integrating all business processes into a single platform, ERPNext provides businesses the tools they need to develop, innovate, and stay competitive in today’s fast-paced market. The analytics tools in ERPNext are designed to be user-friendly yet powerful, enabling you to gain insights from your data with minimal setup. Whether you’re using pivot tables to summarise data, building custom charts for visual insights, or integrating with advanced analytics platforms, ERPNext offers the flexibility to meet your analytical needs. For organisations with more complex requirements, tools like Tableau or PowerBI can be integrated to provide even deeper data analysis and visualisation capabilities.