Update Date fields - CRM 2016 Online

(0) Share

Report

Posted on by Kishor Kumar

3,710

Hey,

I want to update the date fields in lead entity based on the lead id, I have the lead id & Dates in the excel suggest me the best way to update those fields except using the import wizard method.

Shall I go with console app? Then guide me to update using the console & read the data from excel too

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Sort by

Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Refer this:

blogs.msdn.microsoft.com/.../data-integrationmigration-using-sql-integration-services-ssis-2008

Was this reply helpful? Yes No
Suggested answer

Rajkumar Rajaraman on at

Like (0)

Report
Copy link

Link copied!

Hi Kishor,

Walkthrough the following link:

www.inogic.com/.../updating-date-values-of-existing-records-after-modifying-the-datetime-behaviour-in-dynamics-crm-online-update-1

Hope this helps.

Regards,

Rajkumar Rajaraman

Was this reply helpful? Yes No
Suggested answer

Nithya Gopinath 17,078 on at

Like (0)

Report
Copy link

Link copied!
Hi Kishor Kumar,

You could write a console application to achieve this task.

Step 1: Retrieve all the lead records in dynamics CRM with the lead ID and date field using Query Expression.

Step 2: Read the excel file.

Step 3: Find the date value corresponding to the lead id from excel file, set it to the date field value and update the lead record.

Try the following code.

class Program { static void Main(string[] args) { { QueryExpression qe = new QueryExpression(); qe.EntityName = "lead"; qe.ColumnSet = new ColumnSet(); qe.ColumnSet.Columns.Add("leadid", "datefieldname"); EntityCollection ec = _orgService.RetrieveMultiple(qe); DataTable dt = new DataTable(); string fileName = "Your File Name"; string fileExt = ".xslx"; dt = ReadExcel(fileName, fileExt); foreach (Entity act in ec.Entities) { int datefield = (from DataRow dr in dt.Rows where (string)dr["leadid"] == act["leadid"] select (Date)dr["date"]).FirstOrDefault(); act["datefield"] = new Date(datefield); service.Update(act); } } } public DataTable ReadExcel(string fileName, string fileExt) { string conn = string.Empty; DataTable dtexcel = new DataTable(); if (fileExt.CompareTo(".xls") == 0) conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007 else conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007 using (OleDbConnection con = new OleDbConnection(conn)) { try { OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1 oleAdpt.Fill(dtexcel); //fill excel data into dataTable } catch { } } return dtexcel; } }

Hope this helps.

Was this reply helpful? Yes No
Kishor Kumar 3,710 on at

Like (0)

Report
Copy link

Link copied!

Hi Nithya Thanks for the Reply!

I will check it!

Was this reply helpful? Yes No
Kishor Kumar 3,710 on at

Like (0)

Report
Copy link

Link copied!
Nithya,

I have tried to retrieve the data as per the link

http://www.c-sharpcorner.com/blogs/retrieve-data-from-excel-file-using-c-sharp1 and able get the data table filled.

Could you please explain this part,

foreach (Entity act in ec.Entities) { int datefield = (from DataRow dr in dt.Rows where (string)dr["leadid"] == act["leadid"] select (Date)dr["date"]).FirstOrDefault(); act["datefield"] = new Date(datefield); service.Update(act); }
Whatis act?

Was this reply helpful? Yes No
Suggested answer

Nithya Gopinath 17,078 on at

Like (1)

Report
Copy link

Link copied!
Hi Kishor,

Please see the comments.

foreach (Entity act in ec.Entities) //For each all the lead records appearing in the result set of Query expression { int datefield = (from DataRow dr in dt.Rows where (string)dr["leadid"] == act["leadid"] select (Date)dr["date"]).FirstOrDefault(); //Retrieving the date field from the data table corresponding to the lead id of each lead record act["datefield"] = new Date(datefield); //Changing the date field of lead record to the date fetched from excel orgService.Update(act); //Update the lead record }

Was this reply helpful? Yes No
Kishor Kumar 3,710 on at

Like (0)

Report
Copy link

Link copied!
Was this reply helpful? Yes No