Tuesday, February 16, 2010

Is There Junk in My Salesforce Pricebooks?

I had an interesting problem today: Find products in my Salesforce pricebooks that we have never sold.. In my case I want to delete these products from Salesforce (that's another post).

Finding the products was easy. I wanted all products where:

  • No Opportunity referenced the product.
  • No Asset referenced the product

Here is the script:

import SQLForce

session = SQLForce.Session("sandbox")

for product in session.select("SELECT id,name, division__c FROM Product2 ORDER BY name asc"):

    nOps = session.select("SELECT COUNT() FROM OpportunityLineItem WHERE PriceBookEntry.product2Id ='" + product[0]+ "'")[0][0]

    nAssets = session.select("SELECT COUNT() FROM Asset WHERE product2Id='" + product[0]+ "'")[0][0]

    pricebooks = []

    for pb in session.select("SELECT DISTINCT Pricebook2Id FROM PricebookEntry WHERE Product2Id='" + product[0]+ "'"):

    if "0"==nOps and "0"==nAssets:
        notUsed = [product[0],product[2],product[1], nOps, nAssets, "\t".join(pricebooks)  ]
        print "\t".join(notUsed)

I put the results of the script into an Excel worksheet so marketing can look for mistakes. Later I will modify the script to delete the records I found.

No comments: