Consultando Dados em Outra Tabela em Java Script

Question

Olá pessoal, 

Bom, vou direto ao assunto.

Tenho este script abaixo que me retorna todas as minha contas na seguinte ordem: ID e CampoInt.

Minha necessidade é o seguinte:

Estou precisando alterar este script para que eu consiga retornar a soma dos valores do campo int.

No aguardo.

Segue abaixo o script.

// Prepare variables to fetch accounts.
var fetchMapping = "logical";
var entityName = "account";
var firstColumn = "accountid";
var secondColumn = "new_campo_int";
var linkEntity = "systemuser";
var linkEntityTo ="owninguser";
var filterType = "and";
var conditionAttribute = "lastname";
var operator = "ne";
var value = "Cannon";
var authenticationHeader = GenerateAuthenticationHeader();

// Prepare the SOAP message.
var xml = "<?xml version='1.0' encoding='utf-8'?>"+
"<soap:Envelope xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"+
" xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"+
" xmlns:xsd='http://www.w3.org/2001/XMLSchema'>"+
authenticationHeader+
"<soap:Body>"+
"<Fetch xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>"+
"<fetchXml>&lt;fetch mapping='"+fetchMapping+"'&gt;"+
"&lt;entity name='"+entityName+"'&gt;"+
"&lt;attribute name='"+firstColumn+"'/&gt;"+
"&lt;attribute name='"+secondColumn+"'/&gt;"+
"&lt;link-entity name='"+linkEntity+"' to='"+linkEntityTo+"'&gt;"+
"&lt;filter type='"+filterType+"'&gt;"+
"&lt;condition attribute='"+conditionAttribute+"'"+
" operator='"+operator+"' value='"+value+"'/&gt;"+
"&lt;/filter&gt;"+
"&lt;/link-entity&gt;"+
"&lt;/entity&gt;"+
"&lt;/fetch&gt;</fetchXml>"+
"</Fetch>"+
"</soap:Body>"+
"</soap:Envelope>";
// Prepare the xmlHttpObject and send the request.
var xHReq = new ActiveXObject("Msxml2.XMLHTTP");
xHReq.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xHReq.setRequestHeader("SOAPAction","http://schemas.microsoft.com/crm/2007/WebServices/Fetch");
xHReq.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xHReq.setRequestHeader("Content-Length", xml.length);
xHReq.send(xml);
// Capture the result.
var resultXml = xHReq.responseXML;

// Check for errors.
var errorCount = resultXml.selectNodes('//error').length;
if (errorCount != 0)
{
 var msg = resultXml.selectSingleNode('//description').nodeTypedValue;
 alert(msg);
}
// Process and display the results.
else
{

// Capture the result and UnEncode it.
var resultSet = new String();
resultSet = resultXml.text;
resultSet.replace('&lt;','<');
resultSet.replace('&gt;','>');

// Create an XML document that you can parse.
 var oXmlDoc = new ActiveXObject("Microsoft.XMLDOM");
 oXmlDoc.async = false;
// Load the XML document that has the UnEncoded results.
 oXmlDoc.loadXML(resultSet);
// Display the results.
 var results = oXmlDoc.getElementsByTagName('result');
  var msg = "\Account Id\t\t\t\tCampo Int\r";
msg +="--------------------------------------------------------------------------------\r";
 for (i=0;i < results.length;i++)
 {
  var idValue = results[i].selectSingleNode('./accountid').nodeTypedValue;
  var campoInt = results[i].selectSingleNode('./new_campo_int').nodeTypedValue;
  msg += idValue +"\t"+ campoInt+"\r";
 }
 alert(msg);
}

 

Answer 1

Jefferson,

 

 

Construí um método para fazer a soma, teste ai... Coloque no onload da entidade pai.

 

 

 

function SomarValoresDeFilhos(entidade, atributoRelacionado, idRelacionado, atributoSomar) {

 var XMLRequest = "" +
 "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
 "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +
 GenerateAuthenticationHeader() +
 " <soap:Body>" +
 " <RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
 " <query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryExpression\">" +
 " <q1:EntityName>" + entidade + "</q1:EntityName>" +
 " <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" +
 " <q1:Attributes>" +
 " <q1:Attribute>" + atributoSomar + "</q1:Attribute>" +
 " </q1:Attributes>" +
 " </q1:ColumnSet>" +
 " <q1:Distinct>false</q1:Distinct>" +
 " <q1:Criteria>" +
 " <q1:FilterOperator>And</q1:FilterOperator>" +
 " <q1:Conditions>" +
 " <q1:Condition>" +
 " <q1:AttributeName>" + atributoRelacionado + "</q1:AttributeName>" +
 " <q1:Operator>Equal</q1:Operator>" +
 " <q1:Values>" +
 " <q1:Value xsi:type=\"xsd:string\">" +
 idRelacionado +
 "</q1:Value>" +
 "</q1:Values>" +
 " </q1:Condition>" +
 " </q1:Conditions>" +
 " </q1:Criteria>" +
 " </query>" +
 " </RetrieveMultiple>" +
 " </soap:Body>" +
 "</soap:Envelope>" +
 "";

 var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
 xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
 xmlHttpRequest.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
 xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
 xmlHttpRequest.setRequestHeader("Content-Length", XMLRequest.length);
 xmlHttpRequest.send(XMLRequest);

 var Result = xmlHttpRequest.responseXML;
 var BusinessEntityNodes = Result.selectNodes("//RetrieveMultipleResult/BusinessEntities/BusinessEntity");

  var soma = 0;
 if (BusinessEntityNodes.length != 0) {
  for (var i = 0; i < BusinessEntityNodes.length; i++) {
   soma += parseInt(BusinessEntityNodes[i].firstChild.text);
  }
 }

 return soma;
}

 

 

Chamada do método:

 

 

var entidadeFilho = "quote";
var atributoRelacionamentoNoFilho = "accountid";
var atributoRelacionamentoNoFilhoValor = "{78FAE379-E3E6-DC11-A30B-0019B9D772C6}";
var campoASerSomadoNaEntidadeFilho = "billto_name";

SomarValoresDeFilhos(entidadeFilho, atributoRelacionamentoNoFilho, atributoRelacionamentoNoFilhoValor, campoASerSomadoNaEntidadeFilho);

 

 

Abraço,

---

Ricardo Alves
ricardoalves.me

Answer 2

Hehe, um dia chego lá..

Ricardo Muito obrigado, quebrei a cabeça aqui para tentar entender e consegui, funciona perfeitamente.

Muitíssimo obrigado!

Answer 3

Por nada... 8)

 

 

---

Ricardo Alves
ricardoalves.me