Sunday, April 23, 2017

Customer Address Entity in Dynamics crm

The Customer Address entity is one of the different entities in Microsoft dynamics CRM. This entity stores the address for accounts and contacts. But there is something special about this entity. Below are the few points which I could find during my experience with this entity.  

      · The address fields on account and contact are pointers to the records in the customer address entity: The account and contact entity have address1_street1, address1_city, address1_stateorprovince, etc. and a set of address2 fields. When an account or a contact is created the values entered in these fields are stored in the customer address entity. So, the fields on the account or contact are pointers to the records in the address entity. If the values are not entered in these fields CRM creates two blank records in the customer address entity. CRM takes care of keeping these two entities in sync. If the address values are changed on the account or the contact record, the associated records in the customer address entity are updated automatically. 

      ·Address entity cannot be customized: You cannot modify the relationship mappings with Account and contact entity. Also, you cannot create relationships with any other entity. Also, the customer address entity is not available on security roles. 

       ·The first two Address (address1 and address2) records that the CRM creates are hidden in the “More Addresses” associated view on the Account or Contact  

      ·The account or the contact lookup cannot be added on the address records: In the case of Addresses, the only thing stored in the Address table is a field for the GUID. Without the name of the related object .Thus, the lookup view is normal as other entities.

Friday, February 24, 2017

Upload csv files to oracle table in c#

Below is the code to upload csv files to oracle DB in c#

 protected void Import_Click(object sender, EventArgs e)
        {
            try
            {
                int cnt = 0;
                DataTable dt = new DataTable();
                string fileName = fu.FileName;
                string FileExtension = fileName.Substring(fileName.LastIndexOf('.') + 1).ToLower();
                if (FileExtension == "csv" || FileExtension == "CSV")
                {
                    // Load CSV File to Datatable
                    dt = ReadCsvFile();
                    if (dt.Rows.Count > 0)
                    {
                        // Remove all the rows from the tables
                        DSPInventory.Core.DSP_PAGER_Week_Methods.DeleteAllRows();
                       
                        for (int i = 0; dt.Rows.Count > i; i++)
                        {
                            string Week_from = dt.Rows[i].ItemArray.GetValue(0).ToString();
                            string Week_to = dt.Rows[i].ItemArray.GetValue(1).ToString();
                            string US_Day_primary = dt.Rows[i].ItemArray.GetValue(2).ToString();

                            string US_Day_Pri_ADID = dt.Rows[i].ItemArray.GetValue(3).ToString();
                            string US_Day_pri_Email = dt.Rows[i].ItemArray.GetValue(4).ToString();
                            string US_Day_pri_Phone = dt.Rows[i].ItemArray.GetValue(5).ToString();

                            string US_Day_Pager_pri_id = dt.Rows[i].ItemArray.GetValue(6).ToString();
                            string US_Day_secondary = dt.Rows[i].ItemArray.GetValue(7).ToString();
                            string US_Day_sec_ADID = dt.Rows[i].ItemArray.GetValue(8).ToString();

                            string US_Day_sec_Email = dt.Rows[i].ItemArray.GetValue(9).ToString();
                            string US_Day_sec_Phone = dt.Rows[i].ItemArray.GetValue(10).ToString();
                            string US_Day_sec_pri_id = dt.Rows[i].ItemArray.GetValue(11).ToString();

                            string INDIA_Day_primary = dt.Rows[i].ItemArray.GetValue(12).ToString();
                            string INDIA_Day_Pri_ADID = dt.Rows[i].ItemArray.GetValue(13).ToString();
                            string INDIA_Day_pri_Email = dt.Rows[i].ItemArray.GetValue(14).ToString();

                            string INDIA_Day_pri_Phone = dt.Rows[i].ItemArray.GetValue(15).ToString();
                            string INDIA_Day_Pager_pri_id = dt.Rows[i].ItemArray.GetValue(16).ToString();
                            string INDIA_Day_secondary = dt.Rows[i].ItemArray.GetValue(17).ToString();

                            string INDIA_Day_sec_ADID = dt.Rows[i].ItemArray.GetValue(18).ToString();
                            string INDIA_Day_sec_Email = dt.Rows[i].ItemArray.GetValue(19).ToString();
                            string INDIA_Day_sec_Phone = dt.Rows[i].ItemArray.GetValue(20).ToString();

                            string INDIA_Day_sec_pri_id = dt.Rows[i].ItemArray.GetValue(21).ToString();

                            // Insert into table 

                        }
                    }
                    if (cnt > 0)
                    {
                        GridView1.DataSource = dt;
                        GridView1.DataBind();
                        Label1.Text = "Below Grid Data Successfully Uploaded to Weekly Pager Table!!";
                    }
                }
                else
                {
                    Label1.Text = "Only CSV Files Allowed!!";
                }
                
            }
            catch (Exception ex)
            {
                Label1.Text = ex.Message.ToString();
            }
        }

        /// <summary>
        /// Load csv file to DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable ReadCsvFile()
        {
            DataTable dtCsv = new DataTable();
            string Fulltext;
            if (fu.HasFile)
            {
                using (StreamReader sr = new StreamReader(fu.PostedFile.InputStream))
                {
                    while (!sr.EndOfStream)
                    {
                        Fulltext = sr.ReadToEnd().ToString();//read full file text
                        string[] rows = Fulltext.Split('\n');//split full file text into rows
                        for (int i = 0; i < rows.Count() - 1; i++)
                        {
                            string[] rowValues = rows[i].Split(',');//split each row with comma to get individual values 
                            {
                                if (i == 0)
                                {
                                    for (int j = 0; j < rowValues.Count(); j++)
                                    {
                                        dtCsv.Columns.Add(rowValues[j]);//add headers
                                    }
                                }
                                else
                                {
                                    DataRow dr = dtCsv.NewRow();
                                    for (int k = 0; k < rowValues.Count(); k++)
                                    {
                                        dr[k] = rowValues[k].ToString();
                                    }
                                    dtCsv.Rows.Add(dr);//add other rows
                                }
                            }
                        }
                    }
                }
            }
            return dtCsv;
        }

Friday, December 16, 2016

Associate Lead to Email Send in Dynamics CRM


The Associate Lead to Email Send custom workflow activity will accept a Lead and an Email Send and associate the two via a CRM many-to-many relationship named cdi_emailsend_lead

Inputs

Name
Type
Notes
Email Send
EntityReference (cdi_emailsend)
Lookup to the Email Send
Lead
EntityReference (lead)
Lookup to the Lead


Name
Type
Notes
Success
Boolean
Indicates Success or failure of the code

OutputsAssociate two records

using Microsoft.Xrm.Sdk;

using Microsoft.Xrm.Sdk.Messages;

using Microsoft.Xrm.Sdk.Workflow;

using System;

using System.Activities;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;


namespace CustomWorkflow

{

    public class AssociateContactToEmailSend : CodeActivity

    {

        #region Input Properties

        [RequiredArgument]

        [Input("Contact")]

        [ReferenceTarget("contact")]

        public InArgument<EntityReference> Guest { get; set; }


        [RequiredArgument]

        [Input("Email Send")]

        [ReferenceTarget("cdi_emailsend")]

        public InArgument<EntityReference> EmailSend { get; set; }

        #endregion

        protected override void Execute(CodeActivityContext context)

        {

            IWorkflowContext workflowContext = context.GetExtension<IWorkflowContext>();

            IOrganizationServiceFactory serviceFactory = context.GetExtension<IOrganizationServiceFactory>();

            IOrganizationService service = serviceFactory.CreateOrganizationService(workflowContext.UserId);

            //Create the tracing service

            ITracingService tracingService = context.GetExtension<ITracingService>();


            var guest = Guest.Get(context);

            var emailSend = EmailSend.Get(context);


            tracingService.Trace("Guest:" + guest.Id);

            tracingService.Trace("Email Send:" + emailSend.Id);


            Microsoft.Xrm.Sdk.EntityReference Moniker2 = new Microsoft.Xrm.Sdk.EntityReference();

            Moniker2.Id = guest.Id;

            Moniker2.LogicalName = "contact";//Entity Name


            // Code Create Moniker for second Entity: New_CustomEntity

            Microsoft.Xrm.Sdk.EntityReference Moniker1 = new Microsoft.Xrm.Sdk.EntityReference();

            Moniker1.Id = emailSend.Id;

            Moniker1.LogicalName = "cdi_emailsend";

            tracingService.Trace("Started Assosiating");

            AssociateManyToManyEntityRecords(service, Moniker1, Moniker2, "cdi_emailsend_contact");

            tracingService.Trace("Completed Assosiating");

        }


        private bool AssociateManyToManyEntityRecords(IOrganizationService service, Microsoft.Xrm.Sdk.EntityReference moniker1, Microsoft.Xrm.Sdk.EntityReference moniker2, string strEntityRelationshipName)

        {

            try

            {

                AssociateRequest request = new AssociateRequest();

                request.Target = moniker1;

                request.RelatedEntities = new EntityReferenceCollection { moniker2 };

                request.Relationship = new Relationship(strEntityRelationshipName);


                // Execute the request.

                service.Execute(request);



                return true;

            }

            catch (Exception exp)

            {

                return false;

            }

        }

    }

}








Thursday, December 15, 2016

Update the fetchXML that will be used by the Sub- grid in Dynamics CRM

///<reference path="XrmPage-vsdoc.js"/>
function UpdateSubGrid()
{
 window.scroll(0,0);
 var rereferralsGrid = document.getElementById("rereferrals");
 var lookupfield = new Array;
 lookupfield = Xrm.Page.getAttribute("ftd_familymember").getValue();
 intakeid = Xrm.Page.getAttribute("safy_intakeid").getValue();

 if (lookupfield != null)
 {
     var lookupid = lookupfield[0].id;
 }

else
 {
    return;
 }


if (rereferralsGrid ==null || rereferralsGrid.readyState != "complete")
{
    setTimeout('UpdateSubGrid()', 2000);
    return;
}   

 //Update the fetchXML that will be used by the grid.
 var fetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" 
 fetchXml += "<entity name='ftd_intakeassessment'>";
 fetchXml += "<attribute name='safy_intakeassessment'/>";
 fetchXml += "<attribute name='ftd_intakeassessmentid' />";
 fetchXml += "<attribute name='safy_intakeid' />";
 fetchXml += "<attribute name='safy_ica_status_reason' />";
 fetchXml += "<attribute name='safy_ica_status' />";
 fetchXml += "<attribute name='safy_ica_provider_worker_name' />";
 fetchXml += "<attribute name='safy_ica_name' />";
 fetchXml += "<attribute name='safy_ica_intake_office' />";
 fetchXml += "<attribute name='safy_familymembername' />";
 fetchXml += "<attribute name='safy_ica_assess_supervisor' />";
 fetchXml += "<order attribute='safy_intakeid' descending='false' />";
 fetchXml += "<filter type='and'>";
 fetchXml += "<condition attribute='safy_initialassessment' operator='eq' value='1' />";
 fetchXml += "<condition attribute='safy_intakeid' operator='ne' value='"+ intakeid +"' />"
 fetchXml += "<condition attribute='ftd_familymember' operator='eq' uiname='Family Member' uitype='ftd_familymember' value='" + lookupid + "' />";
 fetchXml += "</filter>";
 fetchXml += "</entity>";
 fetchXml += "</fetch>";

 rereferralsGrid.control.SetParameter("fetchXml", fetchXml);
 rereferralsGrid.control.refresh();
}

Wednesday, December 14, 2016

Formats a postal code & Sets a lookup field in Javascript

function formatPostalcode(pcode) {
    var regexObj = /^\s*([a-ceghj-npr-tvxy]\d[a-ceghj-npr-tv-z])(\s)?(\d[a-ceghj-npr-tv-z]\d)\s*$/i
    if (regexObj.test(pcode)) {
        var parts = pcode.match(regexObj);
        var pc = parts[1] + " " + parts[3];
        return pc.toUpperCase();
    }
    else {
        return pcode;
    }
}



function SetLookup(fieldName, idValue, textValue, typeValue) {
    var value = new Array();
    value[0] = new Object();
    value[0].id = idValue;
    value[0].name = textValue;
    value[0].typename = typeValue;

    setDisabledField(fieldName, value);
}

Gets the current user and enters string into specified field

