Best way to implement a many-to-many relationship in Frappe?

ERPNext Project Management
, , , ,
1

I have two doctypes: Project and Project Group.

My requirement is to establish a many-to-many relationship between them, where a Project can belong to multiple Project Groups, and a Project Group can contain multiple Projects.

In addition to storing this relationship, I also want to provide a good UI for users to manage these associations and be able to use the relationship efficiently in Report Builder, Script Reports, and dashboards.

What is the recommended way to model this kind of many-to-many relationship in Frappe? Is there any standard or best practice for implementing this while keeping both the UI and reporting in mind?

2

In Frappe, many-to-many isn’t a first-class pattern the way it is in SQL. You can model it, but it’s usually not the most “Frappe-native” design unless you genuinely need it.

Frappe works best with clear ownership: one DocType holds the relationship, usually via a child table or a Link field (many-to-one). Classic M:N (both sides independently owning the same links) tends to fight the framework and makes UI + reporting harder.

So first ask: do you really need M:N?

  • If a project has one primary group → use a single Link on Project.
  • If grouping is more like tags/labels → consider a simpler classification model.
  • Use M:N only when a project can belong to multiple groups at the same time, and groups can contain multiple projects, with no single “owner” side.

If you do need it, model it with one junction table — don’t put duplicate Link/table fields on both Project and Project Group.

Recommended pattern:

  1. Create a child table DocType, e.g. Project Group Project (istable: 1):
  • project → Link → Project
  • project_group → Link → Project Group (optional if the parent is always the group)
  1. Attach it as a Table field on Project Group (natural “container” UI: one group, many projects).
  2. If you also need to see/edit groups from Project:
  • add a read-only section on Project that lists related groups via frappe.get_all on the junction table, or
  • use a standalone junction DocType (not a child table) if both sides must be editable independently.

Important: avoid maintaining two child tables (one on Project, one on Project Group). That creates two sources of truth and sync problems. One junction table, one source of truth.

1 Like