Repository: frappe/erpnext
Pull request: fix: Migrate old lead notes as per the new format
fixes #31800

Problem statement:
notes for leads created in V13 generate exception after upgrade to V14  
### Information about bug

All leads created with the version V13  containing a note generate a exception after the upgrade to V14 and are inaccessible.
New Leads work fine but the Notes field is missing in the frontend even if in the tabLead table there's the "notes" column filled with the original note.

As I can see Notes have a dedicated tab in V14 but all the notes in the V13  have not been converted in the new format.

Thanks for the support.

### Module

CRM

### Version

Frappe Version 14.0.0
ERPNext Version 14.0.0

### Installation method

docker

### Relevant log output / Stack trace / Full Error Message.

```shell
### App Versions

{
	"erpnext": "14.0.0",
	"frappe": "14.0.0"
}
```
### Route
```
Form/Lead/CRM-LEAD-2022-00073
```
### Trackeback
```
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 54, in handle
    return frappe.handler.handle()
  File "apps/frappe/frappe/handler.py", line 45, in handle
    data = execute_cmd(cmd)
  File "apps/frappe/frappe/handler.py", line 83, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "apps/frappe/frappe/__init__.py", line 1581, in call
    return fn(*args, **newargs)
  File "apps/frappe/frappe/desk/form/load.py", line 37, in getdoc
    doc = frappe.get_doc(doctype, name)
  File "apps/frappe/frappe/__init__.py", line 1172, in get_doc
    doc = frappe.model.document.get_doc(*args, **kwargs)
  File "apps/frappe/frappe/model/document.py", line 73, in get_doc
    return controller(*args, **kwargs)
  File "apps/erpnext/erpnext/controllers/accounts_controller.py", line 80, in __init__
    super(AccountsController, self).__init__(*args, **kwargs)
  File "apps/frappe/frappe/model/document.py", line 105, in __init__
    self.load_from_db()
  File "apps/frappe/frappe/model/document.py", line 151, in load_from_db
    super().__init__(d)
  File "apps/frappe/frappe/model/base_document.py", line 109, in __init__
    self.update(d)
  File "apps/frappe/frappe/model/base_document.py", line 156, in update
    self.set(key, value)
  File "apps/frappe/frappe/model/base_document.py", line 208, in set
    self.extend(key, value)
  File "apps/frappe/frappe/model/base_document.py", line 249, in extend
    self.append(key, v)
  File "apps/frappe/frappe/model/base_document.py", line 234, in append
    value = self._init_child(value, key)
  File "apps/frappe/frappe/model/base_document.py", line 262, in _init_child
    value["doctype"] = doctype
TypeError: 'str' object does not support item assignment

```
### Request Data
```
{
	"type": "GET",
	"args": {
		"doctype": "Lead",
		"name": "CRM-LEAD-2022-00734"
	},
	"headers": {},
	"error_handlers": {},
	"url": "/api/method/frappe.desk.form.load.getdoc"
}
```
### Response Data
```
{
	"exception": "TypeError: 'str' object does not support item assignment"
}
```
```

Hints from issue discussion:
It looks like even though the `notes` field was changed from `Text Editor` to `Table` in https://github.com/frappe/erpnext/pull/31311, it wasn't patched.

Commit message: perf: lesser SQL queries and no validation

Co-authored-by: Sagar Vora <sagar@resilient.tech>

Patch to review:
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 4729add16b38..f48b2a1bb0af 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -307,6 +307,7 @@ erpnext.patches.v13_0.job_card_status_on_hold
 erpnext.patches.v14_0.copy_is_subcontracted_value_to_is_old_subcontracting_flow
 erpnext.patches.v14_0.migrate_gl_to_payment_ledger
 erpnext.patches.v14_0.crm_ux_cleanup
+erpnext.patches.v14_0.migrate_existing_lead_notes_as_per_the_new_format
 erpnext.patches.v14_0.remove_india_localisation # 14-07-2022
 erpnext.patches.v13_0.fix_number_and_frequency_for_monthly_depreciation
 erpnext.patches.v14_0.remove_hr_and_payroll_modules # 20-07-2022
diff --git a/erpnext/patches/v14_0/migrate_existing_lead_notes_as_per_the_new_format.py b/erpnext/patches/v14_0/migrate_existing_lead_notes_as_per_the_new_format.py
new file mode 100644
index 000000000000..6ba5a9ff2143
--- /dev/null
+++ b/erpnext/patches/v14_0/migrate_existing_lead_notes_as_per_the_new_format.py
@@ -0,0 +1,21 @@
+import frappe
+from frappe.utils import cstr, strip_html
+
+
+def execute():
+	for doctype in ("Lead", "Prospect"):
+		if not frappe.db.has_column(doctype, "notes"):
+			continue
+
+		dt = frappe.qb.DocType(doctype)
+		records = (
+			frappe.qb.from_(dt)
+			.select(dt.name, dt.notes, dt.modified_by, dt.modified)
+			.where(dt.notes.isnotnull() & dt.notes != "")
+		).run()
+
+		for d in records:
+			if strip_html(cstr(d.notes)).strip():
+				doc = frappe.get_doc(doctype, d.name)
+				doc.append("notes", {"note": d.notes, "added_by": d.modified_by, "added_on": d.modified})
+				doc.update_child_table("notes")


Code review comments:
diff_hunk: @@ -0,0 +1,21 @@
+import frappe
+from frappe.utils import cstr, strip_html
+
+
+def execute():
+	for doctype in ("Lead", "Prospect"):
path: erpnext/patches/v14_0/migrate_existing_lead_notes_as_per_the_new_format.py
text: @user1:
Maybe add `Opportunity` too? There is no harm. If user had custom field, it will get migrated. If not, will continue after `frappe.db.has_column`

```suggestion
	for doctype in ("Lead", "Prospect", "Opportunity"):
```