Is SUM excluded from client-side script?

ERPNext User Forum
1

Hi

I have created a client-side “script” report. Reason for client-side : I want to put it on a Frappe-Cloud site

in which case I cannot modify the file-system.

Al works but when I add the “sum” on the one SELECT phrase, then I get one of two erros..

… TypeError: Field’ object is not subscriptable

or

Sum is undefined

    query = (
            frappe.qb.from_(soi)
            .inner_join(bi)
            .on(bi.parent == soi.bom_no)
            .select(
                (soi.parent).as_("Sales Order"),
                (soi.delivery_date).as_("Delivery Date"),
                (soi.item_code).as_("Product"),
                (bi.item_code).as_("Item"),
                ((bi.qty_consumed_per_unit).sum()).as_("Required"),
                (soi.delivered_qty).as_("Delivered"),
            )
            .where(soi.parent == "SAL-ORD-0000115")
            .groupby(bi.item_code,soi.delivery_date)
            .orderby(soi.delivery_date)
    )
    return query.run()

The other version I tried …..

    query = (
            frappe.qb.from_(soi)
            .inner_join(bi)
            .on(bi.parent == soi.bom_no)
            .select(
                (soi.parent).as_("Sales Order"),
                (soi.delivery_date).as_("Delivery Date"),
                (soi.item_code).as_("Product"),
                (bi.item_code).as_("Item"),
                (sum(bi.qty_consumed_per_unit)).as_("Required"),
                (soi.delivered_qty).as_("Delivered"),
            )
            .where(soi.parent == "SAL-ORD-0000115")
            .groupby(bi.item_code,soi.delivery_date)
            .orderby(soi.delivery_date)
    )
    return query.run()

Is “sum” really exluded from use on a client-side script ?

2

You have to import the Sum function from query builder like this:

from frappe.query_builder.functions import Sum

then replace ((bi.qty_consumed_per_unit).sum()).as_("Required"), with Sum(bi.qty_consumed_per_unit).as_("Required"),

3

The issue is that you need to import Sum from Frappe’s query builder functions. Change your code to

from frappe.query_builder.functions import Sum

query = (
    frappe.qb.from_(soi)
    .inner_join(bi)
    .on(bi.parent == soi.bom_no)
    .select(
        (soi.parent).as_("Sales Order"),
        (soi.delivery_date).as_("Delivery Date"),
        (soi.item_code).as_("Product"),
        (bi.item_code).as_("Item"),
        Sum(bi.qty_consumed_per_unit).as_("Required"),
        (soi.delivered_qty).as_("Delivered"),
    )
    .where(soi.parent == "SAL-ORD-0000115")
    .groupby(bi.item_code, soi.delivery_date)
    .orderby(soi.delivery_date)
)
return query.run()

Use Sum(field) not (field).sum() or sum(field). This works in client-side scripts.