TeamDesk Blog

Setup search extension

TeamDesk setup sidebar is now extended with Search Keywords box.

This box allows you to search every table for an objects containing the search term in their name or formula. It is quite useful when the user reports a problem or asks for advice mentioning the view or column but forgetting about the table the object is contained. Also, you can now quickly check whether and where some function is used in the formulas. For example if your are planning to upgrade to user property table it is worth searching for Role(, UserToName(, UserToEmail( to gradually replace these calls with role references or references to user property table's columns.

User Properties Feature

How often you need to add the additional information such as salary or department to your application users' records?

Usually you can do it by creating a table (say, User Properties table) with column of User type as a key and adding necessary additional properties as columns of this table. Whenever you need this info for the user recorded in some other table you can relate two tables and extract all necessary information via lookups.

The problem arises when you need to extract this information for the current user, typically when defining access rights such as "Allow view records related to the people from my department". Before you would need a formula column calculating User() formula and a relationship to user property table and a column to extract the value. But situation gets worse since you need to do it for every table you need this information in.

With "native" support for User Property table it's all much easier now.

First, enable User Property Table feature under Setup > Labs. Once enabled, visit Setup > Sharing / Roles > Set User Property table. TeamDesk will propose you to select existing table having a User column as a key column, or create a new one:

Once user table is selected, it will be extended with E-Mail, First Name, Last Name, Screen Name, Role, Default Set, External Customer and Last Access read only columns containing live snapshot from the information found in setup section.

Also you'll have couple of new options.

* For any column of user type you can create lookups to the information stored in user tables right from the column's view screen:

* Wherever you need an information from User Properties related to current user, you can simply refer to the column of User Properties table by its name.

* Setup's Manage Users page is extended with button to edit corresponding record in User Property table.

Obviously users just invited do not have a record in User Properties. For such users the button is named "Add User Property" and creates new record with user's dropdown pre-selected.

Relationship Management Simplified

We added a number of shortcuts to manage relation-related settings in TeamDesk.

First of all, there are two new items under Columns section of table's setup menu: Create new lookup column and Create new summary column.

Each one leads to a subset of general Manage Relations page, focusing on selected functionality.

Above is a screenshot from Manage Relations page. Since lookups are relevant for master tables only, Create new lookup page displays only References section:

while Create new summary displays Selections section:

Column list now sports New Lookup and New Reference buttons to replicate setup links' functionality.

Columns participating in relationships as reference columns now report the table(s) they refer to:

Column's view screen displays the information about associated relationship:

Also, relationship's basic properties are edited together with column.

Enjoy!

Merge Template to Your Application

Now, after selecting a template from the application library we are prompting you to either create a new application or to merge the template with any existing application you own.

This will allow you to build application by combining simple templates together.

DOCX generation improvements

Recently we encountered some issues with DOCX templates created in Word 2010. Unlike its predecessor Word 2010 uses more complicated constructs for merge fields we use for placeholders and we did not recognize such definitions properly. We revised merge fields identification algorithm, fixed few other issues by the way and step by step ended up with completely new document generation algorithm.

Our old client-side .DOC generation module used Word Automation to expand the template. When we started working on server-side code, we simply replicated old algorithm with all the quirks – this helped us to ensure that movement from old to new technology will produce (nearly) identical results.

Yet, one part definitely needed improvement: the detail records generation. Due to lack of suitable methods in the automation our code had somehow sophisticated algorithm that tracked row and column index of each placeholder in the detail table and performed replacement of whole cells, not just the placeholders. This caused some formatting and layout restrictions, for example, the font and color should be defined for the cell as whole, not for the content of the cell; nested tables were not allowed.

Now, generator works more straightforward from user's perspective. First we replace all master-level placeholder in the document wherever they are placed. Next, we generate detail tables one by one. Here we have to repeat some part of template for each detail record. Due to lack of non-visual markers in a Word documents and for backward compatibility we define repeatable item the table row. We find and locate all the placeholder for detail table and find innermost table row they have in common. This row will be repeatable block. For each record we clone the row with all placeholders, replace the placeholders and insert now-expanded content back into the table. Here are illustrations:

Column 1	Column 2 * Column 3
«Detail/Column 1»	«Detail/Column 2» some text «Detail/Column 3»

The row marked gray will be repeatable block since it's a common row for all detail placeholders. Since we now operate on a field level, single table cell can contain several placeholders and/or text around the placeholders.

Now, more complicated layout:

Master Info	Detail
«Master Info»	Column 1 Column 2 «Detail/Column 1» «Detail/Column 2»

Here we nest detail table into another table. The repeatable block is the row in the inner table since this one is innermost common to both detail placeholders. And now even more complicated:

Column 1	Mixed Content
«Detail/Column 1»	Master Info Column 2 «Master Info» «Detail/Column 2»

Here, the row in outer table will be repeated since it's common for both detail placeholders. There is no problem for inner table to contain master information – it will be simply repeated for each row in the detail table.

We are trying to preserve the text formatting wherever possible, but formatting should be applied to placeholder as a whole. Many Word's formatting commands apply to a word the cursor placed on. As we replace the placeholder completely, we do not preserve the styling defined within the placeholder. Moreover, if multiple styles are defined for placeholder content, it's not clear what style to use for content. For example, what font, size, style and color we should use when replacing this?

«Master Info»

As a bonus, we've added column usage information for the documents. Make any change in your app, save and columns will start to report their usage in the DOCX documents.

Anatomy of new form layout

* Few months ago we started experimenting with new form layout. The development is finished now and new layout is enabled in new applications by default. This post describes design decisions we've made during the development and the outcome. We'll describe the migration plan for existing customers in the next post.

* In TeamDesk we call database tables record properties' columns. Some databases call it fields. Here we'll speak mostly about HTML layouts that consist of rows and columns. To avoid mixing up definitions we'll refer to the content of HTML tables as rows and columns and to TeamDesk database columns as fields.

Traditionally TeamDesk used tables for forms. When we started developing the product in 2005 this was perhaps the only truly cross-browser way for making multi-column layouts.

Many things changed since then. Many web standards reached recommendation stage. Browser vendors switching to rapid development to support latest web specifications. Smartphones and tablets arose. We've added many nice features such as dynamic recalculations and form behaviors to TeamDesk.

In tables intersections of rows and columns form cells. In TeamDesk each field were occupying two cells: one for column label and another, next to the label, to display the value or provide an editor. The browser is responsible for the table layout, determining optimal size for each row and column, but the browser will never break a row if its content exceeds the screen width. This is good for tabular data, but in case of forms it is a big problem to render the layout for devices with narrow screens.

Suppose, you have the form laid out in four columns (two fields in a row, each field occupies two columns). Suppose we have long labels and wide-enough content, so each column does not exceed 320px.

This is what we had:

Field 1:

Value 1

Field 2:

Value 2

While, in theory, narrow screen can be detected and four columns could be broken in four rows (label above the value below, field after field), the browser will never do that:

Field 1:

Value1

Field 2:

Value 2

This was the first problem to solve. Another was also caused by table layout. See, what's wrong here?

		Field 2:	Value 2
Field 3:	Value 3	Field 4:	Value 4

Field 1 was hidden by either column access right or by form behavior. But, despite what you may expect Field 2 is not shifted to the left (or Field 3 is not shifted up.) In table layout hiding the cell is identical to hiding its content, the space occupied by the cell is not "freed."

In addition to changed web landscape we had a number of various layout improvement ideas from our customers. So we decided to take on new form layout.

First of all, widely used browsers have decent support for latest cascading style sheet standard. From this standard, two things were of our interest: CSS floats to allow grid-like layout without sticking to tables; and CSS media queries to provide additional layout hints for smartphones and tablet devices. So we ended up with the following rules:

1. The form is divided into four equally sized virtual "columns";
2. The width is always expressed in number of columns;
3. The field can occupy one to four virtual columns (Width1 - Width4). You can instruct the field to occupy new row (hard break), or to be displayed next to preceding field. If the total width of the fields in a row exceeds 4, the field exceeding the width will be displayed in a new row (soft break);
4. You can provide alternative label to a field;
5. You can specify whether to display the label to the left of the value or above it;
6. The field contained in the section. If there is no section defined explicitly we define unnamed one;
7. The width of the section is defined by the sum of fields' widths in  row but does not exceed 4;
8. Sections are laid out the same way as columns.
9. You can apply behaviors to every form item including sections and text blocks.

Perhaps these rules need some explanations:

Why 4 columns? Customers asked us to have a possibility to define 3 fields in a row. Yet, 4 columns give more options to have a form look like  a grid: you can define 1x4 or 2x2 or 4x1, plus combinations of 3+1, 1+3, 2+1+1 1+2+1, 1+1+2. Below is an example of the form from Leads Management application laid out with new features:

Default layout is optimized to work well on a 1280px screen which we believe is a standard for most desktops and does not depend on browser's standard support level. With sidebar hidden, it should work as well on a 1024px screen which is a standard for netbooks and tablet devices in a landscape mode.

For narrower screens we'll provide additional layout hints. For example we can force the layout in two columns or always display label above to free more space to display the value. Yet, this is still the work in progress.

Hard and soft breaks start playing when fields and sections are dynamically shown and hidden. Hard breaks are always respected. Soft breaks are not: when you hide preceding field next field is shifted to the left. If there is enough of free space in a row we'll try to fit the field from the next row. Preview button will give you an idea how the form will look like with various combinations of View/Edit modes, roles and behavior rules in effect.

Displaying the label above makes sense when you have either narrow section (Width1) to make more room for content or wide content by definition such as multiline text. We tried to experiment with some automated decisions, yet finally decided for default layout to leave it up to user's consent.

But hey, tables are not gone! Some e-mail clients have limited HTML rendering capabilities, so in e-mail notifications we are still using tables to make sure our layout can be read correctly.

Autocompletion in type-in record picker

Previously, you needed to type some text into the type-in picker and move input focus out of control to have your search query resolved to particular record. Yet, there were no clear indication if there is no records matching your query or there is multiple records; TeamDesk leaves your text unchanged and error message appears only when you click Save button.

With latest release we have added auto completion functionality to type-in pickers. Starting from second letter you've typed TeamDesk sends your text to the application and gets back the list of top 20 matched records.

These records are displayed in the dropdown under input box allowing you to choose one. Search logic was modified to implicitly add a star (*) wildcard to the end of your query.

Cross tab views

TeamDesk holds the data in flat tables meaning that it consist from fixed number of columns and variable number of rows. While such structure can contain a lot of information it can be difficult to present summarized info in such a way.

Let's demonstrate typical Task Management application. We have a project to work on, a person to perform the task, a date the task is performed and a amount of time spent on the task:

Now we need to find an answer: how much time the team spent each day on each project. Obviously you can obtain the results using summary views:

Obviously, the answer is found, but you'd likely want to have subtotals for all projects each day as well as subtotal by the project for the whole time period.

With charts you would be closer to the goal – you'll have approximate subtotals by day, but you won't get precise value. For example, for 06/09/2001 is a total 18 or 19?

Also, charts are limited to calculate single value. So, cross tabs will bridge the gap between summary and chart views.

For a cross tab you'll need to specify the field whose values will form columns in an output and another field whose values will form rows; and then the field(s) and function to calculate to form a value of a cell on an intersection of row and column. In our example projects will form columns while dates will form rows; each cell displays the number of tasks and total time spent.

Rows, columns and cells are clickable. Clicking on a column heading will display records by specific project for all dates. Clicking the date will display records by specific date for all project. And clicking the cell will display records by specific date and project:

We hope cross tab views will add the value to TeamDesk toolset.

Formula checking tool

Whenever you change something in your TeamDesk application, it also updates all related places. Yet, updating the formula is not an easy task.

Suppose you had a numeric column and a formula doubling the column's value. Then you changed column's type from numeric to date. Or you simply deleted the column. What result the formula should calculate now? Zero? Nonsense!

It's not possible to guess how the formula should be modified to produce still meaningful result. It's likely you, as an application administrator should decide if the formula is still relevant and how it should be modified. So far, when you are trying to delete a column we produce extensive usage report and leave formula code intact, even if it would be invalid. Yet, if you do massive changes in your application you may forgot to patch all the formulas were involved. And it was quite challenging task to identify all such formulas in a large application.

Now the task made easy. Under Setup > Setup tab > Tools you can find "Check Formulas" tool that lists all problematic formulas in your application.

Also, if you have at least one that does not compile, red banner is displayed on the setup overview pages.

<Ask the user> extensions

There is a number of limitations when using <ask the user> option in view's filter and in today's release we've tried to address some of them.

1. If user provides no value, the part of the criteria is ignored. Yet, checkbox has two states, either checked on unchecked and therefore it was not possible to ignore its state. Now <ask the user> for checkboxes renders three-state dropdown with —Any—, Yes and No options.

2. If you set up the view's filter to <ask> for lookup column we were rendering simple edit box for user input. Solution is simple, yet effective for many scenarios.

But what if you need to display a dropdown? In this case you might need to create a dummy column with proper list of choices.

Now TeamDesk takes it one step further. We are tracking all columns along the lookups chain to find "final" column and display input control according to this column's settings. That's if you are <ask>'ing for lookup that refers to (lookup referring to…) text/numeric column with choices, or user column, or the column referencing another table, we'll display drop down box instead of simple edit box.

This might be useful in a case when you need to select, say, all Contacts belonging to Companies having particular Status – and the Status is determined for user input.

To take advantage of new functionality simply open and re-save the view.