Database Management

[erp05AI]

Hello.
I have a master data table called Transaction. The columns are custid, itemid, date, amount, payment type.
How do I break down/split the Transaction table where the customerid and itemid with paymenttype won’t be repeated or duplicated.

[brian_pond]

With only SQL syntax, you’d create a new, unique index. Like so:

CREATE UNIQUE INDEX my_new_index_name 
USING BTREE ON `tabTransaction` (customerid, itemid, paymenttype);

However with Frappe Framework there is an (undocumented) way of creating composite indexes. You do this by opening your DocType’s main Python file, and adding a function named on_doctype_update

Here’s an example from official ERPNext code, showing how 4 different indexes are defined:

frappe/erpnext/blob/7f261f34483aa3f8ebb38e55f480cc7d80d655c9/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py#L350

The tricky part is this: after you’ve written your code, how do you tell Frappe Framework to go create the SQL index you defined? Here are 2 ways:

1. Just call the on_doctype_update() function using 'bench console' or 'bench execute'.

–or–

2. In your web browser, edit your DocType. Make some kind of change, any change, so that the “Save” button is activated. (I sometimes just alter my DocType’s description field, and add a period or space to it.). Then when you click “Save”, the web page automatically calls the on_doctype_update() and synchronizes your SQL indexes.

[erp05AI]

Hi the case above is not relate to erpnext but other framework like database for spring boot. But anyways thanks for the info.