Try ERPNext Buy Support Partners Foundation

Rest API. Error when querying list of documents with complex field(Table) included

Hi all. I’m trying to integrate app with ERP Next, in my case I need to pull some data from ERP to other system.
ERPNext: v12.4.3 (version-12)
Frappe Framework: v12.2.1 (version-12)

Problem that I can’t include complex field (type Table) to be in response when I query a list of documents. Can anyone suggest how to achieve this, if possible, within one request?

For example, when I query single document, let’s say Appraisal, with HTTP GET to
domain/api/resource/Appraisal/HR-APR-20-0700004
I got that document with all fields I need. One of them are Goals.

Screenshot

But if I try to query a list of documents and specify that I need field Goals to be in response, I get following error.
Request URL:
domain/api/resource/Appraisal/?fields=["name","kra_template", "employee_name", "empl_hr_id", "goals"]

Error text

Traceback (most recent call last):
File “/erpnext/frappe-bench/apps/frappe/frappe/app.py”, line 60, in application
response = frappe.api.handle()
File “/erpnext/frappe-bench/apps/frappe/frappe/api.py”, line 116, in handle
doctype, **frappe.local.form_dict)})
File “/erpnext/frappe-bench/apps/frappe/frappe/init.py”, line 1042, in call
return fn(*args, **newargs)
File “/erpnext/frappe-bench/apps/frappe/frappe/client.py”, line 35, in get_list
limit_start=limit_start, limit_page_length=limit_page_length, ignore_permissions=False)
File “/erpnext/frappe-bench/apps/frappe/frappe/init.py”, line 1278, in get_list
return frappe.model.db_query.DatabaseQuery(doctype).execute(None, *args, **kwargs)
File “/erpnext/frappe-bench/apps/frappe/frappe/model/db_query.py”, line 96, in execute
result = self.build_and_run()
File “/erpnext/frappe-bench/apps/frappe/frappe/model/db_query.py”, line 130, in build_and_run
return frappe.db.sql(query, as_dict=not self.as_list, debug=self.debug, update=self.update)
File “/erpnext/frappe-bench/apps/frappe/frappe/database/database.py”, line 171, in sql
self._cursor.execute(query)
File “/erpnext/frappe-bench/env/lib/python3.6/site-packages/pymysql/cursors.py”, line 170, in execute
result = self._query(query)
File “/erpnext/frappe-bench/env/lib/python3.6/site-packages/pymysql/cursors.py”, line 328, in _query
conn.query(q)
File “/erpnext/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py”, line 517, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “/erpnext/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py”, line 732, in _read_query_result
result.read()
File “/erpnext/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py”, line 1075, in read
first_packet = self.connection._read_packet()
File “/erpnext/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py”, line 684, in _read_packet
packet.check_error()
File “/erpnext/frappe-bench/env/lib/python3.6/site-packages/pymysql/protocol.py”, line 220, in check_error
err.raise_mysql_exception(self._data)
File “/erpnext/frappe-bench/env/lib/python3.6/site-packages/pymysql/err.py”, line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.InternalError: (1054, “Unknown column ‘goals’ in ‘field list’”)

Querying list of documents without complex fields included works ok, but not satisfy my requirements.

Example without complex fields

Image removed due to limitation one image in post for new user. Could provide it on request.

Hello,

You may want to separately query the Table field using its’ DocType (e.g. Appraisal Goal) and use the parent field matching the parent doctype name.

domain/api/resource/Appraisal Goal/?filters=[["parent", "=", <parent-doctype-name>]]

Regards,

Ivan