Populating Custom Fields for Existing Submitted "Quotation Item" Documents

I’m trying to populate newly created custom fields on the “Quotation Item” doctype with calculated data. These custom fields capture cumulative quantities for each item. While the get_cumulative_quantities method successfully populates these fields for new records, it doesn’t update existing submitted documents.

Question:

How can I modify my approach to populate these custom fields with the calculated data for already submitted “Quotation Item” documents, not just new ones?

Additional Information:

  • Is there a recommended way to trigger updates on existing documents after adding a custom field?
  • Are there any alternative methods to achieve this functionality?
@frappe.whitelist()
def get_cumulative_quantities(item_code):
    if not item_code:
        return {}

    cumulative_actual_qty = frappe.db.sql("""
        SELECT SUM(actual_qty) FROM `tabBin` WHERE item_code = %s
    """, (item_code,))[0][0] or 0

    cumulative_reserved_qty = frappe.db.sql("""
        SELECT SUM(reserved_qty) FROM `tabBin` WHERE item_code = %s
    """, (item_code,))[0][0] or 0

    cumulative_projected_qty = frappe.db.sql("""
        SELECT SUM(projected_qty) FROM `tabBin` WHERE item_code = %s
    """, (item_code,))[0][0] or 0

    return {
        "custom_cumulative_actual_qty": cumulative_actual_qty,
        "custom_cumulative_reserved_qty": cumulative_reserved_qty,
        "custom_cumulative_projected_qty": cumulative_projected_qty,
    }```


item_code: function(frm, cdt, cdn) {
    let row = locals[cdt][cdn];
    if (row.item_code) {
        frappe.call({
            method: 'florence.quotation.get_cumulative_quantities',
            args: {
                item_code: row.item_code
            },
            callback: function(r) {
                if (r.message) {
                    frappe.model.set_value(cdt, cdn, 'custom_cumulative_actual_qty', r.message.custom_cumulative_actual_qty);
                    frappe.model.set_value(cdt, cdn, 'custom_cumulative_reserved_qty', r.message.custom_cumulative_reserved_qty);
                    frappe.model.set_value(cdt, cdn, 'custom_cumulative_projected_qty', r.message.custom_cumulative_projected_qty);
                }
            }
        });
    }
}

You can execute it directly using bench execute command and pass your ite_code from terminal

https://frappeframework.com/docs/user/en/bench/resources/bench-commands-cheatsheet

@Sudhanshu if u can update the your past record then enter the qty manually in your field based on the logic because the bin records are the real time so u can not find the past record bin qty

So u can allow on submit your custom field and update last all records

@Meet the problem is i have 1000+ records and the users dont want that update button all the time