Hi, I am trying to make my own app in frappe. I have a doctype with various “Check” Field types. I want the checked fields to show up in another doctype.
e.g
First Doctype is called Risk Identification:
Check Fields which define if a particular action is a risk or not i.e “Water Flooding Risk”. Checking this field would define it as a possible risk/hazard.
Multiple Check Fields are present.
Second Doctype is called Risk Details
In here i only want the checked fields from the first doctype to show up so I can enter their further details. If a risk is identified in the first Doctype, I want it to show up in the second doctype. I tried utilizing the Display Depends On method but it only works on fields within the same Doctype.
Any help is welcome. Thanks.
NOTE: There is a particular reason for the choice to separate the two Doctypes, I have over 200+ check Fields in the Doctype, If i try to use Display Depends On and display the fields in the same Doctype, it gives an SQL:Row Size Too Large Error, so i thought I’d separate the Doctype into two parts and Identify the Risks in one Doctype and Fill in their Details in another.
Also I am very new to programming but can understand a bit.
MySQL can handle 1000s of columns in a table, so 200 checkboxes isn’t going to break anything. The Row Size Too Large error isn’t about number of columns directly but rather amount of data stored in a given row. Are you using a text field of some sort in addition to each checkbox?
In general, this is going to be a lot easier (and cleaner) if you don’t try to split it into multiple documents.
This is currently my structure. The fields next to the checkboxes are small text fields. I had to change them to small text from data fields because of the SQL error. The whole issue being that the number of rows which I want to apply a dependent displaying method on are too many and will give an sql error if I use a data or a select field for them.
Like I said before, my understanding is that the error you’re seeing is not the consequence of too many fields. It’s the type of fields you’re using. Data fields use varchar, and those can’t be put off the primary buffer.
Are you saying it’s working now with the small text fields? If so, great.
If you’re really determined to do this on two doctypes, it’s going to require some custom programming. The simplest solution would probably be to design a new child doctype generated dynamically by your details doctype. Depending on the particulars, this could be done on either the server-side or the client-side.
That is what im trying to do, I added a child table, the table using the depends on method. However for each check field type I have to create a Table field and write the display depends upon condition. Could I possibly load data into a single table with the data showing as rows, depending on check field conditions?
Note: I saw some code to load data from one child table to another child table, both being used in different doctypes. Is it possible to load data into a child type based on IF conditions?
This is the code I saw:
"Let me add a nicely formatted example.
'Target DocType' : The one you are currently working with, where the data is supposed to go.
'Source DocType' : The one that contains the data.
link_to_source : Fieldname of the Target DocType that contains the link to 'Source DocType' .
target_table : Fieldname of the table in 'Target DocType' .
source_table : Fieldname of the table in 'Source DocType' .
column_name : Fieldname of the column in Child Table.
frappe.ui.form.on('Target DocType', {
link_to_source: function (frm) {
if (frm.doc.link_to_source) {
frm.clear_table('target_table');
frappe.model.with_doc('Source DocType', frm.doc.link_to_source, function () {
let source_doc = frappe.model.get_doc('Source DocType', frm.doc.link_to_source);
$.each(source_doc.source_table, function (index, source_row) {
frm.add_child('target_table').column_name = source_row.column_name; // this table has only one column. You might want to fill more columns.
frm.refresh_field('target_table');
});
});
}
},
});
"
Can this code be somehow molded to conditionally fill and display data in one child table within the same document?
I’m not really clear what’s happening here. Are you intending to collect multiple responses (from multiple people perhaps?) to each of these questions on a single form? I’m concerned you might be misunderstanding what child tables do, but it’s very possible that I’m just not understanding what you’re trying to do.
With code, absolutely. You can copy, filter, and transform however you want. The sky’s the limit.
I don’t understand what you’re asking, I’m afraid. This code copies data in a child table from one doctype to another. If that’s what you’re trying to do, your code could certainly look something like this.
After a bit more digging, I’ll take back what I said earlier. I don’t think it’s going to be possible to have 600+ fields on a frappe sql table (and there are a lot of UX questions to be raised about having 200+ questions on a single form). I’d strongly suggest rethinking your design here. You could certainly make Frappe do what you’re trying to do, but it’s forcing a square peg into a round hole.
At a structural level, the problem is that the data structure you’ve proposed is highly unnormalized. With relational databases, you really need to define your data more atomically. Instead of sticking 200+ questions in a single row, each question should get its own row to be reassembled in whatever display application you have. ERPNext doctype definitions are a relatively thin layer on top of a SQL database, so that’s not really available out of the box.
You could accomplish a normalized form with child doctypes, but the UX really wasn’t designed for this kind of purpose. It will be extremely tedious for whoever’s filling in the data. If you really need this to be a Frappe app (and, as much as I love Frappe, I’m not convinced this is the right tool for the job), you might consider writing a template-driven custom page.
1.The questions in the first screenshot determine if a particular action is a risk or not based on a check field.
2.The “Risks” depending on Checked or Unchecked will display more fields below which serve their own purpose.
3.I originally intended to use Data or Text or Small Text Fields and create the ‘Dependent’ fields but I came across another error. If multiple fields are dependent one one field for their viewing, the functionality breaks and checking or unchecking the field which defines the viewing of the Dependent fields, it does not work. Apparently one field can have only one dependent field on it, if multiple fields are dependent on one field, the functionality breaks and the display condition does not work.
4.To work around that, I create a Child Table and made multiple fields linking to that child table, its viewing dependent upon the checkfields above.
I don’t know yoir details, but with the amount of data you mention, my feeling is your form is way too big and that indicates a serious flaw in your data design.
I would suggest to sit down and do a database design first.
Specify your data structure before starting implementation.
Think about where there are relations, inheritances, aggregations etc. Where can you generalize by creating new doctypes and including these into you forms?
…
My main advice here: Don’t start with details of the implementation but draw the big picture first.
I agree, the database design is fundamentally flawed. A single document should not have such high amounts of data and so many inter-dependencies. Yet however, this particular app is being developed for a government department and they are adamant about not breaking the documents into segments.
Not true, and anyway that’s not the issue here. The issue is that you’re trying to put 600+ columns in a single SQL table. Anybody will tell you that’s a bad design from the outset.
Good design here doesn’t need to mean breaking the questionnaire into segments. A properly normalized data structure here could still be very easily displayed on a single page to the end user. It’s the underlying organization of the database that’s the problem. Right now, you’re trying to do:
#
Question 1
Q1 details A
Q1 details B
Question 2
Q2 details A
Q2 details B
Question 3
etc…
1
x
lorem ipsum
amor sit
x
2
x
foo
bar
3
etc…
That’s a highly unnormalized data structure, with hundreds of columns, which is extremely inefficient in relational databases like SQL. A normalized data structure design would something like:
#
Question ID
Checked
Details A
Details B
1
Question 1
x
lorem ipsum
amor sit
1
Question 2
1
Question 3
x
2
Question 1
2
Question 2
x
foo
bar
2
Question 2
3
etc…
Do you see the difference? It’s the same data, but 600 columns vs 5. You can do normalized data this way in Frappe, of course, but the default Frappe form interface isn’t anything anybody is going to want to use. If you really want to use Frappe here (and it still seems like the wrong tool for the job), you should look into designing a proper normalized data structure and then using a Custom Page to display the data according to your needs.
You’re right, the second structure design looks more feasible and I would naturally want to implement this. However Frappe only allows Child Doctypes to be used in tables, I have to work around that and make sections which are broken into parts for each Individual Question.
However if i am to design a Custom Page, how would I be able to do that?
Furthermore, For some reasons using one field as a Dependency to display multiple fields i.e, Display Depends On : “ck6”. If i apply this condition to multiple fields it does not work. Checking or unchecking “ck6” has no effect as none of the dependent fields show up.
Did you consider creating a page instead of using a doctype form? Pages and Web pages (they are not the same in ERPNext) are a quite powerfull and you can implement whatever logic and relationship between doctypes and fields using python.
Basically Pages are “Web pages” for the internal system.
There are some standard pages in ERPNext, search for “Page List”. The Permission Manager is an example of a Page, or the Dashboard.
I think for your use case that’s the way to go. Using python/html/css you can create a view for your data base model under the hood. You can make the data model neat and clean and still provide a single input form if that is the requirement.
Exactly, that’s why I’ve been suggesting that Frappe might not be a great fit for this job. You could make it work with Pages, as @Paul_Frydlewicz’s link below suggests, but it’s not immediately clear to me what Frappe adds over a much simpler framework (or even a purpose-built questionnaire tool). Frappe shines as a transactional document management system, but it really wasn’t built for this kind of bulk static data.
There could be a viable use case here if you need to integrate this data closely with transactional documents (like in a medical records system of some sort), but it’s not going to work the way you want it to out of the box.
It’s hard to be specific without knowing more about exactly what you’re doing and which version you’re running, but I was able to do what you’re describing on my test system. Video below:
@peterg might be right. ERPNext is not necessary the correct tool unless you don’t plan to do more ERP features.
However, imho the Frappe framework is exactly the tool your looking for. Just without the ERPNext functions (no need to install the ERPNext app).
So you have the whole DB and web based framework and just put your application on top.
What you can do is to create a custom app with your function and use just the Frappe and bench framework.
