Group data and calculate sum by custom fields in item table

1

Hello,

There is an option in PO as ‘Group same items’ that gives the aggregate value (Qty & Amount) of same items if appeared in multiple rows. However, it has no option to take custom field/column as variable.

We have 2 custom fields as SIZE and COLOR in the child table ‘Purchase Order Item’. For example here are 2 items as Item A & Item B. We have Green color for both and M & L size as follows:

itema-itemb-sum
itema-itemb-sum1059×383 28.7 KB

Following the tutorial of Jinja method here, how can I achieve the above sum for Item A & B with Color & Size. There might be only SIZE or COLOR too.

https://discuss.frappe.io/t/tutorial-jinja-how-to-group-data-and-calculate-sum/59395

Any solution will be greatly appreciated. Thanks in advance.

2

I have the following code by the kind courtesy of @Jeel :slightly_smiling_face:

{% set grouped_items = {} %}

{% for item in doc.items %}
{% set key = item.color + ‘-’ + item.size + ‘-’ + item.item_code %}
{% if key not in grouped_items %}
{% set grouped_items = grouped_items | merge({ key: {‘qty’: 0, ‘amount’: 0} }) %}
{% endif %}
{% set grouped_items = grouped_items | merge({ key: { ‘qty’: grouped_items[key].qty + item.qty, ‘amount’: grouped_items[key].amount + item.amount } }) %}
{% endfor %}

{% for key, values in grouped_items.items() %} {% set parts = key.split(‘-’) %} {% endfor %}
Item Code Color Size Quantity Amount
{{ parts[2] }} {{ parts[0] }} {{ parts[1] }} {{ values.qty }} {{ values.amount }}

This has the error while saving the code in a custom Print Format:
Syntax error in template as line 8: No filter named ‘merge’.

What correction should be made?

I want to achieve the following of my above screenshot in the original question when grouped by the code:

Item A, GREEN color, M size, 200 qty, Amount 2000
Item A, RED color, M size, 150 qty, Amount 4500
Item A GREEN color, L size, 50 qty, Amount 1000
Item B, GREEN color, M size, 500 qty, Amount 5000

If there were no colors but only size in the above example:
Item A, M size, 350 qty, Amount 3500 (Example=@BDT 10, ignoring color wise rates)
Item A, L size, 50 qty, Amount 1000
Item B, M size, 500 qty, Amount 5000

If there were no size but only colors:
Item A, GREEN color, 250 qty, Amount 2500 (Example: @BDT 10, ignoring size wise rate))
Item A, RED color, 150 qty, Amount 4500
Item B, GREEN color, 500 qty, Amount 5000

Sometimes there might only be color or size and other times both like this example.

Thanks.

3

Please try it.

{%- set grouped_items = {} -%}
{%- for item in doc.items -%}
    {%- set group_key = item.item_code ~ (item.color or '') ~ (item.size or '') -%}
    {%- if group_key not in grouped_items -%}
        {%- set _ = grouped_items.update({
            group_key: {
                'item_code': item.item_code,
                'color': item.color,
                'size': item.size,
                'qty': item.qty,
                'amount': item.amount,
                'rate': item.rate
            }
        }) -%}
    {%- else -%}
        {%- set _ = grouped_items[group_key].update({
            'qty': grouped_items[group_key].qty + item.qty,
            'amount': grouped_items[group_key].amount + item.amount
        }) -%}
    {%- endif -%}
{%- endfor -%}

<table class="table table-bordered">
    <thead>
        <tr>
            <th>Item Code</th>
            <th>Color</th>
            <th>Size</th>
            <th>Qty</th>
            <th>Amount</th>
        </tr>
    </thead>
    <tbody>
        {%- for key, item in grouped_items.items() -%}
        <tr>
            <td>{{ item.item_code }}</td>
            <td>{{ item.color or '' }}</td>
            <td>{{ item.size or '' }}</td>
            <td>{{ item.qty }}</td>
            <td>{{ item.amount }}</td>
        </tr>
        {%- endfor -%}
    </tbody>
</table>

few things tested, now set your according to the scenario.
Also check your field name in code.

2 Likes
4

@NCP, It works great and flawlessly! You saved the day. This is the first such example in Jinja for such conditions which I did not find in the forum through search and compelled to create this post. I hope this will help others with less Dev knowledge/experience like myself as well and make them solve similar issues by themselves with this standard example.

Many thanks for your valuable community contributions you provide the forum besides maintaining your regular activities.

Kind regards.