Category Archives: No category

Trying to resolve the Travelling Salesman Problem in Salesforce

Below is a screen showing one attempt at finding the best route to visit several accounts using the Artificial Bee Colony algorithm. The best route is defined as the route with minimal total sum of the distances between accounts. In other words, the route to spent the least amount of time and gas.

I am not sure anyone has ever had that problem to resolve in a Salesforce application but I found it was a good way to test my implementation of the Artificial Bee Colony (ABC) optimization algorithm, which could be used in many other optimization problems.

This implementation offers a fairly good approximation of the best route – definitely not the best route as you can see below:  you would go from Plano to Richardson before Garland and then from Garland to Mesquite to save a few miles/gallons.

Travelling Salesman Problem in the DFW

Travelling Salesman Problem in the DFW

The implementation is split into 2 main classes:  the Hive, which implements the ABC algorithm, and the TravellingSalesmanProblem class, which is in charge of storing the accounts latitude/longitude, calculate the distances between accounts and getting the next best route.

Another interesting part is the Javascript code that uses the Google Map API to plot the route.

Plotting using the Google Map API

Plotting using the Google Map API

Other potential applications of the Travelling Salesman Problem could be:

  • deciding the different routes each truck should follow in a delivery/courier company fleet
  • wiring together memory elements in digital circuits in a sequence to minimize length of wire
  • finding a sequence of advertising spots where ads from one company would not be shown immediately after its competitor (say, keep a Coke ad away from an Pepsi ad)
  • in robotic welding, find a good sequence of tasks and path for the robot arm to weld the seams faster
  • likewise, find the optimum path for a CNC machine to drill multiple holes
Advertisements

How to implement edit locking

When 2 users start editing the same record, the first user who saves the record wins, and the other user will lose the changes with an error message similar to “The record you were editing was modified by *someone* during your edit session. Please re-display the record before editing again.”

This can be an inconvenience:  if an integration process updates the records, any user who opened the record for editing will lose his changes if he/she doesn’t hit Save quick enough.

To lock the record when the first user opens it for editing, you can implement the following 4 steps:

  1. Create a date/time field “Last Edit Date Time” – this field will store the date and time when the record was opened for editing.
  2. Create a validation rule to prevent any updates to the record during the period when it is locked (a period of 5 minutes so that no one can keep the record locked indefinitely).
  3. Create a Javascript “Edit” button that updates the “Last Edit Date Time” field and redirects to the record editing page.
  4. Replace the standard Edit button on the page layout with the custom Edit button.

First we create the field (this example uses Accounts):

Screen Shot 2014-12-30 at 6.29.17 PM

Then we create the validation rule as follows:

AND( NOW() – PRIORVALUE( Last_Edit_Date_Time__c ) < ( 5/1440 )
/* locks for 5 minutes */
, PRIORVALUE( LastModifiedById ) != $User.Id )

The first line in this validation rule calculates how long since the last time the record was locked and compares the number to 5/1440 (5 minutes divided by the number of minutes in a day).

If it is less than 5 minutes, then, on the second line, it checks whether the user doing the update is the same user that set the edit lock.

If it is the same user, it will allow the update to go through, but if it is a different user, it shows an error message. I chose the error message to be “The record is locked for editing by another user (5 minutes duration).”

Lastly, we need to create a new Button on Account to run Javascript code that updates the Last_Edit_Date_Time__c and redirects to the the account standard editing page:

Screen Shot 2014-12-30 at 7.20.18 PM

This is the Javascript code:

{!REQUIRESCRIPT("/soap/ajax/29.0/connection.js")}

// prepare account for updating the last edit date time
var anAccount = new sforce.SObject( "Account" );
anAccount.id = '{!Account.Id}';
var dtNow = new Date();
var nowInJSON = dtNow.toJSON();
anAccount.Last_Edit_Date_Time__c = nowInJSON;

// update the account
var result = sforce.connection.update( [ anAccount ] );

window.location.href = '/{!Account.Id}/e';

Now we need to just remove the standard Edit button and place the new custom Edit button on the page layout.

Screen Shot 2014-12-30 at 7.25.41 PM

This makes it so that if an user clicks Edit, it will set the Last Edit Date Time. The validation rule will prevent updates to the record from anyone else during the next 5 minutes. If another user attempts to save the same record, the validation rule will cause this error message to appear:

Screen Shot 2014-12-30 at 6.02.45 PM

 

 

Another trick: how to obtain a set from any field in a list – faster and without loops!

This is like the previous post about getting maps out of a list, but with the same trick applied for getting a set.

In triggers, we usually code a loop like below to collect certain IDs in a set and later use the set in a query.


Set<String> acctIDSet = new Set<String>();
for( Contact aContact : trigger.new ) {
    acctIDSet.add( aContact.AccountID );
}

The new SetCreator class does that without any loops and in a single line of code.

Set<String> acctIDSet =
      SetCreator.createSetFromField( trigger.new, ‘AccountID’ );

Below is the definition of the SetCreator class that allows that.

public class SetCreator {
    public static Set<String> createSetFromField(
        List<SObject> aList, String fieldName ) {

    // get the list in JSON format
    String jsonList = JSON.serialize( aList );

    // copy only the fieldName value using RegEx substitution
    jsonList = jsonList.replaceAll(
        '(\\{.*?"' + fieldName + '":"(.*?)"(,".*?")*\\},?)'
        , ',"$2"' ).replace( '[,', '[' );

    // create set from the modified JSON
    Set<String> extractedSet =
        (Set<String>) JSON.deserialize(
            jsonList, Set<String>.class );

    return extractedSet;
    }

}

It converts the list to a JSON string and modifies the string to look as if it was a serialized Set. It uses RegEx to keep the specified field values and remove everything else. It then deserializes the modified JSON string into a Set.
Again, there are no explicit loops – all of them are internal to the RegEx/JSON implementations, and are much faster.

Even if there are duplicates, they are automatically eliminated during the deserialization of the JSON string.

 

Trick: How to obtain a map indexed by any field (not just ID) faster and without loops!

Often we query an object and need to create a map out of the list of records retrieved. The most straightforward way is to declare a Map<ID, sObject> and initialize it with a list resulting from a SOQL query.

List<Account> acctList = [ SELECT ID, Name, AccountNumber
            FROM Account ];

Map<ID, Account> acctMapByID = new Map<ID, Account>( acctList );

The code shown above will populate the acctMapByID with accounts using their IDs as the key.

However, what if we wanted to create a map indexed by AccountNumber instead? We would have to loop through the list and add the records to the map one by one specifying theAccountNumber as the key.

List<Account> acctList = [ SELECT ID, Name, AccountNumber
            FROM Account ];

Map<String, Account> acctMapByNumber =
            new Map<String, Account>();

for( Account anAccount : acctList ) {
    acctMapByNumber.put( anAccount.AccountNumber, anAccount );
}

Now what if there was a way to do that without any loops and in a single line of code?

Map<String, SObject> acctMapByNumber =
       MapCreator.createMapByIndex( acctList, 'AccountNumber' );

Below is the definition of the MapCreator class that allows that.

public class MapCreator {
    public static Map<String, SObject> createMapByIndex(
        List<SObject> aList, String indexField ) {

    // get the list in JSON format
    String jsonList = JSON.serialize( aList );

    // remove enclosing []
    jsonList = jsonList.substring( 1, jsonList.length() - 1 );

    // copy the indexField value in front of each
    // {} group using RegEx substitution
    // example result: value:{..."indexField":"value"...}
    jsonList = '{' + jsonList.replaceAll(
        '(\\{.*?"' + indexField + '":"(.*?)"(,".*?")*\\},?)'
        , '"$2":$1' ) + '}';

    // create map from the modified JSON
    Map<String, SObject> changedMap =
        (Map<String, SObject>) JSON.deserialize(
            jsonList, Map<String, SObject>.class );

    return changedMap;
    }

}

It first converts the list to a JSON string, then modifies the string to look as if it was a serialized Map. It does that by using RegEx to insert the index in front of each record in the string. It then deserializes the modified JSON string into a map.
It doesn’t use any loops explicitly although there must be loops hidden in the RegEx and JSON method internal implementations, which are much faster.

What would be the advantages of using this class instead of coding the loop?

The MapCreator class uses string replacement/RegEx, which is faster than looping and creating map elements one by one.

According to my tests, it runs faster than looping for lists of roughly 8 records or more. For 63 records, it should be 5 to 6 times faster depending on the size of the records.

It might not be relevant for most of the situations but if you have a long running code that needs to be optimized, it might be worth a try.

PS.: I fixed a problem with the way WordPress displays code that made it convert quotes and double-quotes to special characters. Now the code can be copied and pasted onto Salesforce.
 

How to clone a custom object in Salesforce/Eclipse

This is how you can create a new custom object with the same fields as an existing custom object:

1) Download custom objects in Eclipse

– right-click the project in the ‘Package Explorer’ window

– in the popup menu, select ‘Force.com/Project Properties’

– in the properties window, select ‘Force.com/Project Contents’

– click ‘Add/Remove’, then make sure that the ‘objects – custom component’ is checked

– click ‘Apply’ and respond Yes to the ‘Refresh Project From Server’ dialog

2) Create new object in Eclipse

– right-click the ‘objects’ folder, then select ‘New/Custom Object’

– give it a label, plural label and a name, then save it

3) Copy existing object fields to the Clipboard

– right-click the object, then select ‘Open With/Text Editor’

– copy to the Clipboard the part from the first line of <fields> to the last line of </fields>

– it is usually between <enableReports> and <label>

4) Insert fields into the new object

– right-click the new object created in step 2, then select ‘Open With/Text Editor’

– locate the line with <enableReports> and paste the Clipboard contents right below it, before the <label> line

5) Repeat steps 3 and 4 for any other needed configurations

– for example, you may need to copy the lines with the following tags: <listViews>, <searchLayouts>, <validationRules>, <recordTypes>

6) Save the new altered object

– look at the ‘Problems’ tab at the bottom for any error messages, they will indicate what you need to change to make the object accepted

– problems to look for: relationship names will need to be changed to be different from the existing object’s relationships ( <relationshipLabel> and <relationshipName>)

– focus on first renaming relationship names like below to avoid duplicates and the rest of the formulas should work thereafter (the errors xxx__r doesn’t exist are because a relationship could not be created due to duplicate relationship names)

– after all errors are resolved, the object will be saved

EXISTS clause in SOQL

Here is how to emulate the EXISTS clause in SOQL

In the example below Education__c is a custom object that has a lookup field to Contact.

Suppose you have a query that would be written like this:

SELECT Id, Name
FROM Contact cn
WHERE EXISTS (

SELECT 1
FROM Education__c
WHERE Contact__c =  cn.Id ) 

It can be rewritten in SOQL using an Id Semi-Join as:

SELECT Id, Name
FROM Contact cn 
WHERE Id IN (

SELECT Contact__c

FROM Education__c )

It will retrieve all Contacts that have an associated Education__c record.

It is possible to use up to 2 Id Semi-Joins in a SOQL query (more information about it).

 

My 1st little App Exchange: SOQL Query Tool

As a little exercise, I’ve created a small tool comprised of a VisualForce page/tab that allows system administrators to run ad-hoc SOQL (SOSL and aggregate queries) from within their applications and do mass update on every page of 100 records. Basically, it reproduces a little of the functionality of the DeveloperForce utility Mass Update Anything.

This tool is available at the AppExchange for free:   goo.gl/90V8f

(The listing is private because SalesForce seems to only allow SalesForce Pardners – that is, companies – to take their apps to security review and make them public.)

SOQL Query Tool screenshot

The results of an ad-hoc query executed in SOQL Query Tool (click to expand).

In one class I used ApexPages.StandardSetController with a Database.QueryLocator to implement pagination of the query results.

objStdSetCtrl = new ApexPages.StandardSetController( Database.getQueryLocator( SOQLText ) );

In the VisualForce page I used the tag apex:repeat to display a dynamic list of columns nested in an apex:pageBlockTable that iterates on the query results’ rows.

<apex:pageBlockTable id="ResultsTbl" value="{!QryResults}" var="row">
    <apex:repeat id="ColumnsRpt" value="{!LstColNames}" var="col">
         <apex:column >
                {!col}
                <apex:outputLink rendered="{!( col == 'Id' || col == 'Name' )}" value="/{!row.id}" target="new">
 <apex:outputText value="{!row[col]}" />
...

What is a Lead, Account, Contact, and Opportunity in salesforce.com?

Very concise definitions on the article linked below:

http://info-logistics.com/2011/09/what-is-a-lead-account-contact-and-opportunity-in-salesforce-com/

Comparing SalesForce with MicroSoft Dynamics xRM

MicroSoft Dynamics (xRM)

SalesForce.com (SF)

Design * offers more options during entity creation* to make changes to a field, it has to be deleted then recreated and if there are forms/views with that field, such dependencies have to be deleted first * easier to create entities (objects)* fields can be changed/deleted and all the related forms are automatically updated

* Master-Detail relationships include cascaded deletes and detail roll-ups (sum, count, average, etc) but M-D relationships can’t be chained, which results in having to implement them as Lookups and lose the benefit of summarization offered by Master-Relationships

Data Manipulation * smarter data import tool with lookup of foreign keys by ID or description* doesn’t have the UpSert function (update existing records and insert new ones) that SalesForce has

* uses SQL Server

* smart data import (lookup of foreign keys)* requires external tool and has the little quirk of having to use UpSert instead of Insert

* the UpSert function updates existing records and insert new ones in one run

* the SF query language (SOQL) has limitations

Infrastructure * can be hosted on premises* no Governor limits if hosted in house

* on premises installation/configuration takes a long time

* there is no option to host SF on premises* subject to Apex Governor Limits because SF is a multi-tenant platform

* automatic upgrades twice a year, each release with a lot of new features

Bugs * error messages popup often although requiring only a retry from the same screen without closing/reopening * no bugs found
Speed * slow, not very responsive * very responsive
Usability * the user interface is familiar to MS Office users but it is very busy

* can be embedded in Outlook

* integrated with Sharepoint

* very basic/light-weight mobile web app (will likely require 3rd party for mobile access)

* offers a much cleaner/simpler interface* all users like it

* Outlook and Sharepoint integration requires a lot more effort

* SF is mobile ready

Customization /Programming * .net:  Visual Studio, C#, VB* seems very flexible * requires unit tests to publish triggers and classes* default filter screens can be customized only to a certain degree and beyond the basics, it requires coding

* requires creation of report types, then reports that use them

* Apex is very similar to Java and C#

* subject to Apex Governor Limits

* limited deployments to Production in a given period as per the contracted level of service

Workflows * offers more types of process steps and seems more cohesive than SF * there are 2 types:  approval process (triggered by the user) and workflow (triggered when a record changes in a specific way)
Forward thinking considerations * MS is one of the world’s largest R&D spenders * clearly the leader in CRM

* SF spends 8% of revenue in R&D while (2008 data) and 5x as much in sales and marketing

* SF buys many other companies and integrate their proven technologies into the platform

* customer base is very large

* the company leader is a visionary

Cost * measured roughly 1/3 to 1/2 of SF’s cost in 1, 3 and 10 years for a high number of users* can be paid monthly * entry price is cheaper (Group edition with 5 users)* annual upfront payment

The above is based on my experience with a proof of concept application and online research. Feel free to comment and add your corrections or a different point of view.

Pertinent links:

– MS Dynamics 2011 vs salesforce.com
Developer frustration
MicroSoft Dynamics CRM vs SalesForce – How do you choose?
Microsoft Dynamics CRM vs. Salesforce.com vs. Microsoft Office Business Contact Manager
– Comparing Microsoft Dynamics CRM To Salesforce
MicroSoft Dynamics CRM vs SalesForce (MS partner)
– Evaluating Software Vendors (from a MS shop)
– Microsoft Dynamics CRM: Much More Than Meets the Eye – Part 2 and Part 3
– Salesforce vs. Microsoft CRM: Relationship Management Applications
– Why We Chose to Work with Salesforce.com

Upserting into SalesForce using .net and SOAP API

I’ve been experimenting with the classes created by Darren Terrell in the article “SalesForce integration with .net web services SOAP API” and found that there is a limit of 200 rows for upserts, so I’ve changed the Upsert() method as below to divide and submit the upserts in batches of 200 items at a time.

public UpsertResult[] Upsert(string externalID, sObject[] items)
{
   SetupService();
   // send in batches of 200 updates
   int iIndex = 0;
   List objResults = new List(items.Count());
   while (iIndex < items.Count())
   {
      sObject[] obj200Items = items.SubArray(iIndex, 200);
      objResults.AddRange( salesforceService.upsert(externalID, obj200Items) );
      iIndex += 200;
   }
   return objResults.ToArray();
 }

The SubArray method is defined as an extension below:

public static class ArrayExtensions
{
 /// <summary>
 /// Extracts a subarray of a given number of items (count) from an array starting from a given position (startIndex)
 /// </summary>
 /// <typeparam name="T"></typeparam>
 /// <param name="arr">Array from which to extract a subarray</param>
 /// <param name="startIndex">Position from where to obtain the array items</param>
 /// <param name="count">Number of items</param>
 /// <returns></returns>
 public static T[] SubArray(this T[] arr, int startIndex, int count)
 {
    var sub = new T[count];
    int iActualCount = count;
    if( arr.Count() - startIndex < count )
       iActualCount = arr.Count() - startIndex;
    Array.Copy(arr, startIndex, sub, 0, iActualCount);
    return sub;
 }
}