Display Gannt Chart in a web page using @Formula

Display schedules in a gannt chart using @formula.

This formula works based on schedule start date, end date and duration values.

These values can be populated using @Dblookup from a view or using lotus script agent. This example uses @DbLookup.



Schedule information input:

- A View with name "Tasks" (you can change the view name in Input_data field formula)

- 1st column Hidden (Schedule_StartDate, Ascending order sorted)

- 2nd column : Task Name ( required)

- 3rd column : Schedule_StartDate (required)

- 4th column : Schedule_EndDate (required)

- 5th column : Duration ( required)

- 6th column : Assigned To (optional)

- 7th column : Document Unique ID (used to display URL href link)



1.Create a form named Gannt Chart or something

2.Create a computed field Input_data or anyname

This field got schedule information input and other reference fields

code:

ViewName:="Tasks";

REM {Input data, add error conditions if required};

FIELD tasknames:=@DbColumn("":"";"":"";ViewName;2);

FIELD taskstartdate:=@DbColumn("":"";"":"";ViewName;3);

FIELD taskenddate:=@DbColumn("":"";"":"";ViewName;4);

FIELD taskduration:=@DbColumn("":"";"":"";ViewName;5);

FIELD resources:=@DbColumn("":"";"":"";ViewName;6);

FIELD docids:=@DbColumn("":"";"":"";ViewName;7);



FIELD DbPath:=@ReplaceSubstring(@Subset(@DbName; -1); " " : "\\"; "+" : "/");



REM {calculate first day of the schedule start date month};

FIELD schedule_startdate:=@ToTime(@Min(taskstartdate));

FIELD schedule_startdate:=@ToTime(@Text(@Month(schedule_startdate))+"/01/"+@Text(@Year(schedule_startdate)));



REM {calculate last day of the schedule end date month};

FIELD schedule_enddate:=@Max(taskenddate);

schedule_enddate:=@ToTime(@Text(@Month(schedule_enddate))+"/01/"+@Text(@Year(schedule_enddate)));

schedule_enddate:=@Adjust(@Adjust(schedule_enddate;0;1;0;0;0;0);0;0;-1;0;0;0);



REM {Total days between firstday and last day of the schedule dates};

FIELD totaldays:=((schedule_enddate-schedule_startdate)/86400)+1;

tmpdate1:=schedule_startdate;



FIELD schedule_months:="";

@While(tmpdate1<= schedule_enddate; tmpdate1:=@Adjust(tmpdate1;0;1;0;0;0;0); @Set("schedule_months";schedule_months:@Text(@Date(@Adjust(tmpdate1;0;0;-1;0;0;0)))) ); schedule_months:=@Trim(schedule_months); FIELD schedule_months:=schedule_months; schedule_months:=@ToTime(schedule_months); schedule_months1:=@Text(@Month(schedule_months))+"/01/"+@Text(@Year(schedule_months)); FIELD fulldays:=@Explode(@ToTime("["+schedule_months1+" - "+@Text(schedule_months)+"]")); REM {Reference fields}; FIELD monthlist:="January":"February":"March":"April":"May":"June":"July":"August":"September":"October":"November":"December"; FIELD weekdays:="S":"M":"T":"W":"T":"F":"S"; ""



3.Create a computed text and enable pass thru HTML code:





schedule_bgcolor:="#FFD39B";

weekend_bgcolor:="#cccccc";



REM {Populate month names};

schedule_months:=@ToTime(schedule_months);

tmp1:="<th colspan="+@Text(@Day(schedule_months))+" align=center>"+@Transform(@Month(schedule_months);"x";monthlist[x])+", "+@Text(@Year(schedule_months))+"</th>";

month_html:="<tr><th rowspan=3 class=\"locked\">Task Name</th>"+@Implode(tmp1)+"</tr>";



REM {Populdate Weekday title};

tmp:=@Date(@ToTime(fulldays));

