I want to create a report where child table data is included in it. I’m having a problem using query report on how to do it. Does anyone have any idea on how to do this?
It works just like any other table:
SELECT * FROM `tabChild_Table_Name`;
Sorry newbie in SQL here…what JOIN should I use?
In general, it would be really helpful if you could be more specific about what you are trying to do, what you have tried, and what didn’t work. It is very difficult to answer vague questions in a useful way.
The JOIN you should use depends on the results you want. For a one-to-many relationship like I’m assuming you’ve got here, you probably want a LEFT JOIN or a regular (inner) JOIN. The difference will be whether parents without children show up or not.
What I’m planning to is this
The “Description of Items” column should fetch data from the child table and that child table has different fields. I want to concatenate those fields and put them in one column, which is “Description of Items”. Do you have any idea how to do it?
It’s a bit more complicated because you want to concatenate child rows into a single value. Often you would process that kind of stuff after your query, but for a simple case like this you can use GROUP_CONCAT.
Something like:
SELECT parentTab.*,
GROUP_CONCAT(childTab.item_description SEPARATOR ', ')
FROM parentTab
LEFT JOIN childTab
ON parentTab.name = childTab.parent
GROUP BY parentTab.id;
Hi, thanks for reply. I tried your suggested code but it only shows 1 row of the child table. How do we do it for multiple rows?
It should show one row for each entry in the parent table, with data from multiple child table entries aggregated into a single field. If that’s not what you want, can you please write out exactly what you’re trying to do?
What I want to do is this:
Supposedly after each semicolon, the data for the next row should show up
hello , did you found solution for the report ?
// create a report
frappe.query_reports[“Child Table Report”] = {
“filters”: [
{
“fieldname”: “parent”,
“label”: __(“Parent”),
“fieldtype”: “Link”,
“options”: “Parent Table”
}
],
“formatter”: function(row, cell, value, columnDef, data