Wednesday, February 19, 2014

Salesforce: Revenue Forecast error, duplicate value found: unknown duplicates value on record with id: unknown

This blog is a continuation of a blog I wrote earlier batch upload Quota in Forecasts. Some people may see following error duplicate value found: <unknown> duplicates value on record with id: <unknown> in some of the record after insert into RevenueForecast object.

Here is my finding:
  1. Each user only allow to have 1 Quota in 1 Forecast Period
  2. The key here is OWNERID + STARTDATE
  3. Salesforce will reject if we create new record of Revenue Forecast with the duplicate key (OWNERID + STARTDATE) and throw error message duplicate value found: <unknown> duplicates value on record with id: <unknown>
Example: your company set the forecast period quarterly. But, when you want to load quota for next quarter and you believe no one has load it before, but you get duplicate value found: <unknown> duplicates value on record with id: <unknown> error for a few users. What does it mean? It mean a record of quota has been created for those users in the quarter.

It doesn't mean someone has load it before you, although it maybe. Here is the permission related to load quota:
  • Edit Personal Quota, user with this permission able to set their own quota from: My Settings | Personal | Advanced User Details, then scroll down to Quota related list.
  • If user also have permission Manage Users, this user able to set quota for other user, include using Data Loader or other API tool if the have API Enabled permission.
If user go to a quota related list in user detail page, then change Range Length, example: by default it only show 3 periods and user click the dropdown and select 4 periods, Salesforce will create new quota record with 0 value.

Here is the illustration, initially:

User select 4 periods in Range Length:

Salesforce will create new record for FQ4 2014 with 0 value in the background. This is the most caused admin fail to load quota for new period because they already exist.

  1. Before you load quota using Data Loader, which is insert to RevenueForecast, query to the RevenueForecast on particular period you are working on, filter by StartDate, example: Select r.Id, r.OwnerId, r.StartDate, r.CurrencyIsoCode, r.Quota from RevenueForecast r where r.StartDate = 2014-01-01
  2. If you find any result, use it to split data in CSV file you will use to insert into 2 CSV file: one for insert and one for update.
  3. Only insert to RevenueForecast for data you not found in query above.
  4. For data found in query above, update the RevenueForecast based on Id in above query.