In our app we give the user the possibility to download a table as excel. Some of the otherwise number-containing columns contain (null) values denoting missing entries. In the downloaded excel, these give errors in the respective cells (#ZAHL! or #NUM!, respectively), which the app user dislikes. Is there a possibility to make these errors disappear in the excel, eg. by defining another fallback value in the excel export? We would like to keep the column formatted as a number.
you could define a SQL transformation on your datasource to replace the NULL values with some fallback. The COALESCE function in SQL is useful for this.
The download in apps respects all transformations defined on the datasource and element and will export the transformed data. So it will contain the fallback values introduced by your SQL query.
@matthias.fisch thanks for the idea. in od/apps everything is fine, the value is shown as (null) in the table, as we want it. the problem only arises in the excel download. if i would transform the datasource, this would also change the table in the app, right?
The transformations defined on the datasource or element in your app configuration don’t change the underlying datasource. They are only applied on-the-fly when the app retrieves data.
You could for example add the following sql transformation to your datasource:
{
"id": "datasource_with_nulls",
"origin": "datatable",
"config": {
"schema": "table",
"dataId": "4279d7db-3a48-452c-a64d-475b6633c702",
"dataOptions": {
"sql": "SELECT entry, COALESCE(value, -1) AS value FROM inputTable"
}
}
}
The query will use the fallback value -1 for the column value in case it is NULL and uses the actual value otherwise.
But the fallback would then be also shown in the table with this approach. Unfortunately it is not possible yet to define transformations specific for the download.
