Script to Automate Price Lists

ERPNext
,
1

Hi Folks,

I was hoping someone might be able to help me; I’m new to erpnext! I’m also using a frappe hosted account so don’t have direct access to the code/database.

Objective
Copy all prices from the Standard Buying Price List to the Standard Selling Price List, with a standard margin. Maintaining multiple price lists is a high admin burden and pricing rules don’t seem to be effective to achieve what’s needed.

Approach

  1. Create Server Script
  2. Delete existing records from Selling Price List, using SQL statement
  3. Copy all items and prices (with standard markup) from Buying Price List, using SQL statement

Problem

  1. I get this error message: ‘You do not have enough permissions to access this resource. Please contact your manager to get access.’ (Note: I have added server_script_enabled to site config).

Ask

  1. Is there a better way to achieve what I’m trying to do?
  2. How can I resolve my permissions issues?

Thanks!!

The script I’m trying to test/run is:

frappe.db.sql(
	    """DELETE * FROM `tabItem Price` where price_list=%s""",
	    "Standard Selling",
	)

frappe.db.sql(
	"""update `tabItem Price` set price_list_rate=p.new_price, modified=NOW() where price_list="Standard Selling" AND item_code=p.item_code FROM (SELECT * FROM `tabItem Price`)p"""
	)
2

Hi there,
Server Scripts use a limited subset of python to ensure server security. You can see the list of what’s available for use here:
https://frappeframework.com/docs/v13/user/en/desk/scripting/script-api

In this case, frappe.db.sql is indeed available, but only for SELECT commands. To delete docs, use the ORM method frappe.delete_doc.

Alternately, you can use any methods you’d like in a custom app, which can be installed to a frappe cloud bench.

1 Like
3

Thanks @peterg - that makes sense. I’ll play with the ORM method and share on the thread if I’m able to solve it :slight_smile: