+ Start a Discussion
chrismclarenchrismclaren 

Retrieving record count to file

I'd like to be able to create a query then return the record count and store this into a file.  I can do all this but store it into a file.  I have managed to sumbit the query, retireve the data and then store the array count into a variable.  I cannot then print this record count to a file.  Can anyone help.  This is the code.
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head> 
    <title>AOC</title>
       <script
" type="text/javascript"></script>
    <script type="text/javascript">
    function load(){
        sforceClient.registerInitCallback(main);
        sforceClient.init("{!API_Session_ID}", "{!API_Partner_Server_URL_70}", true);
    }
  
function main(){   
var d = new Date();
d.setDate(d.getDate()-130);

 var orden = new Array();
 orden.count = 0;
        var cd = new Array();
        cd.push("CloseDate");
        cd.push("ASC");
       
        var n = new Array();
        n.push("Name");
        n.push("DESC");
       
        orden.push(cd);
        orden.push(n);
      
 sforceClient.QueryAllSorted("select Id, My_Commision__c, Name, CloseDate,
Amount from Opportunity"   , orden);
    }
   
function displayResults(opps)
 {
  
 totallength = opps.length;
       var out ="";
out += ""
 document.getElementById("DivWritetext").innerHTML = out;
 }
    sforceClient.QueryAllSorted = function(soql, sort_orders) {
        sforceClient.sortArray = sort_orders;
       
        sforceClient.sobjects = new Array();
        this.Query(soql, queryCallback);
    }
   
    sforceClient.sortArray = new Array();
   
    queryCallback = function(ret) {
        if (ret.size > 0){
            sforceClient.sobjects = sforceClient.sobjects.concat(ret.records);
            if (ret.done == false){
                    sforceClient.queryMore(ret.queryLocator, queryCallback)
            } else {
                displayResults(sforceClient.sobjects.sort(sortSObject));   
  }
 }
    }
</script>
</head>
<FORM ACTION="https://na1.salesforce.com/home/home.jsp">
<div style="position: absolute; left: 640px; text-align: center; top: 29px; width:
155px; height: 44px; font-family: Bradley Hand ITC; font-weight: bold; font-size:
14pt">
&nbsp;<INPUT TYPE=submit value="Salesforce.com" style="position: absolute; left: 1;
text-align: center; top: 0; width: 152px; height: 43px; font-family: Bradley Hand ITC;
font-weight: bold; font-size: 14pt">
 </FORM>
<body onLoad="load();">
<div id=DivWritetext>
<script
src='https://www.salesforce.com/services/lib/ajax/beta3.3/sforceclient.js?browser=true
' type='text/javascript'>
var fso = new ActiveXObject("Scripting.FileSystemObject");
var newFileObject = fso.CreateTextFile("C:\\acccounts.txt",true);
newFileObject.WriteLine ("1");
newFileObject.Close();</script>
</div>
 
</body>
</html>
 
Ron HessRon Hess
what part of this fails?
did you check return values for the activeX calls?

I tried this once, found it was very tricky and basicaly a security risk to allow a web page to write to the user's hard disk. several browser settings must be correct to allow active x to write to your hard disk, i eventualy gave up as this is not portable across supported browsers.


chrismclarenchrismclaren
None of this fails.  It all works except that I can't get the array value of the record count (totallength) to be saved to file.  This script will only be run from one machine with the activeX settings all correctly set.  I know that it works as I can print the variable totallength (the array count) in HTML to the screen.  I can also write static text to file loutisde the functions but I can't write the variable totalength to file with the static text needed.
 
The other option is to achieve what I am trying to do.  I have several charts all driven from XML that need the salesforce data.  Can anyone suggest an application that will write this data to the xml file daily (also it has to be free).
Ron HessRon Hess
does the filesystem scripting object do an auto save?
if you close the object, does that save it ?

just guessing, but you may just need a flush() or save() before closing the object.  unless write() does that for you.
chrismclarenchrismclaren
I managed to get the code working.  It searches the salesforce.com database and then stores the record count (of the array containing the data) into a variable.  This varriable is then stored into an xml file./  For those that would like the code it is here:-
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head> 
    <title>sort</title>
    <script src="https://www.salesforce.com/services/lib/ajax/beta3.3/sforceclient.js?browser=true" type="text/javascript"></script>
<script type="text/javascript">
    function load(){
        sforceClient.registerInitCallback(main);
        sforceClient.init("{!API_Session_ID}", "{!API_Partner_Server_URL_70}", true);
    }
 
function main(){   
var d = new Date();
d.setDate(d.getDate()-130);
 var orden = new Array();
 orden.count = 0;
        var cd = new Array();
        cd.push("Id");
        cd.push("ASC");
       
        var n = new Array();
        n.push("Name");
        n.push("DESC");
       
        orden.push(cd);
        orden.push(n);
       
        sforceClient.QueryAllSorted("select Id from Account"   , orden);
    }
   
function displayResults(opps) {
 
    var sum_amount = 0;
 var textlength = "Total records= "
 var totallength = opps.length;
    var out ="";
    out += "<table border='1'><tbody>";
      
out += "<br>" + "<BR><B> " + (textlength) + (totallength) + "<BR>"
 document.getElementById("maindiv").innerHTML = out;

var fso = new ActiveXObject("Scripting.FileSystemObject");
var newFileObject = fso.CreateTextFile("C:\\totalaccounts.xml",true);
newFileObject.WriteLine ("<graph caption='Salesforce.com data' xAxisName='Month' showgridbg='0'canvasBgColor='99CC99' baseFontColor='333333' canvasbgcolor='99CC99' hovercapborder='889E6D'hovercapborder='CECE00' hovercapbg='FFFFDD' divlinecolor='F47E00' lineThickness='3'  yaxisminvalue='0' limitsDecimalPrecision='0' divLineDecimalPrecision='0' numdivlines='4'>");
newFileObject.WriteLine ("<category name='Mar 06' hoverText='March'/>");
newFileObject.WriteLine ("<category name='Apr 06' hoverText='April'/>");
newFileObject.WriteLine ("<category name='May 06' hoverText='May'/>");
newFileObject.WriteLine ("<category name='Jun 06' hoverText='June'/>");
newFileObject.WriteLine ("</categories>");
newFileObject.WriteLine ("<dataset seriesname='Opportunities' color='222222' showValue='0'>");
newFileObject.WriteLine ("<set value='1845' />");
newFileObject.WriteLine ("<set value='1854' />");
newFileObject.WriteLine ("<set value='1098' />");
newFileObject.WriteLine ("<set value='" + totallength + "'/>");
newFileObject.WriteLine ("</dataset></graph>");
newFileObject.WriteLine ("");
newFileObject.Close();
    }
   
     function sortSObject(a, b){
        for (var i=0; i<sforceClient.sortArray.length; i++){
            var va = a.get(sforceClient.sortArray[i][0]);
            var vb = b.get(sforceClient.sortArray[i][0]);
           
            var s = 1;
           
            if (sforceClient.sortArray[i][1] == "DESC"){
                s = -1;
            }
            if (va > vb){
                return 1 * s;
            } else {
                if (va < vb){
                    return -1 *s;
                }
            }
        }
       
        return 0;
    }
    sforceClient.QueryAllSorted = function(soql, sort_orders) {
        sforceClient.sortArray = sort_orders;
       
        sforceClient.sobjects = new Array();
        this.Query(soql, queryCallback);
    }
   
    sforceClient.sortArray = new Array();
   
    queryCallback = function(ret) {
        if (ret.size > 0){
            sforceClient.sobjects = sforceClient.sobjects.concat(ret.records);
            if (ret.done == false){
                    sforceClient.queryMore(ret.queryLocator, queryCallback)
            } else {
                displayResults(sforceClient.sobjects.sort(sortSObject));
            }
        }
    }
   
    </script>
</head>
<body onLoad="load();">
    <div id="maindiv"></div>
</body>
</html>
 
 
I've taken away some of the xml data (stored on the C:\ drive) that we use but this is almost the complete code I use.  It returns how many accounts we have.  You need to ensure that your browser allows activeX to be run.  I ask it to prompt me for any activeX activity.  If you have any suggestions on how to make this code better then let me know.