Convert entity relationship type with data. (n:1 to n:n)

You are always recommended to determine all your relationships in between the entities prior to implementation of CRM. Anyhow, when it comes to practice you will face a lot of exceptions. In most cases Customer/Business analyst could have a need to change certain relationships due to change of requirements. One common change would be to have a need of a many-to-many relationship instead one many –to-one.

Will take a scenario; If a contact can participate in an event, simply we would create a n:1 relationship in contact. In fact, one can simply fill the lookup shown in the contact form. In the db, you will have an extra field to hold the relevant event id (i.e. new_eventcontactid) in contact enity.

Now, if it is required that contact to be able to participate for more than one event, current solution is not suitable. Then you will be asked to convert current relationship to a n:n relationship. How you do it without losing the data?

Create an n:n relationship in between Contact and Event (actually new_event). If the relationship name is new_event_project, you will find a table in the SQL with the same name that holds the relationships. Actually the primary keys of the related entity instances. Now you will need to fill this tables with the data in previous n:1 table as below t-sql script does.

insert into new_event_project (new_contact_eventid,contactid,new_eventid)
select NEWID(),ContactId,new_eventcontactid 
from contact
where new_eventcontactid is not null

Now you can delete the n:1 relationship in contact entity.

DateFormats and Ascentium CrmService

Playing around with DateTimes seems quite hectic to me. Specially, when you work in client side with Ascentium CrmService, it becomes tricky since you need to do your operations in usual DateTime format available in JavaScript and convert them from/to methods in the service.

For example, if you need some DateTime operation done in your logic using JavaScript, for some value comes from a Fetch result, first you need to convert them to a DateTime field. Fetch returns strings. There is no simple way for that but to do it with some string operations as below.

//convert DATE String (2008-01-01T00:00:00) format 
//to DATE (Tue Aug 23 07:00:00 UTC+1000 2011)
DateFormatFromAttributeValue = function(DateStr)
{
    var yearStr, YearStr, DayStr, MonthStr, HourStr, MinStr, NewDateStr;
    yearStr = DateStr.substr(0, 4);
    MonthStr = String(DateStr.substr(5, 2) - 1); //Month goes as 0-11
    DayStr = DateStr.substr(8, 2);
    HourStr = DateStr.substr(11, 2);
    MinStr = DateStr.substr(14, 2);

    return new Date(yearStr, MonthStr, DayStr, HourStr, MinStr, 0);
}

It is interesting to see that you get three string values from the Fetch as below.

My advice is to use “value” field for all your operations even if you need just the date. I am telling this because, date string is not consistent with the length which could drag you to a mess when handling. If you see carefully, in above example, date is shown as “2” not “02”. In fact, number of characters in the string could be different when date is less than 10th day and greater than 10th Day. This should be a big concern when string functions are being used for conversions.

When you need to submit a DateTime, you got to convert it back to the previous string format (i.e. 2008-01-01T00:00:00). I use below method for that.

//convert DATE (Tue Aug 23 07:00:00 UTC+1000 2011) 
//to DATE String (2008-01-01T00:00:00) format
DateFormatPassToService = function(DateValue)
{
  var YearStr, DayStr, MonthStr, HourStr, MinStr, NewDateStr;
  YearStr = DateValue.getFullYear();
  MonthStr = String(parseInt(DateValue.getMonth()) + 1); //Month goes as 0-11
      if (MonthStr.length == 1) { MonthStr = '0' + MonthStr; }
  DayStr = String(DateValue.getDate());
      if (DayStr.length == 1) { DayStr = '0' + DayStr; }
  HourStr = String(DateValue.getHours());
      if (HourStr.length == 1) { HourStr = '0' + HourStr; }
  MinStr = String(DateValue.getMinutes());
      if (MinStr.length == 1) { MinStr = '0' + MinStr; }
  return (YearStr + '-' + MonthStr + '-' + DayStr + 'T' + HourStr + ':' + MinStr + ':00');
}

Hope this will help you.

Calculate business days in JavaScript (with variable business days per week)

There are lot of ways to calculate working days. Anyway, it was not easy to find a method to calculate business days if the number of working days per week is being changed. By default, working days will be Monday to Friday, but in some cases you might need to calculate days including Saturday or both Saturday and Sunday.

In my case it was required to calculate the cost of hire when renting equipments. They are to be charged according to the days they are being used. Some factories could work just 5 days while some work for 6 or 7 days per week.

I couldn’t find any method in the web to do it in client side. Below is the only way I could think of. Actually hint was given by one of the colleagues.

calcBusinessDaysExtended = function (Start, End, DaysPerWeek)
{
    Start.setHours(0);
    End.setHours(0);

    _StartUTCmilli = Date.UTC(Start.getFullYear(), Start.getMonth(), Start.getDate());
    _EndUTCmilli = Date.UTC(End.getFullYear(), End.getMonth(), End.getDate());
    var _Days = Math.floor((_EndUTCmilli - _StartUTCmilli) / 86400000) + 1;

    var _Saturdays = 0;
    var _Sundays = 0;
    var _bdays = 0;

    var nextDatOfEnd = new Date(End.setDate(End.getDate() + 1));
    for (i = Start; i < nextDatOfEnd; i.setDate(i.getDate() + 1))
    {
        if (i.getDay() == 6) { _Saturdays = _Saturdays + 1; }
        if (i.getDay() == 0) { _Sundays = _Sundays + 1; }
    }

    switch (DaysPerWeek)
    {
        case 5:
            _bdays = _Days - (_Saturdays + _Sundays);
            break;
        case 6:
            _bdays = _Days - _Sundays;
            break;
        default:
            _bdays = _Days;
    }
    return _bdays;
}

One can think, loops could affect the efficiency, but it is not really matters in client side work. I am happy if someone can provide me a better one.. Please find the relevant c# code here.

Difference between completing and cancelling an appointment – part 2

In my previous post (read), I suggested a method to execute a JavaScript for completion of an appointment. This method works fine, but not for one instance. If you carefully watch the tool bar of an appointment, you will notice a button for “Save as Completed”. This is a fast way of completing the appointment. Our previous method doesn’t fire for this click since event.Mode is not 5 for this, but 58.

For completion of the Appointment, We need to modify the code as below, which will work for both menu and button.

var _eventMode = event.Mode;
    if (_eventMode == 5)
    {
        var _status = document.getElementById('newStatusCode').value;
    }

    if ((_eventMode == 58) || ((_eventMode == 5) && (_status == 3)))
    {
       //code to work when completing
    }