pymysql.err.OperationalError: (1054, "Unknown column 'incoming_rate' in 'field list'")

sameermahendru · October 22, 2022, 3:39pm

I keep getting the following error when I try to create a purchase order from a sales order and try to save the purchase.

I have updated and migrated successfully but the error remains.

My site is hosted on frappe cloud.

Any suggestions would be appreciated.

Installed Apps
ERPNext: v13.40.2 (HEAD)

Frappe Framework: v13.41.7 (HEAD)

Traceback (most recent call last):
File “apps/frappe/frappe/app.py”, line 69, in application
response = frappe.api.handle()
File “apps/frappe/frappe/api.py”, line 55, in handle
return frappe.handler.handle()
File “apps/frappe/frappe/handler.py”, line 38, in handle
data = execute_cmd(cmd)
File “apps/frappe/frappe/handler.py”, line 76, in execute_cmd
return frappe.call(method, **frappe.form_dict)
File “apps/frappe/frappe/init.py”, line 1457, in call
return fn(*args, **newargs)
File “apps/frappe/frappe/desk/form/save.py”, line 24, in savedocs
doc.save()
File “apps/frappe/frappe/model/document.py”, line 310, in save
return self._save(*args, **kwargs)
File “apps/frappe/frappe/model/document.py”, line 332, in _save
return self.insert()
File “apps/frappe/frappe/model/document.py”, line 261, in insert
self.run_before_save_methods()
File “apps/frappe/frappe/model/document.py”, line 1052, in run_before_save_methods
self.run_method(“validate”)
File “apps/frappe/frappe/model/document.py”, line 941, in run_method
out = Document.hook(fn)(self, *args, **kwargs)
File “apps/frappe/frappe/model/document.py”, line 1261, in composer
return composed(self, method, *args, **kwargs)
File “apps/frappe/frappe/model/document.py”, line 1243, in runner
add_to_return_value(self, fn(self, *args, **kwargs))
File “apps/frappe/frappe/model/document.py”, line 938, in fn
return method_object(*args, **kwargs)
File “apps/erpnext/erpnext/buying/doctype/purchase_order/purchase_order.py”, line 54, in validate
super(PurchaseOrder, self).validate()
File “apps/erpnext/erpnext/controllers/buying_controller.py”, line 33, in validate
super(BuyingController, self).validate()
File “apps/erpnext/erpnext/controllers/stock_controller.py”, line 42, in validate
super(StockController, self).validate()
File “apps/erpnext/erpnext/controllers/accounts_controller.py”, line 155, in validate
self.set_incoming_rate()
File “apps/erpnext/erpnext/controllers/buying_controller.py”, line 307, in set_incoming_rate
frappe.db.get_value(ref_doctype, d.get(frappe.scrub(ref_doctype)), field)
File “apps/frappe/frappe/database/database.py”, line 469, in get_value
limit=1,
File “apps/frappe/frappe/database/database.py”, line 540, in get_values
limit=limit,
File “apps/frappe/frappe/database/database.py”, line 739, in _get_values_from_table
update=update,
File “apps/frappe/frappe/database/database.py”, line 174, in sql
self._cursor.execute(query, values)
File “env/lib/python3.7/site-packages/pymysql/cursors.py”, line 148, in execute
result = self._query(query)
File “env/lib/python3.7/site-packages/pymysql/cursors.py”, line 310, in _query
conn.query(q)
File “env/lib/python3.7/site-packages/pymysql/connections.py”, line 548, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “env/lib/python3.7/site-packages/pymysql/connections.py”, line 775, in _read_query_result
result.read()
File “env/lib/python3.7/site-packages/pymysql/connections.py”, line 1156, in read
first_packet = self.connection._read_packet()
File “env/lib/python3.7/site-packages/pymysql/connections.py”, line 725, in _read_packet
packet.raise_for_error()
File “env/lib/python3.7/site-packages/pymysql/protocol.py”, line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File “env/lib/python3.7/site-packages/pymysql/err.py”, line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1054, “Unknown column ‘incoming_rate’ in ‘field list’”)

sameermahendru · November 2, 2022, 7:54am

Anyone able to help?

maxchock · November 8, 2022, 4:38am

I’m having the same error too. Manually create the PO and link it to Sales order with the same item and same supplier has no error.

Looking for any suggestion. Thanks.

