Field Integrity Exception - When trying to set Use Standard Price to True on Product via Dataloader

Hi all:
Scenario:
As an admin, I recieved a request to have all products within a particular Pricebook to utilize the Standard Price.

Issue:
When you try to update the USESTANDARDPRICE checkbox field in Salesforce via. Apex Dataloader for that product within that pricebook, you get an error:
"Field Integrity Exception"...

WHY???
Because when you try to do it thru Apex Dataloader, you have to update the price as well as updating that checkbox.

Picture it out:
I will walk thru the Manual Way and then thru Dataloader:

Manual Way:

1. Click on the Product Tab
2. Choose the Pricebook and click on Go

3. You will see a list of all your products under that pricebook.

4. If you select a product and Click on Edit, you can change the setting of that product to utilize the Standard Price and click save it will save. Which means everytime an user selects this product it will utilize the Standard Price automatically.

As you can see the List Price is now grayed out and the list price has changed or been overrides by the Standard Price once that is checked.



This done is the Manual Way but what happens if you have 1500 or more products then what...

Apex Dataloader way:

1. Export all the data from the PricebookEntry object for just the Standard Price Book.

a) Make sure you check off "Show all Salesforce objects"
b) Select Price Book Entry Object
c) Name this file "PricebookEntryStandardPriceBook.csv"
d) Click Next

 





 

2. Now time for our SOQL Query:
You can Select each field or copy and paste this SOQL query into the query box:

Select Id, Name, Pricebook2Id, Pricebook2.Name, Product2Id, Product2.Name, CurrencyIsoCode, UnitPrice, IsActive, UseStandardPrice FROM PricebookEntry where Pricebook2id='00B50000005ZRbX' and IsDeleted=False

NOTE:*****Pricebook2id='00B50000005ZRbX' <== This is my Standard Pricebook Id, you can get this id off from a report or Manage Pricebooks or thru the URL of the Pricebook record you are on.

Click on Finish and done exporting out all Standard Pricebook.

Now we need to Repeat these steps for the Pricebook you would need to update.
Remember to name the CSV file a different name
and Update the Pricebook2Id within the SOQL

Now, you can do this in access and run a query to update the UnitPrice to the pricebook affected from the standard pricebook or use Excel via VLookup..

What needs to be done is:
 1. Open your StandardPricebook csv
 2. Open your "Otherpricebook" csv
 3. Update the UnitPrice on the "Otherpricebook" to match the StandardPricebook Unit Price and Set the UseStandardPrice checkbox to True on the products that need to be updated.
4. Once done and you are comfortable, then it ready to Update via. Apex Dataloader.
5. The mapping of fields would be similar to this screenshot:

a) Make sure you check off "Show all Salesforce objects"
b) Select Price Book Entry Object
c) Make sure you select the correct file to "Update"













This is the way to update UseStandardPrice checkbox via Dataloader.

Hope this helps
Zishan R.

How to use 'NOT LIKE' in SALESFORCE SOQL

Hi everyone:
    Business Scenario:
        They would like something update where a field on the record is NOT LIKE "adviser." Or in simplest terms does not contain "Adviser".

Solution for developers in APEX:

APEX will not allow someone to write a simple query such as:
SELECT Name From ABC where id=:Ownerid and Name NOT Like '%adviser%';

So solution is simple:
SELECT NAME From ABC where id=:Ownerid and (NOT Name Like  '%adviser%');

If you are utilizing GROUP By statements here is an example of one:

 AggregateResult[] groupedResults = 

         [SELECT Ownerid,Count(Id) ce 
         FROM Opportunity Where 
         Withdrawn__c = True 
         AND StageName = 'Closed Won' 
         AND Withdrawal_Status__c ='Withdrawn' 
         AND Withdrawn_Date__c = THIS_MONTH  
         AND Ownerid=:id 
         AND (NOT Name Like '%COMP%')
         GROUP BY Ownerid];  

Hope this Helps.

Thanks

Zishan

FLOOR Function in Salesforce Alternative Way

Hi everyone:
    Business Scenario:
       The business would like a formula field that will calculate a number to the nearest lowest 10.
Which means:
If the number is 129 the new number would be 120
If the number is 64 the new number would be 60.

The issue:
   In Salesforce, FLOOR Functionality does not work the same way in Excel.
In Excel you would do something like this to get the top results:
=FLOOR(129,10) which equals 120

In Salesforce, within a formula field it would be:
FLOOR(129) which equals 129, does not work. Do not know why, when Salesforce states the following:
"Returns a number rounded down to the nearest integer."
  

