Thursday, September 24, 2009

QuickBase surprises calculating working time

QuickBase is one of the most mature products of the online database market. One can think with years of experience and such a huge client base there is no issue the system can’t solve. That’s why it was quite a surprise that the task to calculate working time with working days and hours was a hard nut to crack.

This example is prepared with Kirk Trachys help and I’d really like to thank for the assistance. He commented on the implementation peculiarities of End Date (Test 1) calculation taking into account hours of operation and holidays in QuickBase:
"QuickBase has some native field types and formula functions that address the calculations of dates, workdates and weekdays. Many calculations can be as simple as: WeekDayAdd([Start Date],[Business Days]). Where this takes a start date and you add the number days to it and it only calculates for Monday through Friday days. The result will jump over weekends and resume the next week if necessary. In the calculations below we created a field to accept a Start Date (01-01-2009) and another to accept the number of hours a task might take (120). Since each weekday was 8 hours we calculated the number of days by making them divisible by 8 hours resulting in (15) Business Days."


"Now that we have calculated the business days we need to account for the holiday schedule so we add the holiday dates (see table below) and then calculated if there were any holiday dates that should be added to the calculation. We calculate this by totaling how many times a holiday is present between the [Start Date] and the [Start Date Plus Business Work Days]. This holiday count is added to the business days and applied as a total to the WeekDayAdd function. The End Date then reflects both days based on hours as well as any holidays. Below is the same form as above but with the formulas displayed as text to the right of each field:"


"All of this was calculated in one table. When calculating date ranges that bridge over multiple year periods one will want to use a relationship to multiple records."


"QuickBase provides over 160 formula functions and operators with six SDKs and an open HTTP XML API."

Conclusions

From Kirks description and the app we can see "Hours of Operation" info is not used during the calculation. So I assume, variable hours of operations and the weekends different from Saturday and Sunday can't be implemented with built-in QuickBase functionality.

Even though Kirk didn’t manage to calculate duration between two timestamps (Test 2) I think it’s possible in QuickBase, but only in case we always have 8 hours working day with Saturday and Sunday as weekends.

The situation is the same as with TrackVia: WeekDayAdd function can’t be applied worldwide and count the data as needed and built-in QuickBase functionality doesn’t allow to implement this calculation without it, using hours of operation info.

Unfortunately 160 formula functions and operators can’t help you to calculate working time and you will have to do some coding through one of six SDKs or HTTP XML API.

Monday, September 21, 2009

Are you a fan of flow charts? The way PerfectForms calculate working time.

If you like to create flow charts and define properties take a closer look at PerfectForms. The same Adobe Air platform Coghead used with similar in many respects ideology built around the form that lets you implement pretty complicated business concept.

I really appreciate the help of Paula Selvidge and the example she prepared.
This is how the result of working time calculation looks like in PerfectForms:


Paula commented on the results of implementation methods of Filter weekends and Filter holidays when calculating total available time.

Filter Weekends
"I have employed two ways to address weekends in this application. The first way is via the function Date Difference in the Formula Editor using this function one chooses the start and finish date. Then they choose the unit value (h for hours, m for minutes, d for days). If you type in "wd" (weekdays) for the value you will filter out weekends from the day count."


"The second way used a Simple Branch behavior that checked if the word "Saturday" or "Sunday" matched the date being examined. I used the Weekday Name function in the Formula Editor to return the name of the date being examined and I simply checked to see if it was on a Saturday or Sunday."


Filter Holidays

"Since observed holidays vary from country to country and business to business I decided to manually create lists of the holidays that would be checked during the form operation. Like the weekend solution, I have employed two ways to resolve the problem.The first solution uses a Multiline Text Input box with the holiday dates to check. This field is hidden on the form."


"The second solution that I also employed was specific to the working time calculation. Where we set the beginning and end date for a project and get the workable hours. In this solution, I created hidden date fields with the holiday dates inserted."


"Next the form would check if any of these values existed within the dates selected. When it checks for Christmas it would look like this:"


"Checking if the date I labeled as Christmas was within or on the dates entered."

This is how form customization looks in PerfectForms:


This is the algorithm (Behavior as they call it in PerfectForms) of what happens by pressing "Until Completion" button to calculate Working Days and Hours of Work:


And by pressing "End Date" button to calculate Days of Completion we have the following algorithm:


Conclusions:

As you can see Behaviors are just another way of programming through flow charts. To be honest I am not a fan of such a method for I think this way is more cumbersome if compare it with a text method. Besides, it’s more difficult to see complete app logic picture as each particular behavior distorts it. On the other hand "behavior" provides users with some flexibility so you can program business logic and calculations of the form.

Friday, September 11, 2009

TeamDesk calculates work time without any coding

