Here are some SQL queries useful for exploring links between DocTypes in the UI.
They might help to explore the workflow logic of the enormous system which is ERPNext, for instance for learning more about it, or for checking completeness or prerequisites for an implementation.
Have fun exploring, implementing and building!
Useful SQL queries:
(Note: some have limit clauses in order to extend the returned list beyond 1000 rows.
This seems to be the default limit when opening the mariadb client with “bench mariadb”.
So, check if your own queries return exactly 1000 rows, in that case chances are 1:999 that this default limit limits your query results.)
tabDocFields:
SELECT * FROM tabDocField ORDER BY parent, idx LIMIT 99999;
Forms:
SELECT DISTINCT parent FROM tabDocField;
SELECT DISTINCT parent FROM tabDocField WHERE parent LIKE '%sales%';
SELECT DISTINCT parent FROM tabDocField WHERE parent LIKE '%account%';
All fields of all forms:
SELECT parent, label, fieldname, fieldtype, reqd, options FROM tabDocField WHERE parenttype='DocType' ORDER BY parent, idx LIMIT 99999;
Required fields of all forms:
SELECT parent, label, fieldname, fieldtype, reqd, options FROM tabDocField WHERE parenttype='DocType' AND reqd=1 ORDER BY parent, idx LIMIT 99999;
All link fields of all forms:
SELECT parent, label, fieldname, fieldtype, reqd, options FROM tabDocField WHERE parenttype='DocType' and fieldtype = 'Link' ORDER BY parent, idx LIMIT 99999;
All mandatory link fields of all forms:
SELECT parent, label, fieldname, fieldtype, reqd, options FROM tabDocField WHERE parenttype='DocType' and fieldtype = 'Link' AND reqd=1 ORDER BY parent, idx LIMIT 99999;
Modules:
SELECT DISTINCT module FROM tabDocType;
SELECT module, count(*) FROM tabDocType GROUP BY module ORDER BY module;
Linked account fields:
SELECT parent, label, fieldname, fieldtype, reqd, options FROM tabDocField WHERE parenttype='DocType' AND fieldtype = 'Link' AND options = 'Account' ORDER BY parent, idx LIMIT 99999;
Linked account fields, ordered by fieldname (to see which ones match):
SELECT parent, label, fieldname, fieldtype, reqd, options FROM tabDocField WHERE parenttype='DocType' AND fieldtype = 'Link' AND options = 'Account' ORDER BY fieldname,parent, idx LIMIT 99999;
Mandatory linked account fields:
SELECT parent, label, fieldname, fieldtype, reqd, options FROM tabDocField WHERE parenttype='DocType' AND fieldtype = 'Link' AND options = 'Account' AND reqd=1 ORDER BY parent, idx LIMIT 99999;
Tables which are linked to by some field:
SELECT options FROM tabDocField WHERE fieldtype='LINK' GROUP BY options ORDER BY options;
Tables which are linked to, with fields linking to them:
SELECT options,parent,label,fieldname FROM tabDocField WHERE fieldtype='LINK' ORDER BY options,parent,label LIMIT 99999;
Some as above, but with more explicit column heads:
SELECT options AS 'A Link field to doc of DocType', parent AS 'is in the Form of DocType', idx AS 'Pos', label AS 'The Field label in the Form is', fieldname AS 'Database field name' FROM tabDocField WHERE fieldtype='LINK' ORDER BY options,parent,idx LIMIT 99999;
Self-referencing DocTypes:
SELECT options,label FROM tabDocField WHERE options=parent ORDER BY label, options;
Dto., with variations regarding amendable DocTypes (get these out of the way, or use this as proxy for submittable DocTypes):
SELECT options,label FROM tabDocField WHERE options=parent AND label!='Amended From' ORDER BY label,options;
SELECT options,label FROM tabDocField WHERE options=parent AND label='Amended From' ORDER BY options;
Dto., candidates for Tree Structure:
SELECT options,label FROM tabDocField WHERE options=parent AND label LIKE 'Parent%' ORDER BY options;