Hi Pawel Gradecki, Nithya Gopinath, Alagu nellaikumar.S and all
1. I have a table name "aw_mailchimp". I want to retrieve axp_axpulsemailchimpid and aw_listid from aw_mailchimp.
2. I also have table name "list". I want to retrieve listid and createdfromcode from list.
3. I also have a table name "listmember". I want to retrieve listid and entityid from listmember.
4. I also have a table name "lead". I want to retrieve leadid, firstname, lastname and emailaddress1 from lead.
Relational diagram is present over there
I am using this code but not able to fulfill my requirement. Please help me in making this situation
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;// use for web exception
using System.IO; // use for StreamReader
using System.Threading.Tasks;
using Newtonsoft.Json;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Workflow;
using System.Activities;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Messages;
namespace CreateListMembers
{
public class ListMembers : CodeActivity
{
protected override void Execute(CodeActivityContext context)
{
//string subscriberEmail = "abdulwahabubit@outlook.com";
string dataCenter = "u4";
string apiKey = "ab15c4d1322ca886a92e5a";
string firstname = "";
string lastname ="";
string emailaddress1 ="";
string listid = "";
ITracingService tracingService = context.GetExtension<ITracingService>();
IWorkflowContext workflowContext = context.GetExtension<IWorkflowContext>();
IOrganizationServiceFactory serviceFactory = context.GetExtension<IOrganizationServiceFactory>();
IOrganizationService service = serviceFactory.CreateOrganizationService(workflowContext.UserId);
//<>
//Create a query expression specifying the link entity alias and the columns of the link entity that you want to return
QueryExpression qe = new QueryExpression();
qe.EntityName = EntityName.mailchimpList;
qe.ColumnSet = new ColumnSet();
qe.ColumnSet.AddColumns(mailchimpListAttributes.aw_mailchimpId, mailchimpListAttributes.aw_listid, mailchimpListAttributes.aw_name, mailchimpListAttributes.aw_FromName);
qe.LinkEntities.Add(new LinkEntity(EntityName.mailchimpList, EntityName.list, mailchimpListAttributes.aw_mailchimpId, listAttributes.listid, JoinOperator.Inner));
qe.LinkEntities[0].Columns.AddColumns(listAttributes.listid, listAttributes.createdfromcode);
qe.LinkEntities[0].EntityAlias = "memberType";
EntityCollection ec = service.RetrieveMultiple(qe);
//Console.WriteLine("Retrieved {0} entities", ec.Entities.Count);
foreach (Entity MailChimplList in ec.Entities)
{
//Console.WriteLine("account name:" + act["name"]);
Entity contact = new Entity("contact");
contact["firstname"] = MailChimplList[mailchimpListAttributes.aw_name];
//Console.WriteLine("primary contact first name:" + act["primarycontact.firstname"]);
contact["lastname"] = MailChimplList[mailchimpListAttributes.aw_FromName];
//Console.WriteLine("primary contact last name:" + act["primarycontact.lastname"]);
//contact["lastname"] = "current record";
Guid contactId = service.Create(contact);
}
//<>
//</>
qe.LinkEntities.Add(new LinkEntity(EntityName.list, EntityName.listmember, listAttributes.listid, listMemberAttributes.listid, JoinOperator.Inner));
// sooposition for error
//qe.LinkEntities[0].Columns.AddColumns(listMemberAttributes.listid, listMemberAttributes.entityid);
qe.LinkEntities[1].Columns.AddColumns(listMemberAttributes.listid, listMemberAttributes.entityid);
qe.LinkEntities[1].EntityAlias = "memberType";
EntityCollection ec = service.RetrieveMultiple(qe);
foreach (Entity memberType in ec.Entities)
{
var MemberType = memberType["memberType.createdfromcode"].ToString();
listid = memberType["memberType.aw_listid"].ToString();
if (MemberType == "4")
{
qe.LinkEntities.Add(new LinkEntity(EntityName.lead, EntityName.listmember, listMemberAttributes.entityid, leadAttributes.leadid, JoinOperator.Inner));
qe.LinkEntities[2].Columns.AddColumns(leadAttributes.leadid, leadAttributes.firstname, leadAttributes.lastname, leadAttributes.emailaddress1);
qe.LinkEntities[2].EntityAlias = "mailchimpList";
EntityCollection ecc = service.RetrieveMultiple(qe);
foreach (Entity MailChimpList in ecc.Entities)
{
firstname = MailChimpList["mailchimpList.firstname"].ToString();
lastname = MailChimpList["mailchimpList.lastname"].ToString();
emailaddress1 = MailChimpList["mailchimpList.emailaddress1"].ToString();
var sampleListMember = JsonConvert.SerializeObject(
new
{
email_address = emailaddress1,
merge_fields =
new
{
FNAME = firstname,
LNAME = lastname
},
status_if_new = "subscribed"
});
var hashedEmailAddress = string.IsNullOrEmpty(emailaddress1) ? "" : CalculateMD5Hash(emailaddress1.ToLower());
var urii = string.Format("https://{0}.api.mailchimp.com/3.0/lists/{1}/members/{2}", dataCenter, listid, hashedEmailAddress);
try
{
using (var webClient = new WebClient())
{
webClient.Headers.Add("Accept", "application/json");
webClient.Headers.Add("Authorization", "apikey " + apiKey);
webClient.UploadString(urii, "PUT", sampleListMember);
//Console.ReadLine();
}
}
catch (WebException we)
{
using (var sr = new StreamReader(we.Response.GetResponseStream()))
{
Console.WriteLine(sr.ReadToEnd());
}
}
}
}
}
}
private static string CalculateMD5Hash(string input)
{
// Step 1, calculate MD5 hash from input.
var md5 = System.Security.Cryptography.MD5.Create();
byte[] inputBytes = System.Text.Encoding.ASCII.GetBytes(input);
byte[] hash = md5.ComputeHash(inputBytes);
// Step 2, convert byte array to hex string.
var sb = new StringBuilder();
foreach (var @byte in hash)
{
sb.Append(@byte.ToString("X2"));
}
return sb.ToString();
}
}
public static class EntityName
{
public const string mailchimpList = "aw_mailchimp";
public const string list = "list";
public const string listmember = "listmember";
public const string lead = "lead";
}
public static class mailchimpListAttributes
{
public const string aw_mailchimpId = "aw_mailchimpid";
public const string aw_listid = "aw_listid";
public const string aw_name = "aw_name";
public const string aw_FromName = "aw_fromame";
}
public static class listAttributes
{
public const string listid = "listid";
public const string createdfromcode = "createdfromcode";
}
public static class listMemberAttributes
{
public const string listid = "listid";
public const string entityid = "entityid";
}
public static class leadAttributes
{
public const string leadid = "leadid";
public const string firstname = "firstname";
public const string lastname = "lastname";
public const string emailaddress1 = "emailaddress1";
}
}
Thank You