weekdays1:=@Transform(@Weekday(tmp);"x";weekdays[x]);

weekends:=@Replace(weekdays1;"S";"bgcolor=\""+weekend_bgcolor+"\"");

tmp1:="<th "+weekends+">"+weekdays1+"</th>";

weekday_html:="<tr>"+@Implode(tmp1)+"</tr>";



REM {Populdate days};

tmp:=@Day(@ToTime(fulldays));

tmp2:="<th "+weekends+">"+@Replace(@Text(tmp);"1":"2":"3":"4":"5":"6":"7":"8":"9";"01":"02":"03":"04":"05":"06":"07":"08":"09")+"</th>";

days_html:="<tr>"+@Implode(tmp2)+"</tr>";



REM {Populate Tasks};

html_td:="<td "+weekends+"> </td>";

task_html:=task_html;

@For(n :=1; n<=@Elements(tasknames); n:= n + 1; title:=tasknames[n]; firstday:=((@ToTime(taskstartdate[n])-@ToTime(schedule_startdate))/86400)+1; nodays:=@ToNumber(taskduration[n]); hlinktitle:=@If(@Length(title)>nodays*4;@Left(title;nodays*4)+"..";title);

hlink:="<a href=/"+DbPath+"/0/"+docids[n]+"?opendocument>"+hlinktitle+"</a>";

task_html:= task_html+"<tr><td nowrap class=\"locked\">"+" "+@If(@Length(title)>40;@Right(title;40);title)+"</td>"+@If(firstday>1;@Implode(@Subset(html_td;firstday-1));"")+"<td colspan="+@Text(nodays)+" bgcolor="+schedule_bgcolor+"><center>"+hlink+"</center></td>"+@Implode(@Subset(html_td;(firstday+nodays-1)-totaldays))+"</tr>"

);



"<table cellpadding=0 cellspacing=0>"+month_html+weekday_html+days_html+task_html+"</table>"





4. Adding style to gannt chart (optional)

- this style sheet freezes the column header (month names, days) and Task name.

4.1 Place the gannt chart computed text inside a div tag with id "ViewBody"



<div id="ViewBody">

<computed value>

</div>





4.2. Create a css style sheet page (ie:style.css)



div#ViewBody {

width: 90%;

height: 300px;

overflow: auto;

scrollbar-base-color:#999;

}



div#ViewBody table th {

width: 100px;

position:relative;

top: expression(document.getElementById("ViewBody").scrollTop-1);

z-index: 30;



}

ViewBody th.locked {

font-size: 10px;

color: black;

background-color: lightblue;

border-right: 2px solid silver;

position: relative;

cursor: default;

left: expression(parentNode.parentNode.parentNode.parentNode.scrollLeft-2);

z-index: 40;

}



div#ViewBody td.locked {

font-size: 12px;

color: black;

background-color: #efefef;

border-right: 2px solid silver;

border-bottom: 1px solid silver;

WHITE-SPACE: nowrap;

left: expression(parentNode.parentNode.parentNode.parentNode.scrollLeft-2);

position: relative;

z-index: 10;

}

div#ViewBody td {

font-size: 12px;

color: #c0c0c0;

border-top: #bbbbbb 0px solid; border-right: #bbbbbb 1px solid;

border-bottom: #bbbbbb 0px; border-left: #bbbbbb 1px;

padding: 1px;

}



div#ViewBody th {

font-size: 10px;

color: black;

padding:4px;

text-align: center;

background-color: #efefef;

border-top: #bbbbbb 0px solid; border-right: #bbbbbb 1px solid;

border-bottom: #bbbbbb 1px solid; border-left: #bbbbbb 1px;

}

div#ViewBody a {

font-size: 10px;

TEXT-DECORATION: none

}



4.3. Use this style sheet in Gannt Chart form

GANNT CHART COMPONENT DATABASE DOWNLOAD LINK