I created a Virtual Doctype that populates data from a REST API. The list view doesn’t show any fields by default. I have to manually edit the list view settings in order to get the list to show some fields.
I have set in_list_view:1 for several fields in the doctype.json file. What else should I check?
in_list_view is set for all of the fields that I want in the list view
import frappe
import requests
from frappe.model.document import Document
class Form470(Document):
"""Virtual Doctype that fetches Form 470 data from the USAC Open Data API"""
BASE_URL = "https://opendata.usac.org/resource/jt8s-3q52.json"
@staticmethod
def fetch_data():
"""Fetch data from the USAC API."""
url = Form470.BASE_URL
try:
response = requests.get(url)
response.raise_for_status() # Raise an error for bad status codes (4xx, 5xx)
data = response.json()
return {doc["application_number"]: doc for doc in data} # Keyed by application number
except requests.RequestException as e:
frappe.logger().error(f"Error fetching data from USAC API: {e}")
return {}
def load_from_db(self):
"""Load a single record by application_number."""
data = Form470.fetch_data()
record = data.get(self.name) # self.name is the application_number
if not record:
frappe.throw(f"No Form 470 record found for {self.name}")
self.update(record)
def db_insert(self, *args, **kwargs):
"""Virtual Doctype does not support inserting new records."""
frappe.throw("Cannot insert records in a Virtual Doctype.")
def db_update(self, *args, **kwargs):
"""Virtual Doctype does not support updates."""
frappe.throw("Updating records is not supported for this Virtual Doctype.")
def delete(self):
"""Virtual Doctype does not support deleting records."""
frappe.throw("Deleting records is not supported for this Virtual Doctype.")
@staticmethod
def get_list(args):
"""Return a list of Form 470 records."""
data = Form470.fetch_data()
return [frappe._dict(doc) for doc in data.values()]
@staticmethod
def get_count(args):
"""Return the count of Form 470 records."""
data = Form470.fetch_data()
return len(data)
@staticmethod
def get_stats(args):
"""Stats function (not used here)."""
return {}
Hi @oguruma:
Has your Form470 data properties correspondence with doctype fieldnames?
Your data contains name property?
The REST API that the data comes from has a key called “application_number” in the JSON that is the UUID.
What I don’t understand is how to tell Frappe that the application_number should be used as the name for the Name, and how to get a single Document in the form_470.py controller file…
Follow this line by line, MongoDB Powered DocTypes in Frappe Framework | Frappe Blog
Don’t miss,
def load_from_db(self):
# ...
super(Document, self).__init__(record)
Hi @oguruma:
Check this sample from @buildwithhussain repo
See how the returned object is built.
Note that the previous as_list check is indented for Link Fields cases (needs this method too …)
Hope this helps.
Yes, this is needed for form!
def load_from_db(self):
"""Fetch a single Form 470 record from the USAC API using the application number."""
url = f"{Form470.BASE_URL}?application_number={self.name}"
try:
response = requests.get(url)
response.raise_for_status()
data = response.json()
if not data:
frappe.throw(f"Document {self.name} not found", frappe.DoesNotExistError)
# The API returns a list, so we extract the first item
record = data[0]
# Map API response fields to Frappe fields
data = {
"name": record.get("application_number"),
"form_nickname": record.get("form_nickname"),
"form_pdf": record.get("form_pdf", {}).get("url") if record.get("form_pdf") else None,
"service_request_id": record.get("service_request_id"),
"rfp_upload_date": record.get("rfp_upload_date"),
"fcc_form_470_status": record.get("fcc_form_470_status"),
"funding_year": record.get("funding_year"),
"billed_entity_name": record.get("billed_entity_name"),
"billed_entity_number": record.get("billed_entity_number"),
"billed_entity_city": record.get("billed_entity_city"),
"billed_entity_state": record.get("billed_entity_state"),
"billed_entity_zip": record.get("billed_entity_zip"),
"billed_entity_phone": record.get("billed_entity_phone"),
"contact_name": record.get("contact_name"),
"contact_email": record.get("contact_email"),
"authorized_person_name": record.get("authorized_person_name"),
"authorized_person_email": record.get("authorized_person_email"),
"service_category": record.get("service_category"),
"service_type": record.get("service_type"),
"function": record.get("function"),
}
self._record = record
super(Document, self).__init__(frappe._dict(data))
except requests.RequestException as e:
frappe.logger().error(f"Error fetching Form 470 data: {e}")
frappe.throw("Failed to fetch data from USAC API", frappe.ValidationError)
Gives me `### App Versions
{
"erpnext": "16.0.0-dev",
"erpnextcrmkit": "0.0.1",
"frappe": "16.0.0-dev",
"mspkit": "0.0.1",
"rangeldigital": "0.0.1"
}
Form/Form 470/240022562
Traceback (most recent call last):
File "apps/frappe/frappe/app.py", line 115, in application
response = frappe.api.handle(request)
File "apps/frappe/frappe/api/__init__.py", line 49, in handle
data = endpoint(**arguments)
File "apps/frappe/frappe/api/v1.py", line 36, in handle_rpc_call
return frappe.handler.handle()
~~~~~~~~~~~~~~~~~~~~~^^
File "apps/frappe/frappe/handler.py", line 50, in handle
data = execute_cmd(cmd)
File "apps/frappe/frappe/handler.py", line 86, in execute_cmd
return frappe.call(method, **frappe.form_dict)
~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/__init__.py", line 1700, in call
return fn(*args, **newargs)
File "apps/frappe/frappe/utils/typing_validations.py", line 32, in wrapper
return func(*args, **kwargs)
File "apps/frappe/frappe/desk/form/load.py", line 54, in getdoc
doc.add_seen()
~~~~~~~~~~~~^^
File "apps/frappe/frappe/model/document.py", line 1596, in add_seen
frappe.db.set_value(
~~~~~~~~~~~~~~~~~~~^
self.doctype, self.name, "_seen", json.dumps(_seen), update_modified=False
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
)
^
File "apps/frappe/frappe/database/database.py", line 959, in set_value
query.run(debug=debug)
~~~~~~~~~^^^^^^^^^^^^^
File "apps/frappe/frappe/query_builder/utils.py", line 84, in execute_query
result = frappe.db.sql(query, params, *args, **kwargs) # nosemgrep
File "apps/frappe/frappe/database/database.py", line 238, in sql
self._cursor.execute(query, values)
~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^
File "env/lib/python3.13/site-packages/pymysql/cursors.py", line 153, in execute
result = self._query(query)
File "env/lib/python3.13/site-packages/pymysql/cursors.py", line 322, in _query
conn.query(q)
~~~~~~~~~~^^^
File "env/lib/python3.13/site-packages/pymysql/connections.py", line 563, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
File "env/lib/python3.13/site-packages/pymysql/connections.py", line 825, in _read_query_result
result.read()
~~~~~~~~~~~^^
File "env/lib/python3.13/site-packages/pymysql/connections.py", line 1199, in read
first_packet = self.connection._read_packet()
File "env/lib/python3.13/site-packages/pymysql/connections.py", line 775, in _read_packet
packet.raise_for_error()
~~~~~~~~~~~~~~~~~~~~~~^^
File "env/lib/python3.13/site-packages/pymysql/protocol.py", line 219, in raise_for_error
err.raise_mysql_exception(self._data)
~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^
File "env/lib/python3.13/site-packages/pymysql/err.py", line 150, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1146, "Table '_7384140f3b03905f.tabform 470' doesn't exist")
{
"type": "GET",
"args": {
"doctype": "Form 470",
"name": "160002125"
},
"headers": {},
"error_handlers": {},
"url": "/api/method/frappe.desk.form.load.getdoc",
"request_id": null
}
{
"docinfo": {
"user_info": {},
"comments": [],
"shared": [],
"assignment_logs": [],
"attachment_logs": [],
"info_logs": [],
"like_logs": [],
"workflow_logs": [],
"doctype": "Form 470",
"name": "160002125",
"attachments": [],
"communications": [],
"automated_messages": [],
"versions": [],
"assignments": [],
"permissions": {
"select": 1,
"read": 1,
"write": 1,
"create": 1,
"delete": 1,
"submit": 0,
"cancel": 1,
"amend": 1,
"print": 1,
"email": 1,
"report": 1,
"import": 0,
"export": 1,
"share": 1
},
"views": [],
"energy_point_logs": [],
"additional_timeline_content": [],
"milestones": [],
"is_document_followed": null,
"tags": "",
"document_email": null,
"error_log_exists": null,
"webhook_request_log_log_exists": null
},
"exception": "pymysql.err.ProgrammingError: (1146, \"Table '_7384140f3b03905f.tabform 470' doesn't exist\")",
"exc_type": "ProgrammingError",
"_debug_messages": "[\"Error in query:\\n('UPDATE `tabForm 470` SET `_seen`=%(param1)s WHERE `name`=%(param2)s', {'param1': '[\\\"Administrator\\\"]', 'param2': '160002125'})\"]"
}
Yes, but I don’t even know what it does…
“Track seen” feature is not supported for Virtual DocTypes. On regular ones, a table field (_seen) is updated when the document is opened by the user but here there is not field to do it … You could create a method to manage this, but probably don’t make sense.
I set track_seen to 0, but I still only get the ‘ID’ in the list view by default…
If I set the various fields in the ‘List Settings’ then those fields will show up in list view, however it’s as if Frappe isn’t respecting my in_list_view settings in the form_470.json file.
“Track seen” was causing you can’t access to the form view of each document (load_from_db manage this). It’s working now?
Have you changed your get_list method? You should return data assigning api response “fields” to DocType fields, same way you did for load_from_db
Hope this helps.
