Journal Account Table Bulk Update

ERPNext Accounting
1

Good Day,

I have had a bunch of Journal Entry transactions that I have posted and I would like to perform an update on the party and party_type columns of the Journal Account Table

I tried using the Bulk Update but it doesn’t allow for the update to proceed because it can’t find a specific value (although it is there !)

I would like to change the party_type from Employee to Supplier and the party from HR-EMP-00001 to Income Tax Authority

image
image906×734 26 KB

If there is any way to access the database I would like to run this update statement (These are just 21 records that need to be updated) or to do it via Bulk Update ?

I am using ERPNext 13 hosted on erpnext.com

UPDATE 
`tabjournal entry account` ja  
SET 
  ja.party = 'Income Tax Authority' , ja.party_type = 'Supplier'
WHERE ja.party_type = 'Employee' AND ja.party = 'HR-EMP-00001' AND ja.account = '21302 - Employee Income Tax Payable - noga';
2

Hi,

You can run SQL from the system console. In the Awesome Bar search System Console:

image

In console type your update query and don’t forget to tick on commit check box and execute.

image
image3645×1677 215 KB

Thanks,

3

Good Day,

Thanks for your kind reply, but it is not working at all.

I searched the documentation and it is clear that the Console doesn’t accept native SQL, it has to be invoked using python code.

4

Changed the Code into Python Code to invoke the frappe.db.sql function

This is the Code

image
image710×416 14.7 KB

5

Here is the Output

image
image872×288 10.4 KB

6

first, check the data, if you are version 13 then it will not show the

data = frappe.db.get_list('Task', {'party_type': 'Employee',
         'party': 'HR-EMP-00001', 'account': '21302 - Employee Income Tax Payable - noga'},
        ['party', 'party_type'])
log(data)

then check on [commit] and run the below command
(if you are on version 13 then it will not show the output. to check run the above command again)

try = frappe.db.set_value('Task', {'party_type': 'Employee',
         'party': 'HR-EMP-00001', 'account': '21302 - Employee Income Tax Payable - noga'},
        {'party': 'Income Tax Authority', 'party_type': 'Supplier'})
log(try)

Thanks

Suresh