SQL-controlequery toegevoegde marktlijstleden

SELECT FilteredListMember.listmemberid, FilteredListMember.createdbyname, FilteredListMember.modifiedon, FilteredList.listname, DATEPART(m, FilteredListMember.createdon) AS Maand, DATEPART(yy, FilteredListMember.createdon) AS Jaar, DATEPART(dd, FilteredListMember.createdon) AS Dag, FilteredListMember.createdby

FROM FilteredListMember INNER JOIN FilteredList ON FilteredListMember.listid = FilteredList.listid

WHERE (FilteredListMember.listid = '6E093B6F-E775-DE11-B303-005056940368') AND (DATEPART(m, FilteredListMember.createdon) = 8) AND (DATEPART(yy, FilteredListMember.createdon) = 2009) AND (DATEPART(dd, FilteredListMember.createdon) = 18) AND (FilteredListMember.createdby = 'A26FF5EE-AF22-DE11-8C4D-005056940368')

ORDER BY FilteredListMember.modifiedon DESC

Creëer masker op veld

function OnCrmPageLoad()
{
Mask( "telephone2" , "+(###)-(#)###-###" );
Mask( "gi_visa" , "####-####-####-####" );
Mask( "gi_scid" , "#-########-#" );
}

function Mask( fieldId , mask )
{
field = document.getElementById(fieldId);
field.mask = mask.split("");
field.regex = new RegExp(escapeRegEx(mask.replace(/#/gi,"").split("")),"gi");
field.title += " " + mask;
field.attachEvent( "onchange" , MaskOnFieldChange );
}

function escapeRegEx( chars )
{
var regChars = "+_)(*^$[]-?{}"; //Add all regexp chars id needed
var regExprs = "";
var run2Index = chars.length - 1;

for( var i = 0 ; i < run2Index ; i++ ) Concat( chars[i] , "|" );

Concat(chars[run2Index]);

function Concat( c , d ){
regExprs += (( regChars.indexOf(c) != -1 )? "\\":"" ) + c + d;
}

return regExprs;
}

function MaskOnFieldChange()
{
var field = event.srcElement;
if( field.DataValue == null ) return;

var arrDataValue = field.DataValue.replace(field.regex,"").split("");
var arrResult = [];

for(var i=0 , j=0 ; i < field.mask.length ;i++)
arrResult[i] = (field.mask[i] != "#")?field.mask[i]:arrDataValue[j++];

field.DataValue = arrResult.join("");
}

OnCrmPageLoad();

Opvragen fetch vanuit Advanced Find

javascript:void( new function(){ prompt("Fetch Parameters:",getFetchParams());function getFetchParams(){ return "FetchXml:\n" + advFind.FetchXml + "\n\n" + "LayoutXml:\n" + advFind.LayoutXml + "\n\n" + "EntityName:\n" + advFind.EntityName + "\n\n" + "DefaultAdvancedFindViewId:\n" + advFind.DefaultAdvancedFindViewId } } )

Soap: ophalen lookup uit gekoppelde entiteit

//Get the lookup ownerid

GetSalesteam = function()
{
var lookupItem = crmForm.all.ownerid.DataValue; //Dit is de de lookup waarbij je extra gegevens op wilt halen

// check if there is data in the field, get the salesteam
if(lookupItem != null && lookupItem[0] != null)
{
var sb=new StringBuilder();
sb.Append('<?xml version="1.0" encoding="utf-8"?>');
sb.Append('<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/%22 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance%22 xmlns:xsd="http://www.w3.org/2001/XMLSchema%22>');
sb.Append(GenerateAuthenticationHeader());
sb.Append('<soap:Body>');
sb.Append(' <RetrieveMultiple xmlns="'+CRM2007_WEBSERVICE_NS+'">');
sb.Append(' <query xmlns:q1="http://schemas.microsoft.com/crm/2006/Query%22 xsi:type="q1:QueryExpression">');
sb.Append(' <q1:EntityName>systemuser</q1:EntityName>'); //Dit is de gekoppelde entiteit waaruit je gegevens op wilt halen
sb.Append(' <q1:ColumnSet xsi:type="q1:ColumnSet"><q1:Attributes><q1:Attribute>sgt_salesteamid</q1:Attribute></q1:Attributes></q1:ColumnSet>'); //Dit is het veld dat je uit de gekoppelde entiteit wilt ophalen
sb.Append(' <q1:Criteria>');
sb.Append(' <q1:FilterOperator>And</q1:FilterOperator>');
sb.Append(' <q1:Conditions><q1:Condition>');
sb.Append(' <q1:AttributeName>systemuserid</q1:AttributeName>');
sb.Append(' <q1:Operator>Equal</q1:Operator>');
sb.Append(' <q1:Values><q1:Value xsi:type="xsd:string">' + lookupItem[0].id + '</q1:Value></q1:Values>');
sb.Append(' </q1:Condition></q1:Conditions>');
sb.Append(' </q1:Criteria>');
sb.Append(' </query>');
sb.Append(' </RetrieveMultiple>');
sb.Append('</soap:Body>');
sb.Append('</soap:Envelope>');
var xml=sb.ToString();

var xmlHttpRequest=new ActiveXObject("Msxml2.XMLHTTP");
xmlHttpRequest.open('POST','/mscrmservices/2007/CrmService.asmx',false);
xmlHttpRequest.setRequestHeader('SOAPAction',CRM2007_WEBSERVICE_NS+'/RetrieveMultiple');
xmlHttpRequest.setRequestHeader('Content-Type','text/xml; charset=utf-8');
xmlHttpRequest.setRequestHeader('Content-Length',xml.length);
xmlHttpRequest.send(xml);
var xmlDoc=xmlHttpRequest.responseXML;
// look for response
if ( xmlDoc.selectSingleNode('//q1:sgt_salesteamid') != null ) //Controle of het veld dat je op wilt halen wel data bevat
{
var salesteam = new Array();
salesteam[0] = new LookupControlItem(
xmlDoc.selectSingleNode('//q1:sgt_salesteamid').text, 10001, //10001 is het nummer van de entiteit waaruit je gegevens op wilt halen
xmlDoc.selectSingleNode('//q1:sgt_salesteamid').getAttribute('name'));
// assign new lookup to the field
crmForm.all.sgt_salesteamid.DataValue = salesteam;
}
else
{
// make the field empty, for the contactpersoon is null
crmForm.all.sgt_salesteamid.DataValue = null;
}
}
}
GetSalesteam();

Expiratiedatum bepalen o.b.v. Aanvraagtype en Prioriteit

var caseTypeCodeName=crmForm.all.casetypecode.SelectedText;
var priorityCodeName=crmForm.all.prioritycode.SelectedText;

switch(priorityCodeName.toLowerCase())
{
case 'hoog':
switch(caseTypeCodeName.toLowerCase())
{
case 'incident':
crmForm.all.followupby.DataValue=getFollowUpDate(1);
break;
case 'vraag':
crmForm.all.followupby.DataValue=getFollowUpDate(2);
break;
case 'wijziging':
case 'nieuw':
crmForm.all.followupby.DataValue=getFollowUpDate(5);
break;
}
break;
case 'normaal':
switch(caseTypeCodeName.toLowerCase())
{
case 'incident':
case 'vraag':
crmForm.all.followupby.DataValue=getFollowUpDate(3);
break;
case 'wijziging':
case 'nieuw':
crmForm.all.followupby.DataValue=getFollowUpDate(10);
break;
}
break;
case 'laag':
switch(caseTypeCodeName.toLowerCase())
{
case 'incident':
case 'vraag':
crmForm.all.followupby.DataValue=getFollowUpDate(10);
break;
case 'wijziging':
case 'nieuw':
crmForm.all.followupby.DataValue=getFollowUpDate(20);
break;
}
break;
}

function getFollowUpDate(nrOfDays)
{
var weekday=new Array(7);
weekday[0]='sunday';
weekday[1]='monday';
weekday[2]='tuesday';
weekday[3]='wednesday';
weekday[4]='thursday';
weekday[5]='friday';
weekday[6]='saturday';
var date
if(!IsNull(crmForm.all.createdon) && !IsNull(crmForm.all.createdon.DataValue))
date=crmForm.all.createdon.DataValue;
else
date=new Date();
// Calculate the new followup date
var followupDate=new Date();
followupDate.setFullYear(date.getFullYear(),date.getMonth(),date.getDate()+nrOfDays);
var followupDay=weekday[followupDate.getDay()];
// If followupday is on saterday or sunday, set the followupday to monday
if(followupDay=='saturday')
nrOfDays=2;
else if(followupDay=='sunday')
nrOfDays=1;
else
nrOfDays=0;
// Set the new followup date
date.setFullYear(followupDate.getFullYear(),followupDate.getMonth(),followupDate.getDate()+nrOfDays);
return date;
}

Tellen van het aantal notities in een aanvraag

//COUNT NUMBER OF NOTES
var totalNotes = getTotalNotes(crmForm.ObjectId);
setNoteTabName(totalNotes);

function setNoteTabName(count) {
    /* update note tab */
    if (crmForm.FormType != 1) {
        var cells = document.getElementsByTagName("A");

        for (var i = 0; i < cells.length; i++) {
            if (cells[i].innerText == "Opmerkingen") {
                if (count > 0) {
                        cells[i].innerText = "Opmerkingen (" + count + ")";
                        document.all.crmTabBar.style.width = "auto";
                }
                break;
            }
        }
    }
}

// Helper method to return the total notes associated with an object
function getTotalNotes(objectId) {
        // Define 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\">",
        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>annotation</q1:EntityName>",
        "<q1:ColumnSet xsi:type=\"q1:ColumnSet\"><q1:Attributes><q1:Attribute>createdon</q1:Attribute></q1:Attributes></q1:ColumnSet>",
        "<q1:Distinct>false</q1:Distinct><q1:Criteria><q1:FilterOperator>And</q1:FilterOperator>",
        "<q1:Conditions><q1:Condition><q1:AttributeName>objectid</q1:AttributeName><q1:Operator>Equal</q1:Operator>",
        "<q1:Values><q1:Value xsi:type=\"xsd:string\">",
        objectId,
        "</q1:Value></q1:Values></q1:Condition></q1:Conditions></q1:Criteria>",
        "</query>",
        "</RetrieveMultiple>",
        "</soap:Body>",
        "</soap:Envelope>"
        ].join("");
        var resultXml = executeSoapRequest("RetrieveMultiple", xml);
        return getMultipleNodeCount(resultXml, "q1:createdon");
}

// Helper method to execute a SOAP request
function executeSoapRequest(action, xml) {
    var actionUrl = "http://schemas.microsoft.com/crm/2007/WebServices/";
    actionUrl += action;

    var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
    xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
    xmlHttpRequest.setRequestHeader("SOAPAction", actionUrl);
    xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
    xmlHttpRequest.send(xml);

    var resultXml = xmlHttpRequest.responseXML;
    return resultXml;
}

// Helper method to return total # of nodes from XML
function getMultipleNodeCount(tree, el) {
    var e = null;
    e = tree.getElementsByTagName(el);
    return e.length;
}

Locatie automatisch vullen vanuit het veld Betreft bij toevoegen afspraak in CRM

Maak een workflow aan op de entiteit Afspraak die afgaat wanneer de afspraak toegevoegd wordt of wanneer het veld Locatie in de afspraak wordt gewijzigd.