Using Transformation key in Connectors

We have seen how to configure connectors and bind data to the controls. The most common is the use of Table Grid control to display data using the different connectors. There are a few 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 source databases or Collections. The Connectors has the Transform Keys features to enhance the usefulness of your data presented in the table grid by formatting it. The Transform keys is a part of your connector configuration process.

Using the 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 OUTPUT.result.rows.name for fetching the name column within the transformation.

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.

To the Table Grid example above, 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

So now whenever you run the form you can see the data displayed as per the Transformation planned.