Convert Saleforce 15 Digit IDs to 18

    Attached is a QVW script containing a Sub-Routine which converts 15 digit case-sensitive Salesforce IDs into 18 digit case-insensitive IDs. Plus a demo with sample data showing how it works. 

     

    Why would you ever need this?

    • If you use a lot of Salesforce data, sourced via the Salesforce connector or Apex Data Loader then you have 18 digit IDs 
    • When reports are exported they contain the 15 digit ID
    • Sometimes complex Salesforce reports are impractical or too difficult to replicate using the connector. 
    • Salesforce's Opportunity Influence linkages to campaigns are not exposed by the API / reachable via a SOQL statement

     

    Hope you find this useful!

     

    --- John McTurnan

     

     

     

     

     

    //-----------------------------------------------------------------------------------------------------------------------------------

    SUB-ROUTINE BY ITSELF (No Demo)

    //-----------------------------------------------------------------------------------------------------------------------------------

    //-----------------------------------------------------------------------------------------------------------------------------------

    //-------- SUBROUTINE:  CONVERT SFDC 15 DIGIT KEY TO 18 -----------------------------------------------------------------------------

    // Credits: 

    // John McTurnan:  Conversion from to Qlikview scripting from Javascript (jmcturnan@gmail.com)

    // Ron Hess:  author of the Javascript version (http://boards.developerforce.com/t5/user/viewprofilepage/user-id/198)

    //-----------------------------------------------------------------------------------------------------------------------------------

    //-----------------------------------------------------------------------------------------------------------------------------------

    //-----------------------------------------------------------------------------------------------------------------------------------

    Sub SFDC_ID_15_to_18 (v_SFDC_ID15) //

     

     

      Let v_SFDC_ID18 = v_SFDC_ID15;

      Let v_SFDC_ID15 = chr(39) & v_SFDC_ID15 & chr(39);

      Let v_Check_digit = chr(39) & 'ABCDEFGHIJKLMNOPQRSTUVWXYZ012345' & chr(39);

      Let v_LT = ORD('A');

      Let v_GT = ORD('Z');

     

     

    IF len(v_SFDC_ID15) <> 17 THEN //======  Check if ID conforms to basic length.  Exit & Null if not OK.

      Let v_SFDC_ID18 = Null();

      Exit Sub;

    EndIf;

     

     

      Set v_i = 1; 

        

       For v_i = 1 to 3

      

          Set v_X = 0;

          Let v_Block = chr(39) & Mid($(v_SFDC_ID15), 5 * ($(v_i) - 1) + 1, 5) & chr(39);

          v_j = 1;

         

          For v_j = 1 TO 5

         

             Let v_C = ORD(Mid($(v_Block), $(v_j), 1));

     

     

             IF v_LT <= v_C And v_C <= v_GT THEN

              Let v_X = $(v_X) + POW(2,($(v_j) - 1));

             EndIf;

     

     

          Next

     

     

          Let v_temp1 = Mid($(v_Check_digit), $(v_X) + 1, 1);

      

          Let v_SFDC_ID18 = v_SFDC_ID18 & v_temp1;

       

      Next

     

     

      Let v_SFDC_ID18 = chr(39) & v_SFDC_ID18 & chr(39);

     

    EndSub;

    //-----------------------------------------------------------------------------------------------------------------------------------