I am not sure who should take credit for that - MS SQL server itself, or was it TeamDesk’s natural ability to perform calculations, but TeamDesk almost completely managed to implement "end date and duration calculation" task, accounting working time and holidays, while using only a built-in functionality without any coding.

First of all, special thanks to Kirill Bondar from TeamDesk for implementing a sample application and explaining its functionality in details.

Here is the sample data which is going to be used for End Date and Duration calculations.

Working Times table:


Holiday’s table:


"Calendar" is a key table which is going to be used for all the calculations.

Here is how Kirill explained its structure:

"We've created Calendar table that lists all the dates and linked it to Working Time and Holidays tables. The only data column there is the date; Start and End Time columns draw the information from the Working Time or calculate as 0:00 for holidays. The Hours formula calculates the duration between Start and End Date. This table acts as a uniform source of information for any given date."

Here is how this table’s data looks like:


Let’s review End Date calculation function implementation (Test 1) first.

Here are Kirill’s comments describing this function implementation details.

"End date calculation would cause some difficulties in TeamDesk; the task is similar to calculating the running balance capped by Working Duration specified by the user; we can't do that directly. So we've made one assumption: the working hours for each day are either eight or zero.

Then we calculate number of working hours that fall on start date, calculate the number of working days (with an eight hours assumption) and use Calendar table and Index function to get the End Date from Nth working day. Remaining hours are added to the day's Start Date to determine End Time."

Here is how End Date calculation results look like:


I would especially outline the way how End Date is selected from the calendar.

Here is how Summary Column parameters look like:


Here is a many-to-many relation that it utilizes:


Now let’s move to the Duration calculation function implementation (Test 2).

Here are Kirill’s comments describing this function implementation details:

"Calculation of the working duration seems to be a simple math: one relation between Working Duration to Calendar records that fall between Start and End Dates to calculate the total of working hours between these two dates. Then we have to take in account partial days on Start and End Dates: two more relationships and some math to subtract the duration (if any) between Working Duration's From (Time) and the Start Time from Calendar; and the same for the End Date."

Here is how Working Duration calculation results look like:


As you might guess, the summary column does the main job here as well.

Here are Summary column parameters (this column calculates full working hours between two calendar dates):


…and many-to-many relation used for its calculations:


Now working duration calculation, accounting partial days, is nothing but a simple math and may be easily calculated using TeamDesk’s formula-column:

Excluding To (Hours)Max([To (End Time)] - Max([To (Time)], [To (Start Time)]), 0)
Excluding From (Hours)Max(Min([From (End Time)], [From (Time)]) - [From (Start Time)], 0)
Working Duration[Full Days] - [Excluding From (Hours)] - [Excluding To (Hours)]

Conclusions

As you can see, TeamDesk almost completely managed to implement our task. The system’s only shortcoming was End Date calculation if working hours fluctuate every day.

It is nice to see that there are still some systems capable to implement various business tasks without coding, even though I would mention that configuring Relation and Summary , as well as writing formulas takes some efforts from the user.

Nevertheless, it is the best result so far among the vendors pretending for a "no-coding" technique.

Thursday, September 10, 2009

Zoho Creator – write a program to calculate working time

If you like challenges and are not afraid of some programming - then Zoho Creator would help you implementing your tasks with no compromises.

First of all, special thanks to Mr. Gaev for detailed explanations and to Nandhini from Zoho Creator team for the sample application implementation.

Here is a sample data that is going to be used for working day and hours’ calculation:

Working Hours table:


Holidays’ table:


Naturally, Zoho Creator doesn’t have a ready-made function to process these tables and calculate working time, but Deluge Script allows creating a User-defined Function to perform this job.

The main function that is going to be used for End Date and Duration calculation is getWorkingHours. It calculates number of working hours in any particular working day, and it accounts holiday schedule as well. Here is how it looks:
int getWorkingHours(date date)
{
working_hours = map();
for each daily_hrs in Working_Hours [ID != 0]
{
working_hours.put(daily_hrs.DayOfWeek.toString(), daily_hrs.Total_Hrs);
}
holidays_list = List();
for each holidays in Holidays [ID != 0]
{
holidays_list.add(holidays.Date_field);
}
if (holidays_list.contains(input.date))
{
return 0;
}
else
{
dayofWeek = input.date.getDayOfWeek();
return (working_hours.get(dayofWeek.toString())).toLong();
}
}

Now we may calculate End Date (Test 1):
date getEndDate(date start_date, int start_duration, int total_duration)
{
duration = thisapp.getWorkingHours(input.start_date);
input.start_duration = (input.start_duration + duration);
if (input.start_duration < input.total_duration)
{
new_start_date = input.start_date.addDay(1);
input.start_date = thisapp.getEndDate(new_start_date, input.start_duration, input.total_duration);
}
return input.start_date;
}

Here is this function output:


