We have seen how to make use of the Table grid control to view the data in 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 and present it using the Table grid control and Detail view control.
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. Studio now has the Format Data feature to present formatted data. The Formatting would typically be dependent on the type of data, like presenting the date column in a certain format or adding a Currency symbol to your numeric column.
Using Format Data for Detail view control
With the Format Data feature, you can format your data using a key that represents the column that you want to provide the formatting. The formatting options that are available are based on the column type and are supported by the Table Grid control and Detail view control.
Now let us first understand how the Format Data feature can be used to fetch product data from the Prod_docTest sheet that we have here for the sake of the example.
Now let us view the data from this sheet using the detail view. Add a detail view control to your form and then bind it to the sheet. Select the columns you want. In this example, we would fetch the data for the product name that is entered in the text control. So let us add the filter accordingly. You can see the Custom Formula accordingly.
Now below Custom formula, click Format Data and you would be taken to the Column Format. Here you would add the Column names which represent the columns you have selected. So now in this example let us add formatting to the Prod_name and Prod_type columns. So let us add the Columns using the same name (Prod_name and Price for the sake of this example).
Select the Column name and click Column Type. Here you can see the different column types are listed out. Depending upon the column you can select the Column type and set the formatting accordingly and click Apply.
Once you add formatting to all those columns you want, click Finish. Now if you run this form you would be able to see the formatting applied to the selected columns.
Using Format Data with Table Grid Control
We have seen earlier how to use the Format Data with Detail view control whose data is being fetched from a Sheet. Now let us take another example to fetch data for a Table Grid control using a DB connector. Let us see how we can use Format Data in the Table Grid control.
Here you can see in the illustration that we have selected the MyData DB Connector and the keys to display data.
Below Custom formula you can find the Format Data button. Click Format Data to go to Column Formatting and Row Formatting options. This is conditional formatting applied to the specified columns and rows. Here you would add the Columns’ names which represent the keys from the Connectors, Sheets, and other data sources.
You can apply conditional formatting to the selected column. Select the Column name from the suggestions that appear on hover and click Column Type. Here you can see the different Column types are listed out. Depending upon the column data you can select the Column type and set the formatting accordingly and click Apply.
You have different formatting options available. You can add an icon with its respective color, apply a background color, text color, and other text formatting if your column is text type. A very important function here is that you can apply formatting to a column while comparing the value of another column when you specify the conditional format. So in the illustration here the result.rows.name column will be formatted, but the condition given specifies the value of the result.rows.initials is compared here. We have also applied a simple formatting for the Author ID column. If the column is not empty then the formatting is applied.
Once you add formatting to all those columns you want, click Finish.
Now if you run this form you would be able to see the formatting applied to the selected columns as per the conditions specified in these examples. The name column will have a certain color code and the icon where the initials start with A and the Author_id would be formatted in a specific manner.
For a detailed description of the formatting options using Format Data feature, refer to this article here.
Another interesting formatting feature that you can apply to the Percentage type data is to add a progress bar.
Select a column that contains percentage type data.
Goto Format Options. In case of Percentage type, you need to specify the Limit for which the formatting would be applied.
Specify the Minimum and Maximum limit values.
You can see the toggle switch to display the Progress bar. So now if you Switch it ON the progress bar would be displayed in the column in the table grid.
By default, you can see that any value below the minimum limit is indicated with Red colored progress bar, anything within the range is indicated with Blue colored progress bar and those above the maximum limit is indicated with the Green colored progress bar.
You can also apply conditional formatting to a Row. So you can specify the conditions based on the operators available for each type of data. You can then change the color settings for the respective row based on the row value.
So in this example below, you can see that the formatting is applied to the row based on values in the Initials column where the value is equal to JP. For other rows where the Author ID is less than 5 the specified color formatting is applied to those rows.
Now if you run the form and take a preview of the rows the formatting for the rows is applied. In the screenshot below note that we have the column formatting and the row formatting applied accordingly
Another option is to import the formatting that you have applied. Assume that you have already used Formatting for another Detail view or Table grid and you feel that it could be useful to apply the same for the current detail view, then in that case you can use the Import feature available in the right corner for the Detail view control as well as the Table grid control.
Click Import and select control from From Control to get the format. All the table grid and detail view from you current Catalog would be listed out.
Select the control and click Import. You can see that under Column Format, the common columns are automatically filled up along with their respective formatting.
Thus the same format is already applied which reduces you formatting effort substantially for large datasets.
Wherever there is a mismatch of columns, the format would not be applied. You would have to apply the formats manually.