What all methods do we need while updating data to the CRM database? We can use CreateRecord, FindRecord or either simple Update query and ExecSql can also update the data in CRM database as per our need. Now the hitch is always when the date field comes into picture. Consider the scenario, you have created entry screen and this screen has the date field in it. Now if you are using standard container.execute command there will be no problem in saving date value to table, however if you are manually updating the date value in the recordset found using FindRecord method it needs some more things to be taken care of. Well in this case we need to pass the exact compatible formatted date value to date field before applying save changes. To do the same you can follow some easy steps below.
1. The idea is in getting date in mm/dd/yyyy format as the date value in date field comes as per the user date format.
2. For this first of all we need to find the date format for current logged in user. Below post of mine on this blog site will help you to achieve the same.
http://sagecrmaddons.blogspot.com/2009/02/retrieve-current-logged-in-users-date.html
3. Suppose here the date format is set as dd/mm/yyyy, so on screen we will get the date in same format. E.g. 23March2010 will look like 20/03/2010 in date field on screen.
4. Now if we use Request.Form method to retrieve the value from form, the value we will get is 20/03/2010 i.e. in dd/mm/yyyy format.
5. Now as we know that the user date format is dd/mm/yyyy we can convert the date value collected from the form into string and the split it using the appropriate separator as that of the date format i.e. slash or dot etc.
6. Splitting the date string will get us the 3 different values always. Now we just need to identify what is the date, which one is month and which one is year.
7. Based on the date format of the user we can retrieve 3 values needed to format date as explained above.
8. Then we can concatenate these 3 parameters in string to get the string in mm/dd/yyyy format.
9. Now this date has to be converted to UTC date which can be done as follows.
Var Newdate = new Date( );
Newdate = Newdate.getVarDate();
10. You can assign the Newdate found above to recordset column while saving the data using CreateRecord/FindRecord and SaveChanges method.
Doing this for every date field will be quite cumbersome task and hence you can write a global function to carry out this task where you can pass the form date value to get the database compatible value as output. However if we are updating using update query, we just need to arrange the string in mm/dd/yyyy format and pass it in query. Rest all will be handled by SQL itself.