TeamDesk Blog

API Usage and public attachments

Some of you use TeamDesk as a back-end data management solution while extracting the content via SOAP API from the database to present on an front-end site, such as online shop. By the way, it's a way TeamDesk promo site works; the list of applications and their categories is managed via TeamDesk application, and the promo site utilizes the API to obtain the info.

Let's describe typical scenario: there is a Product table with Title, Description and Image columns; first two are a obviously a Text, and the last is Attachment.

To present the list of products with images in an HTML you typically need two pages. One will perform API's Login() call, then Query() to get the list of products and then render Title and Description texts and HTML <img> tag referring to the second page. Second page will again perform Login() and GetAttachment() API calls and serve the binary data obtained from latter call as an image.

Suppose your product list page displays 100 products at once. How many API requests it will generate? It's easy to calculate: there would be one Login()/Query() pair from the page itself and one Login()/GetAttachment() pair per product, resulting to 202 calls for each page view. Since each call generates network traffic and the network is a potential bottleneck, is there a way to reduce the number of calls?

Perhaps you can share API object between pages performing login once per session; this approach will cut the number of API requests in half; 102 requests exactly – one for first-time Login(), one Query() and 100 GetAttachment(). But is further optimization possible?

Let's check what typical website visitor does. She reads the list of products (102 requests), then clicks on a link to see more of some product details (this will generate some more requests we are not interested in at the moment). If she's not interested in the product she presses browser's back button or clicks a link to return to the product list to check another product – and this generates yet another 102 requests.

Perhaps you can optimize product list page logic to perform Query() only when list changes, but this requires some relatively sophisticated logic and advanced coding skills. It would be much easier to cut the number of calls to an image-serving page down, or, better to say, you have a chance to remove image-serving page at all.

As you probably know, some time ago we have enabled public access to attachment files. If public access is enabled, the file is served to the user without a need to login. Our web page serving the file performs various browser cache checks and return file only if the browser does not have it cached or cached copy is out-of-date. And now you can utilize this page on your site.

If you include attachment column in your Query() call you'll get back the file name and some additional info separated by semicolon, e.g:

filename;revision;uniqueidentifier

There are two pages in TeamDesk you can refer from your site:

https://www.teamdesk.net/secure/db/<app-id>/attachment.aspx 
and
https://www.teamdesk.net/secure/db/<app-id>/image.aspx

First serves attachment file as is, second assumes the file is image and produces the thumbnail of specified width and height

Both pages accept two parameters:

fid is a number identifying the column. You can obtain this identified by visiting column's page in the setup section and capturing the number following the string column= in the browser's address bar.
guid is an uniqueidentifier, the text after last semicolon in the  attachment string.

To generate the link to attachment file as-is that will be served directly from TeamDesk you'll need to provide an URL to:

https://www.teamdesk.net/secure/db/<app-id>/attachment.aspx?fid=<column-id>&guid=<uniqueidentifier>

Image thumbnails are served in a similar way:

https://www.teamdesk.net/secure/db/<app-id>/image.aspx?fid=<column-id>&guid=<uniqueidentifier>&w=<size>

Last parameter specifies the size of the square box, in pixels, the image will be fit to. The aspect ratio of the image is preserved and resulting image will not exceed box dimensions; e.g. if you specify w=100 16x16 image will remain the same, 400x400 image will be downscaled to 100x100, 200x100 image will be downscaled to 100x50 and 100x200 image will be downscaled to 50x100.

New form layout: migration plan

In this post we'll try to outline the steps you'll need to do to migrate to a new form layout from your existing applications. It's not only the change in a way the form appears on the screen. TeamDesk has developed over the years and many times new features supersede old ones. With new layout we are trying to fold various settings into one consolidated thing.

* In old layout Default Form was displayed in a single-column with order of the columns as defined in a table, without sections, and text blocks. In a new layout, Default Form is a fully featured form with sections, text blocks and columns' order separated from the columns' order defined in the table. If you have single custom form associated with all roles and actions you are few clicks away from successful migration.

