when i am using frappe.db.sql … i am facing a error
anyone help me .
{%- for row in doc.yarn_description -%}
<tr>
<td>{{row.idx}}</td>
<td>{{row.yarn_specification}}</td>
<td>{{row.qnty_in_kgs}}</td>
<td>{{row.imp_qnty}}</td>
<td>{{row.b2b_lc_no}}</td>
<td>{{row.balance}}</td>
<td>{% if row.remarks %} {{row.remarks}} {% endif %}</td>
</tr>
{%- endfor -%}
{% set get_sumation=frappe.db.sql("""select sum(`qnty_in_kgs`) as 'qnty_in_kgs', sum(`imp_qnty`) as 'imp_qnty' , sum(`balance`) as 'balance' from `tabYarn Description child`""") %}
<tr>
<td></td>
<td>Total :-</td>
<td>{{get_sumation[0]}}</td>
<td>28080</td>
<td></td>
<td>-970</td>
<td></td>
</tr>
</tbody>
</table>
</div>
Sl No
Yarn Description
Qnty in Kgs.
B2B L/C no
Mills
Balance
Remark
Hi I can see that you are looping a child table, assuming that this is item table why not do the calculation on custom script?
1 Like
Hi @Rahib_Hasan ,
Did you manage to find a solution?, i am also in need of similar workout.
Yes I Found This Solution this way Because frappe.db.sql is not working on print format but frappe.get_all() , get_value(). its working
{% set qty_in_kgs_total = [0] %}
{% set balance_total=[0] %}
{% set imp_qnty=[0] %}
{%- for row in doc.yarn_description -%}
{% set _ = qty_in_kgs_total.append(qty_in_kgs_total.pop() + row.qnty_in_kgs|int) %}
{% set _ = balance_total.append(balance_total.pop()+row.balance|int ) %}
{% set _ =imp_qnty.append(imp_qnty.pop()+row.imp_qnty|int ) %}
<tr>
<td>{{row.idx}}</td>
<td>{{row.yarn_specification}}</td>
<td>{{row.qnty_in_kgs}}</td>
<td>{% if row.imp_qnty %} {{row.imp_qnty}} {% endif %}</td>
<td>{% if row.b2b_lc_no %} {{row.b2b_lc_no}} {% endif %}</td>
<td>{% if row.mills %} {{row.mills}} {% endif %}</td>
<td>{{row.balance}}</td>
<td>{% if row.remarks %} {{row.remarks}} {% endif %}</td>
</tr>
{%- endfor -%}
<tr>
<td></td>
<td>Total :-</td>
<td>{{ qty_in_kgs_total[0] }}</td>
<td>{{ imp_qnty[0] }}</td>
<td></td>
<td></td>
<td>{{balance_total[0]}}</td>
<td></td>
</tr>
</tbody>
</table>
Sl No
Yarn Description
Qnty in Kgs.
Imp Qnty.
B2B L/C no
Mills
Balance
Remark
Thank you for the update. But i need to use sql with group clause.
@Rahib_Hasan then how can i get value from diferent doctype?
lokesh
February 18, 2020, 5:31am
7
You can use get_value
and get_all
as simply you used in .py.
Still if you want to use sql query, you can do this by calling your python function from print format. Just use doc.your_function_name
On your .py file you can define this function and return sql query.
frappe.db.sql
query i specifically used for a query where i need to use joins. For other purpose we have get_value
and get_all
.