Item Rounding Tool - Purchase Order, Invoice, Quotation, Purchase Receipt

ERPNext Buying
1

I have been working with my staff very closely in recent days, and we came up with an idea to make data entry slightly faster.

I have configured the Float Precision to 6 in the Setup>System Settings>Date and Number Format section.

For a given line item in a purchase invoice, purchase order, quotation, etc. ERPNext requires at the very least:

  1. Quantity
  2. Rate

And then it will calculate the Amount that will be summed for the Total of the document being modified. In this example, it can either be a Purchase Order, Invoice, Quotation or Purchase Receipt.

Let’s assume for a moment that we are entering a purchase Invoice for Fuel, where only the following data is shown:
Quantity: 30
Amount: 125.00

If you divide this, depending on the calculator you use, you get different results. (125.00 /30) On the calculator App in MAC OS X Sierra, I get:
Rate: 4.166666666666667

Thus, Quantity: 30 x Rate: 4.166666666666667 = 125.00 Amount
However in ERPNext, it does NOT come up to exactly 125.00, but rather it adds a cent here and there.
The way we enter it is like this: Quantity: 29.9760192 x Rate: 4.17 = 125.0000001
For our purposes, this is as precise as we can get with the Amount, and we are OK with this.
The workaround we use when non-precise amounts or rounded amounts are entered, is that we adhere to a strict policy that the Amount field in ERPNext has to equal the Amount printed on the invoice. We then give priority to the Rate (Price) of the item, and last, the Quantity. What I mean by this is that we make the calculation in such a way that the Amount calculated by ERPNext matches the Amount on the invoice, and then we modify the Quantity of the item by small increments, using the standalone calculator application on the computer, to figure out what the quantity should be for the Rate to remain untouched, and the Amount to be exactly as put forth by the invoice, (We do it this way because I would rather round off inventory than the accounts as a matter of preference).

The workflow is currently like this for each item where we need a precise Amount

  1. Enter Quantity

  2. Enter Rate

  3. Press TAB or ESC (Imprecise Amount is shown)

  4. Open calculator in OS

  5. Enter Amount

  6. Divide by Rate

  7. Copy the result with all its decimal numbers Quantity

  8. Switch back to ERPNext window where item was being entered

  9. Click or select Quantity field

  10. Paste calculator result

  11. Press TAB or ESC to verify result

  12. Data is now correct for Quantity, Rate and Amount, with Amount matching the value on the physical invoice being entered, Rate having suffered no modification, and Quantity adjusted for precision of Amount

This ten step process gets tedious after a while, and I figured that perhaps a feature can be added as a control on each line that will calculate the Quantity (or the Rate) from a verified/specified Amount that is re-entered manually, only where needed.

The Workflow would be:

  1. Enter Quantity

  2. Enter Rate

  3. Press TAB or ESC (Imprecise Amount is shown)

  4. Press the Rounding Tool button (Pop up window is shown with Quantity, Rate and Amount (Amount already selected)

  5. Amount is modified manually to desired value.

  6. User clicks on checkbox below either Rate fields, to “fix” value.

  7. ERPNext displays the result of the modified Quantity in the pop up window

  8. User presses ESC, or TAB or ENTER

  9. Data is now correct for Quantity, Rate and Amount, with Amount matching the value on the physical invoice being entered, Rate having suffered no modification, and Quantity adjusted for precision of Amount

The workflow described just above, has three less steps than the previous workflow, and it also has the advantage that the user does not need to switch between windows with a mouse or by tapping. He/She can simply do all the calculations within ERPNext. (Similar to what Quicken does with its in-field calculator.)

To further reduce the workflow, one could have a “priority” setup in System Settings, where you preselect which two of these three items will be fixed when doing a calculation of this sort. For example, you can specify that Amount is the desired result, and that Rate will be fixed, so that if you already have an existing rate in ERPNext, as soon as you enter the amount in the proposed pop up window, the calculation is complete. No need to verify!

That workflow would go something like this:
The Workflow would be:

  1. Enter Quantity

  2. Enter Rate

  3. Press TAB or ESC (Imprecise Amount is shown)

  4. Press the Rounding Tool button (Pop up window is shown with Quantity, Rate and Amount (Amount already selected)

  5. Amount is modified manually to desired value.

  6. ERPNext displays the result of the modified Quantity in the pop up window (Given that Rate is marked as fixed)

  7. User presses ESC, or TAB or ENTER

  8. Data is now correct for Quantity, Rate and Amount, with Amount matching the value on the physical invoice being entered, Rate having suffered no modification, and Quantity adjusted for precision of Amount

I want to hear opinions on this matter!

2 Likes
2

Maybe a trigger on Amount to back-calculate might also work (without the popup!)

1 Like
3

Ok, so your proposed process is as such:

  1. You enter exact desired Amount in item line
  2. Given a preselected fixed operand (Qty or Rate), ERPNext highlights the next required field to prompt user to enter the required number.
  3. User enters the number
  4. on TAB, save or mouseclick elsewhere, ERPNext calculates the non-priority operand.

Brilliant!
Much shorter and simpler.
I will submit GitHub feature request.

Thanks Rushabh!

4

Reviving this issue because we have a workable solution.
Example: Purchase Invoice, in developer mode

  1. Add a custom field: amount_back_calc, type Currency, (right below amount field in Purchase Invoice Item

  2. Add this javascript to your public folder: /home/frappe/frappe-bench/apps/erpnext/public

  3. Modify your build.json to ensure that when building the newly downloaded javascript is included in the minified version when doing bench build

  4. Alternatively add all the contents of the js to the custom script you want to create.

  5. Add this to a trigger of the amount_back_calc custom field.

    frappe.ui.form.on("Purchase Invoice Item", {
    amount_for_back_calc: function (frm, cdt, cdn) {
        frm.doc.items.forEach((row, index) => {
            var a = row.qty;
            var b = row.rate;
            let calc = goalSeek({
                Func: item_rounding_tool,
                aFuncParams: [a, b],
                oFuncArgTarget: {
                    Position: 1
                },
                Goal: row.amount_for_back_calc,
                Tol: 0.001,
                maxIter: 10000
            });
            // run this console log to see the result in the console for debugging. comment in production.
            console.log(calc);

            frm.doc.items[index].qty = calc;
            frm.doc.items[index].amount = calc * frm.doc.items[index].rate;
            frm.refresh_field("items");
        });
    }
});

    function item_rounding_tool(a, b) {
        return a * b;
    }
  1. Usage: User enters a quantity and rate in the Purchase Invoice and then right below the amount field, enters the line amount desired, thus that it recalculates the quantity properly. Each time the back calculation amount is re-entered, the quantity changes. The rate is left unchanged.
1 Like
5

how would you modify this , so that when the back cal amount is entered ,i.e the desired amount , instead of the qty , the rate gets updated , and the qty is left unchanged , i.e adjust the rate rather than qty to get the required line amount .

6

we have a similar case, this javascript https://github.com/adam-hanna/goalSeek.js/blob/master/goalSeek.js is not available now, can you help to provide a valid link or provide the code in this post again?
Many thanks.