* In old layout you were able to define multiple forms per table and associate various forms for different roles and actions. In a new layout we are defining a single form per table; multiple forms are dropped in favor of using form behaviors to specify what columns should be hidden from the form; with behaviors you can emulate role-based differences plus achieve a lot more.

* New layout affects Default Forms only, custom forms are not affected. To see which users will be affected, please check what roles in what tables in your application are associated with Default Form via Setup mode > table's tab > Forms > Associate Forms with Roles and Actions. If your have found the roles you do not want to enable new feature ]instantly, make a copy of Default Form and associate a copy of the form with the role(s).

* To enable new form layout you need to go to Setup mode > Setup tab > Tools section > Labs and enable Grid Form Layout. You can enable or disable this feature anytime.

Once you enable the feature, Forms menu will be reorganized. Old one contained:

• Customize Form Behavior
• Create New Form
• Customize Existing Form
• Associate Forms with Roles and Actions

New one is shortened to:

• Customize Form Layout
• Customize Form Behavior
• Generate Web-to-Record Form

If you had custom forms defined for a table you can still have access to them; the link to old functionality is moved under Rules section > Custom Forms link.

* If you have single custom form for all roles and actions, view the form and click "Copy to Default Form" button. This will copy all the columns, sections and text blocks to a new layout's Default Form. Then change form associations to set Default Form for roles and actions you want to enable new functionality to. If you are satisfied with results, change the association for remaining roles and actions.

* If you have multiple custom forms associated with various roles and actions start with the form most suitable for migration (containing most of the columns for example). Then adjust the columns' visibility as necessary via form behaviors:

1. To differentiate between Add and Edit mode you can use
   RecordId()='0' behavior's filter condition.
2. To differentiate between roles you can simply address the role name enclosed in square brackets in the filter, for example [My Role].

The Preview button found under Customize Form Layout can help you to play with behaviors and roles without actually deploying the form.

* Web-To-Record (W2R) functionality is also slightly affected. While old W2R forms exist, they will work unchanged. Yet if you decide to migrate completely, please note that there will be a single W2R gateway URL per table.

Enable all the columns from all of your W2R forms. Save. Capture <form>'s action URL from generated HTML code and use updated URL on your site forms.

* Once you complete the migration you can delete all of your custom forms to hide Rules > Custom Forms item.

Nuts and bolts: JS and CSS minified

While developing our products we use wide variety of technologies and tools. Some of those technologies are now obsolete and were superseded with new ones, some were improved over time and some still do the job.

With this series of articles we'll try to uplift the curtain and share our experience. It's not an ultimate HOWTO; it's an attempt to describe how WE do it. So, here we go!

Typical web site consist of static HTML pages, or HTML code generated dynamically and additional resources requested and loaded by HTML pages, such as images, style sheets (CSS) and JavaScript files. We'll speak about last two today.

Browser sends the request to the server and receives the response over the network. While some extra bytes does not seem significant, if you count the traffic generated by millions of requests you'll end up with megabytes of extra network load and hours of extra time. You can squeeze bytes by configuring your web server to compress the response. By why not squeeze few more bytes by removing letters and digits, spaces and punctuations that are not needed for web site operation? This is the goal number one.

Also, JavaScript and CSS are languages each with own syntax and rules. Normally they are interpreted when requested by the browser. Some browsers report syntax errors, some do not. Moreover obeying certain simple programming rules help to avoid common pitfalls and diagnose hard-to-find runtime errors – errors that pop up when the code is executed; but some code executed rarely. So, here is the goal number two: provide syntax check early in the development process, before deploying the files on web server.

There are number of tools to do this. Microsoft's Ajax Minifier aka AjaxMin, Yahoo's YUI Compressor, Google's Closure Compiler, JSLint, JSMin and many others; all of different quality, performance and feature set.

In terms of quality and feature set first three tools are winners – all of them provide a lot of checks and optimizations such as renaming local symbols, dead code elimination etc. Yet, to simplify build process configuration process we wanted to use single tool for both CSS and JavaScript syntax check and compression. This requirement narrowed the choice to first two as Closure Compiler does not support CSS validation and compression. As we are using Microsoft tool chain, MSBuild in particular, we have chosen Ajax Minifier since it is packaged with extra goodies to make integration with build process easier – no surprise.

Now, to the build process: in production environment everything is built for efficiency. For developers it's easier to maintain multiple, smaller files than one big file, yet, each request to the file take some network resources, so you'll likely want your files to be minified and concatenated together to minimize the number of requests needed to load all necessary resources. Moreover while some tools allow performing minification on the fly upon request we would prefer to do it prior to deployment to reduce web server's load.

In the debug build you'll do not care about network, but you'll want to have your original source code intact: all modern browsers have debug console were errors are reported and in case of runtime error it's better to see your own code than some hyper-crunched nonsense.

Speaking of concatenation keep in mind that you may need to control the order files are appended to each other as former can define some rules, functions and variables used in latter.

To control the order of concatenation and set of files to concatenate we end up with creating a sort of "project files" with .cssproj extension for CSS output and .jsproj extension for JavaScript code. Each project file results to a single output file and lists one source file per line such as:

compat.js
jquery/jquery.js
jquery/jquery-ui.js
fs/fs.namespace.js
fs/fs.datepicker.js
fs/fs.colorpicker.js
fs/fs.cssresize.js

In debug build each file should be validated and the output file contains "include" directives of original source files (via CSS @import directive or via document.write("<script …>") calls). To avoid filename clash, we name output file as "projectname.debug.js" (or.css, depending on project type). In production build the output files contain minified and concatenated content of each file listed in the project file, the output is "projectname.min.js". Site code is referencing either .debug or .min version of the output depending on build switches.

Ideally, in debug build any changed JS/CSS file should be re-validated. The output file should be re-generated only if the project file was changed, since output file does not contain source code content. In production build output file should be regenerated if the project file or any of the files listed in the project were changed. Yet, ideal implementation would require extra efforts. Since minification assumes validation as well, in a build process we do not differentiate between debug and production builds and produce both .debug and .min version of the files. Moreover, we trigger rebuild if ANY (not necessary those listed in the project) of .js or .css files were changed; we spend more time working on server code and few extra seconds of build time is the price we ready to pay.

Finally, while there is a special AjaxMin task for MSBuild we have decided to use command line version of the tool since it produces error reports in better format (as of version 4.23). Since there is nothing special about command line, with a little modification you can easily substitute AjaxMin with your favorite compression tools.

All build definitions are placed in a separate Minify.targets file.

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

<ItemGroup Label="Minifiers">

<JsProject Include="**\*.jsproj" />

<JsFiles Include="**\*.js" Exclude="**\*.debug.js;**\*-vsdoc.js;**\*.min.js"/>

<CssProject Include="**\*.cssproj" />

<CssFiles Include="**\*.css" Exclude="**\*.debug.css;**\*.min.css"/>

</ItemGroup>

<PropertyGroup>

<AjaxMinPath>"$(MSBuildExtensionsPath32)\..\Microsoft\Microsoft Ajax Minifier\AjaxMin.exe"</AjaxMinPath>

<AjaxMinJsOpts>-js</AjaxMinJsOpts>

<AjaxMinCssOpts>-css</AjaxMinCssOpts>

<AjaxMinOpts>-silent -clobber -term</AjaxMinOpts>

<JsDebugSuffix>.debug.js</JsDebugSuffix>

<JsReleaseSuffix>.min.js</JsReleaseSuffix>

<CssDebugSuffix>.debug.css</CssDebugSuffix>

<CssReleaseSuffix>.min.css</CssReleaseSuffix>

</PropertyGroup>

<Target Name="CleanProjects" AfterTargets="Clean">

<Message Text="Cleaning .js and .css" Importance="high" />

<Delete Files="@(CssProject->'%(RelativeDir)%(Filename)$(CssDebugSuffix)');@(CssProject->'%(RelativeDir)%(Filename)$(CssReleaseSuffix)')" />

<Delete Files="@(JsProject->'%(RelativeDir)%(Filename)$(JsDebugSuffix)');@(JsProject->'%(RelativeDir)%(Filename)$(JsReleaseSuffix)')" />

</Target>

<Target Name="BuildCssProject" AfterTargets="Compile" Inputs="@(CssProject);@(CssFiles)" Outputs="%(CssProject.RelativeDir)%(CssProject.FileName)$(CssDebugSuffix);%(CssProject.RelativeDir)%(CssProject.FileName)$(CssReleaseSuffix)">

<ReadLinesFromFile File="%(CssProject.Identity)">

<Output TaskParameter="Lines" ItemName="CssLines" />

</ReadLinesFromFile>

<PropertyGroup>

<CssProjPath0>%(CssProject.RelativeDir)</CssProjPath0>

<CssProjPath>$(CssProjPath0)</CssProjPath>

<CssOutFileName>$(CssProjPath)%(CssProject.Filename)</CssOutFileName>

<CssDebugFile>$(CssOutFileName)$(CssDebugSuffix)</CssDebugFile>

<CssReleaseFile>$(CssOutFileName)$(CssReleaseSuffix)</CssReleaseFile>

<CssImports>@(CssLines->'@import &quot;%(Identity)&quot;%3B&#13;&#10;','')</CssImports>

<CssInFiles>@(CssLines->'$(CssProjPath)%(Identity)',' ')</CssInFiles>

</PropertyGroup>

<Message Text="%(CssProject.Identity)" Importance="high"/>

<WriteLinesToFile File="$(CssDebugFile)" Lines="$(CssImports)" Overwrite="true" />

<Exec Command="$(AjaxMinPath) $(AjaxMinCssOpts) $(AjaxMinOpts) $(CssInFiles) -out $(CssReleaseFile)" />

</Target>

<Target Name="BuildJsProject" AfterTargets="Compile" Inputs="@(JsProject);@(JsFiles)" Outputs="%(JsProject.RelativeDir)%(JsProject.FileName)$(JsDebugSuffix);%(JsProject.RelativeDir)%(JsProject.FileName)$(JsReleaseSuffix)">

<ReadLinesFromFile File="%(JsProject.Identity)">

<Output TaskParameter="Lines" ItemName="JsLines" />

</ReadLinesFromFile>

<PropertyGroup>

<JsProjPath0>%(JsProject.RelativeDir)</JsProjPath0>

<JsProjPath>$(JsProjPath0)</JsProjPath>

<JsOutFileName>$(JsProjPath)%(JsProject.Filename)</JsOutFileName>

<JsDebugFile>$(JsOutFileName)$(JsDebugSuffix)</JsDebugFile>

<JsReleaseFile>$(JsOutFileName)$(JsReleaseSuffix)</JsReleaseFile>

<JsImports>(function(d){&#13;&#10;@(JsLines->'d.write(&quot;&lt;script src=\&quot;&quot;+gRootURL+&quot;/$(JsProjPath)/%(Identity)\&quot;&gt;&lt;\/script&gt;&quot;)%3B','&#13;&#10;')&#13;&#10;})(document)%3B&#13;&#10;</JsImports>

<JsInFiles>@(JsLines->'$(JsProjPath)%(Identity)',' ')</JsInFiles>

</PropertyGroup>

<Message Text="%(JsProject.Identity)" Importance="high"/>

<WriteLinesToFile File="$(JsDebugFile)" Lines="$(JsImports)" Overwrite="true" />

<Exec Command="$(AjaxMinPath) $(AjaxMinJsOpts) $(AjaxMinOpts) $(JsInFiles) -out $(JsReleaseFile)" />

</Target>

</Project>

Import this file to your project file as

<Import Project="Properties/Minify.targets" />

and enjoy!

Calculating Job Duration

We’ve got a challenging request from Jane McCarty of webappsatwork to calculate job duration in TeamDesk. In the application we created we had to comply with the rules of competition; now we’ve decided to share the results with all of our users and created a simplified version of this app for our application library.

The task is defined as follows: defining the start and the end of working hours and knowing the weekends and the holidays - calculate job duration between specified start and end dates and times.

More on Customization

Here is a brief demo of how to customize a predesigned application step-by-step and completely tailor it to any business on the example of invoicing application.

We'll be glad if it facilitates your day-to-day work.

Jump start has never been easier

More and more business people come to conclusion that for a qualitative leap forward they need to shift from old fashioned way of data handling in Excel to advanced web based application use. But in the vast majority of cases beginners are stuck with the complicated structures, how to begin, etc and all that just throws them away.

To make the shifting process painless and absolutely simple the jump start with TeamDesk is free of any constraints now.

We take an Excel file and based on its data create a customizable application for free so the customer can make sure that its exactly what he needs and that it provides all expected benefits. Giving a hand at this stage we permit the user to check in practice how up and running application changes the very approach of working with the data.

You can check it out here: http://www.convertexceltoapp.com

Once you try it, you will never want to come back to previous methods of information processing.

As you keep on using a web-based application it becomes quite clear that it’s an unexpendable tool of business development.

Replace Spreadsheet

Searching for ways to make starting working with TeamDesk more simple, and looking for the best manner to do that to make everything clear from the first sight, we've decided to give an illustrative example of the most frequent and basic operation: data transfer from Excel into TeamDesk. To eliminate any confusing moments we’ve made a short spot of "How to Start" without a hitch.

More detailed info on how to start the workflow optimization and why it is worth doing that see at Replace-Spreadsheet.com

We hope it will give a hand as expected!

Impatiently waiting for feedback and any comments!

Area Code to Time Zone mapping

Our US customers, especially the ones using Leads management or Calls tracking applications, kept asking us about adding a functionality that automatically fills in the time zone according to the area code for the US phone numbers as it would save them a great deal of time by instantly displaying timezones next to the phone numbers.

We created a sample Area Code to TimeZone mapping application to demonstrate this functionality. Please explore the application and review a Setup mode.
The application is based on two tables joined via many-to-many relationship.
"Phones" table contains sample phone number list along with the State/Time Zone offset extracted from the "Mappings" table.
Area code is intelligently extracted from the US phone number regardless of the format.
"Mappings" table contains mapping between the US phone number area code and the timezone.
Now you may add area code to time zone mapping functionality to your existing applications or hire us to do it for you.

How to Calculate Running Balance

Given lines with amount (positive for income, negative for expenses) in a table called Money, you want to calculate running balance. What should you do?

First you have to define order for lines. Standard Date Create column TeamDesk generates for every table can suit your needs - old lines will appear on top, recent on bottom. If you plan to reorder the lines, you may need to create another, editable Timestamp column, let's call it Date.

Next, let's create many-to-many relation from Money table to self, and specify "Date Created < Date Created" match condition (or Date < Date, if you decided for custom timestamp). For any given record this relation will create the list of preceding records. Calculating a total on the amount column will result to opening balance. Adding the total (via formula column) will result to closing balance.

Simple, is not it?

Running Balance application from Application Library does one step beyond separating operation from amount and calculating income and expenses. Feel free to explore.

Default Value for a Column

You have a table with the data, and want to extend it with new column? No problem. But what if you want to initialize existing records to some value? Default Value property will not work in this case. Obviously new records will receive default value, but not old ones. Is there something you can do?