maxchock · November 9, 2022, 4:55am

I notice the error is because of the particular supplier that I issue PO is actually an Internal Supplier…

sameermahendru · December 15, 2022, 6:55am

I have noticed the same issue occurs for me on when the PO is made against an internal supplier.

I am no expert and do not know how all the various code files interact, but I think I have isolated the issue to the following function in buying_controller.py:

def set_incoming_rate(self):
	if self.doctype not in ("Purchase Receipt", "Purchase Invoice", "Purchase Order"):
		return

	ref_doctype_map = {
		"Purchase Order": "Sales Order Item",
		"Purchase Receipt": "Delivery Note Item",
		"Purchase Invoice": "Sales Invoice Item",
	}

	ref_doctype = ref_doctype_map.get(self.doctype)
	items = self.get("items")
	for d in items:
		if not cint(self.get("is_return")):
			# Get outgoing rate based on original item cost based on valuation method

			if not d.get(frappe.scrub(ref_doctype)):
				posting_time = self.get("posting_time")
				if not posting_time and self.doctype == "Purchase Order":
					posting_time = nowtime()

				outgoing_rate = get_incoming_rate(
					{
						"item_code": d.item_code,
						"warehouse": d.get("from_warehouse"),
						"posting_date": self.get("posting_date") or self.get("transation_date"),
						"posting_time": posting_time,
						"qty": -1 * flt(d.get("stock_qty")),
						"serial_no": d.get("serial_no"),
						"batch_no": d.get("batch_no"),
						"company": self.company,
						"voucher_type": self.doctype,
						"voucher_no": self.name,
						"allow_zero_valuation": d.get("allow_zero_valuation"),
					},
					raise_error_if_no_rate=False,
				)

				rate = flt(outgoing_rate * (d.conversion_factor or 1), d.precision("rate"))
			else:
				***field = "incoming_rate" if self.get("is_internal_supplier") else "rate"***
				rate = flt(
					frappe.db.get_value(ref_doctype, d.get(frappe.scrub(ref_doctype)), field)
					* (d.conversion_factor or 1),
					d.precision("rate"),
				)

			if self.is_internal_transfer():
				if self.doctype == "Purchase Receipt" or self.get("update_stock"):
					if rate != d.rate:
						d.rate = rate
						frappe.msgprint(
							_(
								"Row {0}: Item rate has been updated as per valuation rate since its an internal stock transfer"
							).format(d.idx),
							alert=1,
						)
					d.discount_percentage = 0.0
					d.discount_amount = 0.0
					d.margin_rate_or_amount = 0.0

Any ideas how to elevate this to the erpnext team to check?

sameermahendru · December 15, 2022, 6:58am

The particular line in this code is on line number 313 of buying_controller.py and is as follows:

field = "incoming_rate" if self.get("is_internal_supplier") else "rate"

In Purchase Order Item table I am unable to find any field titled “incoming_rate”.

sameermahendru · December 15, 2022, 7:31am

I have raised the following issue on Erpnext’s issues page on Github:

Cannot Submit Purchase Order that has been Created from Sales Order when Supplier is Internal Supplier #33349

Npk-codes · December 29, 2023, 2:09pm

The error occurs due to the absence of the ‘incoming_rate’ field in either the sales order or purchase order. To resolve this issue in Frappe:

1. Add a new custom field named ‘incoming_rate’ to the Sales Order.
2. Add the same custom field (‘incoming_rate’) to the Purchase Order.

Ensure that both custom fields are of the same type and have the necessary permissions. This addition ensures that the required field exists for the condition check during the creation of internal purchase orders in Frappe.

ashaseeb · March 16, 2024, 4:34am

facing same issue on version 15 anyone can help to resolved

Server Error

pymysql.err.OperationalError: (1054, “Unknown column ‘published_in_website’ in ‘field list’”)
Possible source of error: webshop (app)

ashaseeb · October 26, 2024, 6:15pm

finally, I resolved it, the reason was the missing fields and after installing updates,

however, follow these steps:

Customize Form:

Add a new custom field named ‘incoming_rate’ to the Sales Order.
Add the same custom field (‘incoming_rate’) to the Purchase Order.

Bench Commands:

bench --site execute webshop.setup.install.after_install.
bench --site migrate
bench sudo supervisorctl reload