function loggedUser(field) {
    var user = Xrm.Page.context.getUserId();
    var userId = user.substring(1, 37);
    var serverUrl = document.location.protocol;
    serverUrl += "//" + document.location.host;
    serverUrl += "/" + Xrm.Page.context.getOrgUniqueName();
    var ODATA_ENDPOINT = "/XRMServices/2011/OrganizationData.svc";
    var ODATA_EntityCollection = "/SystemUserSet";
    var ODATA_Query = "(guid\'" + userId + "')";
    var ODATA_Final_url = serverUrl + ODATA_ENDPOINT + ODATA_EntityCollection + ODATA_Query;

    $.ajax({
        type: "GET",
        contentType: "application/json; charset=utf-8",
        datatype: "json",
        url: ODATA_Final_url,
        beforeSend: function (XMLHttpRequest) {
            XMLHttpRequest.setRequestHeader("Accept", "application/json");
        },
        success: function (data, textStatus, XmlHttpRequest) {
            var userName = data.d.FullName;
            Xrm.Page.getAttribute(field).setSubmitMode("always");
            Xrm.Page.getAttribute(field).setValue(userName);
        },
        error: function (XmlHttpRequest, textStatus, errorThrown) {
            alert('Error: ' + ODATA_Final_url);

        }
    });

}

Checks to see if the user has a particular role in Dynamics CRM

//  UserHasRole(roleName)           Checks to see if the user has a particular role
function UserHasRole(roleName) {
    var oXml = GetCurrentUserRoles();
    if (oXml != null)
    {
        var roles = oXml.selectNodes("//BusinessEntity/q1:name");
        if (roles != null)
        {
            for (i = 0; i < roles.length; i++)
            {
                if (roles[i].text == roleName)
                {
                    return true;
                }
            }
        }
    }
    return false;
}

//  GetCurrentUserRoles()           Gets user's security roles
function GetCurrentUserRoles() {
    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>role</q1:EntityName>" +
     " <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" +
     " <q1:Attributes>" +
     " <q1:Attribute>name</q1:Attribute>" +
     " </q1:Attributes>" +
     " </q1:ColumnSet>" +
     " <q1:Distinct>false</q1:Distinct>" +
     " <q1:LinkEntities>" +
     " <q1:LinkEntity>" +
     " <q1:LinkFromAttributeName>roleid</q1:LinkFromAttributeName>" +
     " <q1:LinkFromEntityName>role</q1:LinkFromEntityName>" +
     " <q1:LinkToEntityName>systemuserroles</q1:LinkToEntityName>" +
     " <q1:LinkToAttributeName>roleid</q1:LinkToAttributeName>" +
     " <q1:JoinOperator>Inner</q1:JoinOperator>" +
     " <q1:LinkEntities>" +
     " <q1:LinkEntity>" +
     " <q1:LinkFromAttributeName>systemuserid</q1:LinkFromAttributeName>" +
     " <q1:LinkFromEntityName>systemuserroles</q1:LinkFromEntityName>" +
     " <q1:LinkToEntityName>systemuser</q1:LinkToEntityName>" +
     " <q1:LinkToAttributeName>systemuserid</q1:LinkToAttributeName>" +
     " <q1:JoinOperator>Inner</q1:JoinOperator>" +
     " <q1:LinkCriteria>" +
     " <q1:FilterOperator>And</q1:FilterOperator>" +
     " <q1:Conditions>" +
     " <q1:Condition>" +
     " <q1:AttributeName>systemuserid</q1:AttributeName>" +
     " <q1:Operator>EqualUserId</q1:Operator>" +
     " </q1:Condition>" +
     " </q1:Conditions>" +
     " </q1:LinkCriteria>" +
     " </q1:LinkEntity>" +
     " </q1:LinkEntities>" +
     " </q1:LinkEntity>" +
     " </q1:LinkEntities>" +
     " </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", xml.length);
    xmlHttpRequest.send(xml);

    var resultXml = xmlHttpRequest.responseXML;
    return (resultXml);
}

Get files of last hour in Azure Data Factory

  Case I have a Data Factory pipeline that should run each hour and collect all new files added to the data lake since the last run. What is...