Variable-Transformations With Two Outcomes (based on a condition)

Apps (Frontend) Apps Builder
1

Dear App Builders :relaxed:,

in our Apps we use a workaround to make it possible for the user to choose which columns of a table should and which ones shouldn’t be displayed. To achieve this we use a multi-select filter that is based on a table that only contains the column-names. We then use that filters variable-value in the SQL statement of the table element, to only display the columns the user selected. This usually works very well, if there are no “fixed” columns, meaning columns which the user cannot deselect.

Here is an example of how our SQL statement currently looks like. The fixed_column is a column that is not available in the filter and is therefore always displayed.

"sourceoptions": {
    "$sql": {
        "value": "SELECT fixed_column, {{filters.column_filter.columns.values | list?quote= &separator=,}} FROM inputTable"
    }
}

We use the pipe operation to essentially parse the variable-value into correct SQL syntax. The transformation from the raw variable-value looks something like this:

[“cash_disc_days1”,“cash_disc_days2”] → column1 , column2, column3

The problem arises when the user decides to unselect all the columns in the filter. If this happens the SQL statement will become invalid, because the variable value will be empty which in turn leaves the last comma after fixed_column dangling.

My question therefore would be: is there a way to condition the existence of this one comma after fixed_column on whether the variable value is empty or not (by e.g. incorporating it into the {{}} expression)?
Essentially the evaluation of the variable in the SQL statement should behave something like this:

if filters.column_filter.columns.values NOT empty: SELECT fixed_column, column1 , column2, ... , columnX
else: SELECT fixed_column

I already spent quite some time experimenting with the pipe operations, different SQL statements etc., but really couldn’t find a way to achieve this.

As a side note:
Another way to achieve the column-select functionality (instead of using an SQL statement) is to override the config.columns property of the table element. This is how that could look like:

"config": {
    "$columns": {
         "type": "object",
         "value": "[{\"name\":\"fixed_column\"}, {\"name\": {{filters.column_filter.columns.values | list?separator=}, {\"name\": }} }]"
    }
}

Unfortunately, this solution also ends up causing the same problem as the previous one.

Of course any feedback or idea is highly appreciated! Also if you happen to come across a way to approach this column-selection problem differently let me know. Thanks everyone :relaxed:

Best Wishes,

Johannes

3 Likes