Auto Delete Item Price

Hi How to create a script to delete the item price for the pricelist Product Cost if its already expire

thanks in advance

@xchicox , Create server script,

Script Type : Schedule Event
Event Frequency : Daily

item_price = frappe.db.sql("""select name, valid_upto from `tabItem Price` where DATEDIFF(valid_upto,CURRENT_DATE) < 1""", as_list=1)
for row in item_price:
        frappe.delete_doc("Item Price", row[0])

Thanks

Thank you so much, but what if i want this only to specific pricelist named Product Cost , because i have three pricelist

@xchicox , Apply a filter in the where clause.

select name, valid_upto from `tabItem Price` where price_list in ('Retails', 'Wholesale')  and DATEDIFF(valid_upto,CURRENT_DATE) < 1"""

Thanks.

Can we use frappe.db.sql in the server script?

@TurkerTunali , Yes. I tried and it worked.

Interesting. It’s a little bit scary and a little but usefull. But I think SQL must be used in code not in any script.

Because I’ve written so many codes which I forgot to add a “WHERE” condition to the “DELETE” statement :grimacing: