Using Transformation key and Formatting in Connectors

We have seen how to make use of the Table grid control to view the data in a listview or detailed view. Table grid control data can be fetched from sheets or even the SQL and NoSQL databases using the different connectors Typically there would be two ways in which you can fetch data, using LOOKUP formula and using the BINDAPI function. We have seen how to fetch data in this article here. There are a few more features that can help enhance the data that you fetch when using Connectors for the different SQLs like MySQL, MongoDB, and so on.

When you are using a table grid control the idea is to present all your data in a tabular and easily readable format. By default, the columns are reflected as they are within the Collections. The Connectors now have a feature to enhance the usefulness of your data presented in the table grid by formatting it. Formatting could be based on the type of data where you present the date in a certain format or add a Currency symbol to your numeric column. Similarly, when presenting data you can also transform the data by combining two different columns into one using functions like Concatenate or using a limited number of characters from a column with data running into hundreds of characters in each row. For example, showing only the first 50 characters of a hotel review stored in a column and so on.

Studio offers two features within the connectors: Adding Transform Key and Formatting.

Transform keys: The Transform Keys allow you to add the Keys to the connector response to fetch and transform data using formulas for the selected columns. You can use the string, numeric and other formulas as required in this transformation. The response from the connector would be accessed while building the formula using the OUTPUT keyword. E.g for fetching the name column within the transformation.

Formatting: This can be used to format the data of your particular key. The formatting options that are available are based on the column type and are supported by the Table Grid control.

Using the Transform Keys and Formatting

Now let us take a simple example here to fetch data from the Author collection using the MySQL connector and display the data using the table grid control.

In the sample above you would note that the selected columns are displayed as-is. You may want to have some formatting or some better presentation for each so in that case, we can add the transformation key and formatting to present data in an overall better look and feel. Also, we may want to add some conditional formatting wherein for the empty rows you want it to be highlighted differently or for one that is not empty it is displayed differently. So similarly any conditional formatting can be applied depending upon the column type. You can add custom functions or formulas to transform data using Additional Keys to the response from the connectors. The existing response key can be accessed in the formula using the OUTPUT keyword.

Adding Transform Key

Let us add a Transform Key to concatenate the last name column in the collection and the initials column. We would use the Concatenate() function for the same as seen in the example below. Similarly, let us also see if we can fetch only the first few characters from the column. All the columns will be added as transform keys as follows:

API transform key

Adding Formatting

Now we further want to add certain formatting to the respective rows. For the FullName, we want to use a background color where the row is not empty. We also want to display the Author ID column with a prefix “Auth Id”. So under Bind Data> Format Column Types select the respective column and keys and add the formatting.

bind data format column

  • Select Author ID as OUTPUT.result.rows.author_id and select the data type as Number. Now if you click on the dropdown arrow next to it, for the number data type you have options to Format as currency or accounting or set prefix/suffix or add conditional formatting. In this example, we want to add “ AuthID: “ as a prefix. So add the prefix and click Apply.

  • Now select the FullName key. Here the data type would be text. For the text data type, you have the option to add conditional formatting. Depending upon the data type the conditional formatting operators would be provided. For the sake of this example let us take it as IsNotEmpty we would have row color set to some color.

Formatting types

There are different data types for keys that can be used to apply the formats. The data types are as follows. Only some of the data types would have certain formatting options available.

  • Text : Conditional formatting as seen above

  • Number and Decimal: Format, Prefix / Suffix, and Conditional format as seen above.

  • Toggle : Styles: You can provide the different style of toggle symbols like the default one or the star or heart shapes and so on and also use different colors for them.

  • Single select and multi-select: Both single and multi-select have the formatting for Options to add colors.

  • Percent: You can provide the limit to the percentage

  • Rating : For rating you can add different icons like star, heart and so on and also provide the maximum number of icons to add to provide the rating degree and also use different colors for them.

  • URL, FileUpload, Email, TextArea, JSON Object, GeoLocation : There would be no formats for these data types.

Running the form

Once you have added the keys and the respective formatting, you can then bind the API to the table grid. Now here you can find the respective columns as well as the Keys to be used for the table grid output.

api values

So now whenever you run the form you would be able to see the data fetched from the respective collections and presented in with the formatting you have applied to each of the Transformation keys as well as the individual columns.

  • For the sake of this example for the FullName key we concatenated the last name and initials with a colon “ : ” symbol in between. Here we had conditional formatting for IsNotEmpty.

  • FirstChar Key fetches only the first two characters from the Last name column using the LEFT() function.

  • Author ID columns only had formatting with AuthID as a prefix which can be seen in the output.

Thus the Transformation key and Formatting features of the Connectors enable you to format data in your TableGrid in a presentable manner.