A similar function may be added to calculate Duration (Test2):
int getTotalWorkingHrs(date start_date, date end_date, int duration)
{
if (input.start_date <= input.end_date)
{
date_duration = thisapp.getWorkingHours(input.start_date);
total_duration = (input.duration + date_duration);
new_start_date = input.start_date.addDay(1);
input.duration = thisapp.getTotalWorkingHrs(new_start_date, input.end_date, total_duration);
}
return input.duration;
}

Here is the second function output:


As you can see, both tests were implemented successfully; however some programming was required, and I am not sure that a business user would be capable to do that. Fortunately, there is Zoho Marketplace and Forum where novice Users can ask for help implementing such functionality.

Conclusions

Zoho Creator proofed to be able to calculate End Date and Duration, correctly accounting working days and hours. I had no doubts in this tool even before starting our tests since "Deluge Script", which Zoho is based on, is actually nothing less than a programming language.

The only question that remains is: Who is he, ideal Zoho Creator customer?

Wednesday, September 9, 2009

A real life could be more complicated than TrackVia could think

In the ideal world all the people would speak the same language, use the same alphabet and work Monday through Friday 8 hrs a day with a lunch break. In such an ideal world TrackVia would be a good tool for a working time calculation.

First of all, a special thanks to Matt Strenz, TrackVia Customer Support Engineer who created a sample database for me.
"We’ve added a database in your account that is using a calculated field to determine the number of working hours a date range would contain taking into consideration a 5 day work week of 9 hours a day and all the holidays you had mentioned."

Here are Matt’s recommendations how to implement end date calculation (Test 1):
"The first test you mention about creating an end date from the hours entered would be a more difficult calculation to do as this would require looping through the days to determine if they are a weekday or holiday and then to ignore them if so. Because this is a more complex issue it would have to be handled with a full programming language to allow for loops and variables. We built a simplified version in a database called End Date Calculation that you can review to see that this is using a dateadd() formula we have created which allows adding years, months, days, hours, or even minutes to an existing date field."

Here is how the database looks from the setup mode:


Here are calculation results:


Matt also explained how to implement duration calculations (Test 2):
"This is done using a weekdays() formula TrackVia offers which calculates the number of weekdays between two dates and datedif() to determine if there is a holiday that falls between the two entered dates. This would allow for a person to enter a starting and ending date for a project but would not take into consideration the times of day. The formula used can easily be updated to reflect a shorter or longer working day as well as changing the dates that a holiday will fall on."

Here is how the database looks from the setup mode:


Here is a complete formula that was used for the Duration Days calculation for your review:
weekdays(Start Time, End Time) - if(and(datedif(Start Time, year(Start Time)&"-12-25")>=0,datedif(End Time, year(End Time)&"-12-25")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-11-26")>=0,datedif(End Time, year(End Time)&"-11-26")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-11-11")>=0,datedif(End Time, year(End Time)&"-11-11")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-10-12")>=0,datedif(End Time, year(End Time)&"-10-12")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-09-07")>=0,datedif(End Time, year(End Time)&"-09-07")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-06-03")>=0,datedif(End Time, year(End Time)&"-06-03")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-05-25")>=0,datedif(End Time, year(End Time)&"-05-25")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-02-16")>=0,datedif(End Time, year(End Time)&"-02-16")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-01-19")>=0,datedif(End Time, year(End Time)&"-01-19")<=0),1,0) - if(and(datedif(Start Time, year(Start Time)&"-01-01")>=0,datedif(End Time, year(End Time)&"-01-01")<=0),1,0)

Here are calculation results:


Matt came to the following conclusions:
"Other date formulas available are datesub() which is the reverse of dateadd(), datetimedif() and datedif() which return the difference in either time or days, and weekday() which returns the day of the week a date falls on. Combining these formulas using if-then-else logic also available allows for all kinds of complex calculations to be made. We hope that from this you can see how complex calculations can be done in TrackVia and this will show how we have a wide range of prebuilt formulas to use for solutions to standard business problems."

My own conclusions:

Good news first: Looking at the "Duration Days" calculation formula, I think, nobody would say that TrackVia can’t perform complex calculations.

Bad news is that the system is still not able to calculate exactly what we needed. It looks like Test 1 had failed because the system is not able to calculate End Date while accounting working days (not even talking about hours). There are also some problems in the way durations were calculated for the Test 2. The main problem is that the function weekdays() is not designed for worldwide usage. If you are a TrackVia user from Israel, for example, the system would give you incorrect results because Sunday is a working day there.

As you can see, if you are going to add "working days and time support" functionality to your TrackVia application, you may need to research this system’s capabilities as well as its shortcomings to avoid surprises later on.

Tuesday, September 8, 2009

Caspio Bridge's way of working time calculation

Caspio Bridge provides you with all the MS SQL database power when you need to implement working days and hours support in your application, but require you to become acquainted with SQL language in return.

Here is Frank Zamani comments on how it can be implemented in Caspio Bridge:
"This has been done before. The process is straight forward:
  1. A web form is used to enter the starting date and the number of hours the project requires.
  2. Upon submission, a database trigger (custom SQL code) uses the customer’s lookup tables of holidays and working hours, and comes up with the end date, storing it on the just-inserted record.
  3. The web form from step 1 loads a "Search and Report Data Page" against the record that was just submitted and displays the calculated date.
The trigger in step 2 is created by the customer and provided to us or a Caspio engineer builds it for the customer for a small fee."

In case you do not have an idea on how SQL trigger looks like, here is rough draft of one:
-- ================================
-- Author: Caspio, Inc.
-- Create date: September 3, 2009
-- Description: Calculates end date based on start date and number of hours
-- for a project.
-- Replace <MyTable>, <FromDateField>, <ProjectHoursField>,
-- <EndDateField> with the appropriate field names from your table
-- ================================
CREATE TRIGGER [dbo].[ct_Ins_Upd_CalcDates]
ON <MyTable>
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @PKID int,
@FromDate_local datetime,
@projectHours_local decimal(18,2),
@EndDate_local datetime

SELECT @PKID = PK_ID,
@FromDate_local = ISNULL(<FromDateField>,'01/01/1900'),
@projectHours_local = ISNULL(<ProjectHoursField>,0)
FROM inserted

IF @FromDate_local <> '1/1/1900'
BEGIN
SELECT @EndDate_local = fn_GetEndDate(@FromDate_local, @projectHours_local)

UPDATE <MyTable>
SET <EndDateField> = @EndDate_local
WHERE PK_ID = @PKID
END
END

Following clarifications should help you better understand the process of building and using such a trigger.

Which name conversions are used to reference tables and columns from the app?
"In SQL actual field names are used. This can also be done in JavaScript. In JavaScript our standard prefix that is added to all field names should be considered. Users can look at the HTML source to see the naming pattern."

If some changes were done in the app we need to change this trigger too every time it happens?
"For SQL code, the table and app can change as long as the particular fields used in the calculation or storage of the value remain intact. JavaScript is more dependent on the app."

When this trigger is created by the customer is there any tools to test or debug it from customers’ side?
"Users can run their SQL code against their account via our API to test but we don’t provide a development environment."


Summary

Caspio Bridge’s capability of executing customer’s own code on server using SQL triggers opens wide horizons for implementing complex business logic in applications. The only problem is that you should be quite familiar with SQL in order to use it to the full extent. There also one more point that draws my attention: though I don’t know how data isolation is implemented in Caspio, but I think that possibility of running own code on server could present higher security risk of unauthorized data access.

Thursday, September 3, 2009

I want to calculate working time! Good luck buddy..

So many business tasks require calculating future date or time duration between two events but the majority of web based systems are not capable to calculate it right. The reason is nobody takes into account working days and hours. I am going to check if any system out there can handle this issue and how easy the implementation is.

This is initial data I am going to use to calculate working days and hours.

Working Time table

Day Of WeekStart TimeEnd Time
Monday10:00 am6:00 pm
Tuesday10:00 am6:00 pm
Wednesday10:00 am6:00 pm
Thursday10:00 am6:00 pm
Friday10:00 am6:00 pm
Saturday0:00 am0:00 am
Sunday0:00 am0:00 am


If Start Time = End Time this day is considered as a weekend.

Holiday’s table

DayDescription
12/25/2009Christmas Day
11/26/2009Thanksgiving Day
11/11/2009Veterans Day
10/12/2009Columbus Day
9/7/2009Labor Day
6/3/2009Independence Day
5/25/2009Memorial Day
2/16/2009Washington’s Birthday
1/19/2009Birthday of Martin Luther King, Jr.
1/1/2009New Year’s Day


Test 1: End date calculation.

Field/ColumnDescription
Start DateThe beginning of any process. The date is set by user.
Working Duration (Hours)The duration of the process in hours. Set by user.
End DateThe system calculates the End date taking into account holydays and working time.


On practice it can be used to manage projects or in planning. For example, you need to calculate the end date of anything (working days and hours included).

Test 2: Working time calculation.

Field/ColumnDescription
Start Date/TimeThe beginning of any process. The date is set by user.
End Date/TimeThe ending of the process. Set by user.
Working Duration (Hours)The system calculates the duration taking into account holydays and work time.


For example, one can control SLA in CRM. Let’s take the case you commit yourself to handle the client request within N business hours.

Well, I understand it’s not such an easy task, that’s why I am asking vendors to tune it in their systems if possible. After that I’ll sum up the results so you could compare the implementation (if any at all) in different systems, how easy it is and what skills are required.