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>

@FromDate_local datetime,
@projectHours_local decimal(18,2),
@EndDate_local datetime

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

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

UPDATE <MyTable>
SET <EndDateField> = @EndDate_local

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."


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.


  1. Great post.

    Part of the reason requiring customers to provide their server-side code to Caspio for deployment is the security concern you raise in your last paragraph.

    Caspio allocates a separate database schema for each customer account. Each customer's data and files are stored in that customer's schema.

    Possibilities are endless.

    Frank Zamani

  2. Interesting post - thank you. When I tried to create a simple calculation in Caspio to take "Percent Complete", deduct it from 100 and display "Percent Remaining" it was amazingly difficult. I spoke back and forth with support a bunch of times and was told that I had to deploy the page then examine it using some viewer to find the "real name" of the field I was trying to reference (or something like that - it's been 6 months or more). When they told me it would cost $200 for them to write this tiny snippet of JS for me, I just gave up. I compare this to my experience with TeamDesk where I have some extremely complex code (e.g. mash-up with google visualizations) and the support has bent over backwards to help out. I do like Caspio, but it really seems that anything beyond *very* simple forms & views is quite tough for a "non-coder."

  3. To write JavaScript against a form you must know your field names. It has nothing to do with Caspio. If you don't know how to find your field names you shouldn't try to write code. Caspio was right to offer you a quick service and save you a lot of hassle.

    Share us the link to your fabulous new app.

  4. I get that you have to know the field names - the problem was determining the field names. The names I had to use in the JS were apparently different than the ones shown on the Caspio screens.

  5. Oops - forgot the link to our other app. The data sheet/overview is www.nextwave360.com. It's an enterprise app, so it's not a "sign-up & use it now" type system, but this should give you a sense of what it does. Thanks for asking about it!