Solution:
   Navigate to the Object that you are placing this formula field in: For me its:
Setup | Create | Object | Test | New Field

Click on Formula Attribute type and Click Next.
For data type click on Number, no decimal if you want you can have it.

The field I will utilize is an existing field within my formula that is a Number(18,0) field already named Net_Enrollment__c.

This is my final formula for the new field I created named Level__c:
IF(Net_Enrollment__c <1 1="1" et_enrollment__c="et_enrollment__c" span="span">

Breakdown of the formula:
I utilize If then statement to conditionalize it to work properly (you do not have to)
I start with:

IF(Net_Enrollment__c<1 0="0" 1="1" be="be" do="do" end="end" greater="greater" if="if" is="is" it="it" math="math" means="means" net_enrollment="net_enrollment" result="result" should="should" span="span" the="the" then="then" which="which" zero="zero">

FLOOR(Net_Enrollment__c) <== which returns me my number


VALUE(RIGHT(TEXT(Net_Enrollment__c), 1) <== Reasons of utilizing TEXT functionality and RIGHT Function

RIGHT Function only works on TEXT and not number data types. 
So I had to convert the Net_Enrollment__c field into a TEXT and then utilize the RIGHT function to get me the Last Digit in my Net Enrollment number and VALUE function returns it to a number

So if I have 129, by using the formula
VALUE(RIGHT(TEXT(129), 1) would give me "9"

So then I take the whole number 129 - 9 = 120
Which is
FLOOR(129)-VALUE(RIGHT(TEXT(129), 1))


Hope this helps and remember always think outside the box.

Thanks
Zishan

 

SLA - Service Level Agreement Case Times

Hi:
   Scenario:
     Business wants to know the following:

1. How many hours a case has been open?
2. How many days a case been open?

All for SLA - Service Level Agreement amongst the business units who you support.

Solution:
We can utilize many methods, I will list out number of hours a case been open and number of days a case been open.

"DATEVALUE" <== a formula method that "Creates a date from its datetime or text representation"

To get the number of hours a case been open, we will not utilize "DateValue" but just common mathematical formula.
Let's say the 2 fields we are utilizing are CreatedDate and LastModifiedDate

Knowing there are 24 hours in a day worldwide... lets use that:

(24* (LastModifiedDate - CreatedDate  ))

 This formula will give you the number of hours between the 2 fields.

The Number of Days a Case been open:

Let's say you are utilizing a formula field which based on the type of case that comes in counts off the number of days SLA should meet for that case.

Lets say for "User Profile Change" type of case that comes in, the number of days to get back to the user is 1 day.

Field: SLA Expectations | SLA_Expectations__c | Attribute - Number (2,0) | Formula

Formula is:
IF(Type='User Profile Change',1,0)

Now we will use that field to figure out if the SLA was met or was it over 1 day.

Create another formula field named: SLA | SLA__c | Attribute - Number (2,0)

Formula is:
(DATEVALUE (CreatedDate) + SLA_Expectations__c) - TODAY()

This shall give you the result you need to figure out SLA which you can then set up an escalation rule to send out an email to the manager of operations let say that SLA was not met.

Great for reporting purposes as well :)

Thank you everyone
Hope this helped
Zishan

Change Password via Developer Console in Salesforce without resetting password

Scenario:
      We need to reset a password or change the password on an user utilizing a 3rd party app in salesforce. This user is there for the app only and not really a physical user in the instance. The email for this user is a person who has left the company or is on vacation but you need the app to work.

Solution:
    You can do this 2 ways:

1. Change the Email of the user in question
2. Click on Generate Password and click save.

But lets say you can not change the Email of the user because you should not be the owner of the app or you are not part of the app as an admin. Here is where you change using Developer Console.

Go to Setup and Drop it down to Developer Console or sometimes called the System Log




Within the "Logs" tab you will see a button called "Execute".


Click on the text box or white space next to it.

It shall open up another window which is titled "Enter Apex Code"

Place in the following SOQL query:
User usr = [select Id from User where username='appuser@myzc.com'];
System.setPassword(usr.Id,'welcome1234');

Then click on Execute.
It should bring up a success window and you should be good to go.
Now you may need to log in and make sure everything is ok.

Hope this helps... I am not responsible for anyone utilizing this method... It can be a risk... I would check before you do this task.
Thanks
Zishan R.

Watch Me Fight 
Join My Facebook on Cage Assassin Fight Gear 

Like my fan page Cage Assassin Fight Gear 

Dynamically generate the logged in user's id Syntax in Salesforce.com

Scenario:
Place on the home page sales history information by user for only that user.

Tools needed:
Apex Class/Trigger/Controller
VisualForce

I will go into the code part later. But to get the id of the user dynamically and have your VF page component added onto your Home Page Layout working. In your SOQL query under your Apex Controller/Class you must use the following:

UserInfo.getUserId();

So in SOQL, in Apex code you can do:
Select Id, Owner.Id, Name, Site from Account where Owner.Id=:UserInfo.getUserId();

I hope this helps....
Thanks
Check out my Other Blogs:
VisualForce Made Easy
Data Migration Made Easy

IE (Internet Explorer) Issue for Salesforce.com?

Hi Everyone:
I had an issue where I wrote a simple trigger to update Contact Record off an Opportunity. Everything worked out with a test rate of 90%, then deployed into the production from sandbox.
But then realized that it worked for some people and some people it did not.
So a couple of test I did was:

1. Check my code
2. Check the Profile
3. Login as the user and see if I re-create the same error message.

Well I logged in as the user, the trigger worked in every aspect... So I knew there was maybe a IE issue since the user kept using IE and did not want to use Mozilla.
I contacted Salesforce Support and there great representative told me what I needed to do to get it working for that person...See Below:

"Please have your user implement the following solution on his computer and test the trigger again.

Here is a guide to browser settings on Microsoft Internet Explorer 6 and 7 that can help users who are having trouble.

1. From the browser menu, click Help, then choose About Internet Explorer. Ensure that the cipher strength is 128 bit or higher. If it is not, go to www.microsoft.com/windows/ie and upgrade to a 128 bit version.

2. Next, click the Tools menu, then select Internet Options.

For IE 6:
3. On the General tab, locate the Temporary Internet Files section and click the Settings button. Select the "Every visit to the page" radio button and ensure there is enough disk space for the cache. The ideal cache size is between 20 and 50 megabytes (MB). (Make sure your computer has enough disk space on its hard drive.) When you are finished, click the OK button.

4. In the same Temporary Internet Files section of the General tab, click the Delete Cookies button and Delete Files button.

For IE 7:
3. On the General tab, locate the Browsing history section and click the Settings button. Select the "Every time I visit the webpage" radio button and ensure there is enough disk space for the cache. The ideal cache size is between 20 and 50 megabytes (MB). (Make sure your computer has enough disk space on its hard drive.) When you are finished, click the OK button.

4. In the same Browsing history section of the General tab, click the Delete button. In the Delete Browsing History window that appears, locate the Temporary Internet Files section and click the Delete Files button. Then locate the Cookies section and click the Delete cookies button. When you are finished, click the Close button

********

5. Click the Privacy tab. In the Web Sites area or using the Sites button click the Edit button. In the Address of Web site text box, enter "https://*.salesforce.com" (without the quotation marks) and click the Allow button. When you are finished, click the OK button.

6. Click the Advanced tab. Scroll to the bottom of the Settings box. The "Do not save encrypted pages to disk" check box should NOT be selected.

7. Also on the Advanced tab, the "Empty Temporary Internet Files Folder when browser is closed" check box should be selected, as well as the "Use SSL 2.0" and "Use SSL 3.0" check boxes.

8. Click the Security tab. Click the "Internet" icon (represented by a globe graphic). Click the Custom Level button. Scroll almost to the bottom of the Settings box to "Active Scripting". Ensure that the Enabled option is selected. Now scroll to the option that says "Display Mixed Content" and make sure this is set to Enabled. When you are finished, click the OK button.

9. Also on the Security tab, click the "Trusted Sites" icon (represented by a check mark graphic). Click the Sites button. In the "Add this Web site to the zone" text box, enter "https://*.salesforce.com" (without the quotation marks) and click the Add button. When you are finished, click the OK button.

10. With the "Trusted Sites" icon still selected, click the Custom Level button. Scroll to the option that says "Display Mixed Content" and make sure this is set to Enabled.

11. Click the "OK" button.

12. Close all open browser windows, re-launch Internet Explorer, and login to salesforce.com.

NOTE: If problems persist and you know you are connecting to the Internet using a Proxy server, you can also make the following change to your settings.

1. Click the Tools menu, and select Internet Options.

2. Click the Advanced tab, and scroll approximately half-way down the Settings box.

3. Select the "Use http1.1 through proxy connections" check box." From Salesforce.com Premier Support.

Thanks I hope this helps out anyone with this issue.
Thanks
View my Other Blogs:
VisualForce Made Easy

Salesforce Data Migration Made Easy

Tips and Tricks About AJAX TOOLS in Salesforce.com

Hi all:
Today, I want to give tips and tricks about AJAX TOOLS in salesforce.com. But you need a couple things first:

• If you have an salesforce.com org set up then download the ajax toolkit .
  
• Click on Get it Now.
  
• Go to the process, it will ask where would you like to place it, using your developer account, company's or sandbox username and password.
• Once all of the steps are done and executed onto the org you decided.
• This will create an app onto your org called AJAX TOOLS. Look at Picture
  
• 
• 
  
• The best place to run AJAX is in Mozilla Firefox (If you do not have Mozilla then download here). With IE it does not work properly.
• Now once we are in AJAX, Click on Start Ajax Tools - This will open up a new window and first thing you will see is all of your S-Controls and Underneath that your Apex Classes, and Visual Force Pages.
• You will see many icons on top going from Left to Right Across. See Picture Below
• 
• Save, Run, New, Backup and Config On the Left hand side of the Menu - Is used to save your S-Controls, Apex Classes
• On the Right hand Side These are different set of menus that open up a new world.
• Shell:

1. You Can place your Javascript code to see if it runs correctly
2. Can place in APEX Code, SOSL Statement or your SOQL statement
3. To Make it run just hit enter...

• Samples:

1. Cool way to learn Javascript and correct SOQL Statements.

• Explorer:

1. This is the best tool of them all on this AJAX TOOLKIT.
   
2. One can create queries and relationships just by selecting tables fields from the Parent Table.
3. For example, Dropdown Account Where it states SObject.
4. Next you will see all the fields come up PLUS all the relationship Tables that go along with the Account. AMAZING.
5. You can do many things with the query statement you produce out of here.
6. You can create your S-Control and then make the tweeqs that you need to make it work. All you have to do is click on Generate Sample. This will generate the code needed for your S-Control on that object. See Picture Below

I hope this help anyone out... Let me know...
Thanks
View my Other Blogs:
VisualForce Made Easy

Salesforce Data Migration Made Easy

• 

Too many SOQL queries: 21 Error in Force.com

Hi and Welcome:
Basically this error defines that your code went over the governing level of salesforce. Basically, this happens when you are uploading data through an web service api or data loader.

Just remember you will have to keep your batch size low due to the trigger governor limits (soql statements). A batch of 20 is just fine, or less.

So an example:
Scenario:

1. Upload through a 3rd party tool to salesforce from sql server into the product and pricebookentry table.
2. This is to update the existing products into salesforce. (NOT AN INSERT BUT AN UPDATE)
3. The trigger will update the account making the IsActive(Price) Active on the current Pricebook which is Active not Standard Pricebook.
   

Bad Trigger:

trigger updateitafter on Product2 (after update) {

List toUpdate = new List();
for(Product2 p:Trigger.new){

Product2 existingProduct= Trigger.oldMap.get(p.Id);
//The SOQL statement below will give you an error
//because of the for loop..
for (PriceBookEntry li:[Select UnitPrice, Product2Id,
Pricebook2Id, Id From PricebookEntry
where Product2Id=:existingProduct.Id
and IsActive=false and UnitPrice!=0.00])
{
li.IsActive=true;
toUpdate.add(li);
}

update toUpdate;
}
}

The right way to do the trigger:
trigger updateitafter on Product2 (after update) {
List allids = new List();

List toUpdate = new List();
for(Product2 p:Trigger.new){

Product2 existingProduct= Trigger.oldMap.get(p.Id);
allids.add(existingProduct);
}

for(PriceBookEntry li:[Select UnitPrice,
Product2Id, Pricebook2Id, Id
From PricebookEntry where
Product2Id In :allids
and IsActive=false
and UnitPrice!=0.00])
{
li.IsActive=true;
toUpdate.add(li);
}
update toUpdate;
}

The Change:

1. In apex or any database-centric Programming, a query should not be executed inside the loop.
2. So getting all the ProductId's first and placing it into an array(list) then using that list placing it back into the query just by an IN statement makes it easy and within limits of salesforce.
   
3. This would do two things:
1. Execute a single query and loop through its results.
   
2. Also, efficient coding and clean.

Hope this was helpful.
Thanks
View my Other Blogs:
VisualForce Made Easy

Salesforce Data Migration Made Easy