UNPIVOT: Columns to rows

January 26, 2012

Q : Write a query to format the columns of a table to rows?
A : Lets say there is a table 

    SquadID INT, 
    Date DATETIME, 
    Q01 VARCHAR(10), 
    Q02 VARCHAR(10), 
    Q03 VARCHAR(10), 
    A01 VARCHAR(10), 
    A02 VARCHAR(10),     
    A03 VARCHAR(10))

INSERT INTO @t(SquadID, Date,  Q01, Q02, Q03, A01, A02, A03) SELECT 123,'2008-09-19','5.1','2.1','3.0','DEG','ABC','CDE'
I want a query to display results as below
Q1 A1
Q2 A2
Q3 A3

This is not straight forward we can use UNPIVOT to achieve this to some extend.  And, where clause does the trick. I found this on Internet. 

select ROW_NUMBER() OVER(ORDER BY Question_ID), Question, Answer FROM 
(SELECT Q01,Q02,Q03,A01,A02,A03 FROM @t) p
   (Question FOR Question_ID IN 
)AS up
   ( Answer FOR Answer_ID IN 
)AS an
where RIGHT(Answer_ID,1)=RIGHT(Question_ID,1);


How to package a single workflow using Force.com Migration tool?

January 26, 2012

Extracting a single workflow out of force.com can be a little tricky. I am using ANT/JAVA migration tool to extract force.com metadata. From the documentation it is clear that 'Workflow' tag should be used to extract workflow.


The above component in project manifest - package.xml will fetch all the workflows related to Opportunity. How to fetch a specific workflow? dot operator will not work in this case. One way to get around this limitation is specifying the following components related to a workflow as appropriate.
  • WorkflowAlert
  • WorkflowFieldUpdate
  • WorkflowOutboundMessage
  • WorkflowRule
  • WorkflowTask
For example, a workflow sends an email and does a field update upon meeting a certain criterion. In package.xml, the following components should be included. 

<members>Opportunity.Set_Date</members><name>WorkflowFieldUpdate</name></types><types><members>Opportunity.Request Email</members><name>WorkflowRule</name>
</types> <types> 

Make sure we don't miss any alert, field update, task or outbound message coupled with a rule. Upon packaging, we get the right metadata. And, upon deployment we see the workflow in the target environment. 

Email Templates

Accessing cross object merge fields in Salesforce

January 10, 2012

At present accessing cross object merge fields in email templates is not possible. You need to create a formula field pointing to the related object's custom field. Also if the related object's field's type is of "Long Text", it won't show up in advanced formula editor while creating the field. We should choose alternative type Text Area. 



Prominent limitation of time based workflow for batch processing

January 09, 2012

I was going through the following link about time based workflows. My aim was to avoid governor limits encountered for a class implementing Apex Schedulable interface. For a while time based workflow seems to be an alternative. 

Time based workflow queues its tasks. If the tasks are above the limit, it executes the left over tasks in the current run in the next hourly run. But, the following limitation may make the time based workflow unpredictable.

Time-dependent actions aren't executed independently. They're processed several times every hour, where they're grouped together and executed as a single batch. Therefore, any triggers that fire as a result of those grouped actions are also executed in a single batch. This behavior can cause you to exceed your Apex governor limits if you design your time-based workflow in conjunction with Apex triggers.

Finally, Salesforce will run all the queued jobs as a batch job on an hourly basis. Below is another blog post on the same issue.

At this point in time Schedulable interface appears to be better than time based workflow. We can use Limits Apex method to gracefully exit the batch job. As promised in the above blog if time based workflow is made to run for every 5 minutes, it may have edge over Schedulable interface. 

Popular Posts