We are happy to deliver the brandnew July 2023 release with the new calculation wizard and many other new features! Try it now!

Remember, the Web Client is the user friendly spreadsheet for your Collector Users to deliver their monthly/weekly/daily data. The Web App is the tool for the Power User to setup and control your organization’s collection workflows.

(1) Web Client: New calculation wizard (Add-On)

With the new calculation wizard your users can enter values with the calculation operators +, -, * or /:

How does this work? The definition is very simple:

• Definition of the named range “data1_CELLS01_Calculation“
• Simply type the column letter of the source column for the calculation in this range (only into the desired columns)

Please note that this new feature is licensed as Add-On since it was financed by a single data1.io customer. Please find the current pricing here.

(2) Web Client: New parameter “Application Key”

The ListQuery feature was introduced in the February 2023 release and was improved in the March 2023 release, in the April 2023 release and the May 2023 release.

If you want to connect your ListQuery filter with the Fact table – e.g. to show only elements with current values – then the application key is needed for this SQL filter criteria. Until now this application key had to be hard-coded within the workbook.

With this release you can use a new parameter to retrieve dynamically the application key:

• Named range “data1_ApplicationKey“

Simply connect your application key with the ListQuery Filter using a formula to generate an SQL filter statement like this:

• [DIM005.KEY] IN (SELECT [DIM005.Key] FROM appG5K9KA5DVD.T_FACT01 WHERE [SYSTEM.IsDeleted] = 0 AND MEASURE01 IS NOT NULL AND [Dim003.Key] = ‘MARKET’)

(3) Web Client: New parameter “Read after Write”

From now on you can defined a refresh of your sheet after the WRITE process – e.g. in order to refresh the ListQuery with the customers which have already data:

Simply define

• Named Range “data1_ReadAfterWrite“
• 1 = execute a Read process immediately after the Write process
• 0 = do nothing after the Write process (= default)

(4) Web Client: Debugging for % queries

There were several issues with aggregated “%” queries:

1. Combination of “%” query in 1 dimension and “%” query in another dimension
2. Combination of “%” query in 1 dimension and “-1” query in another dimension
3. Combination of “%” query with the definition of a “DIM001_Alternative” parameter

Those issues are solved now.

(5) Web App: New behaviour of table filters

We have changed the behaviour of table filters in 2 manners:

1. Deactivating the filter button removes now all current filters from the table
2. Deleting records does not remove current filters from the table any more

Think of the fact table with 2 current filters:

First, deactivating the filter button removes now not only the filter symbols from the table header but removes now also all current filters from the table:

Second, deleting records …

… does not change the current filters anymore:

This will help you a lot, if you have to delete records in portions with the variation of only 1 filter criteria and keeping the other filters unchanged. And if you want to remove all the current filters simply press the filter button to do this. 🙂

(6) Technical Improvements and Debugging

We have solved an issue with the error message “Cannot read properties of null (reading ‘findIndex’)” with Sheets that have no READ activated cells.

We have solved another issue with the error message “Sequence contains no elements” with sheets that have no valid date in the named range “data1_CELLS01_DATExx”.

Furthermore there was an issue with the title of workflows using the new type “recurring”.

---

Hope you like the new features – we are very curious about your feedback!

Please let us know what you think and what kind of collection processes you would prefer to automate with data1.io. 

We`ll be back after the summer break with the September Release!

we #StandWithUkraine

We are happy to deliver the brandnew May 2023 release with important new workflow options in the Web App! Try it now!

Remember, the Web Client is the user friendly spreadsheet for your Collector Users to deliver their monthly/weekly/daily data. The Web App is the tool for the Power User to setup and control your organization’s collection workflows.

(1) Web App: New workflow type “Continuous Workflow”

We are very happy to present a new workflow type: Continuous Workflows! The workflow type that we had to date is now called “Single Workflow”.

You can change now the workflow type …

… to “Continuous Workflow” then this workflow has no start and no end date any more. That means this workflow will be valid for an indefinate period of time.

The workflow box in the web client does not show any validity date for the collector users and there is no “Finish” button:

(2) Web App: Configure the Finish button in “Single Workflows”

In the “Single Workflow” we had until now the mandatory “Finish” button:

From now on, you can activate or deactivate the Finish button and furthermore you can also configure the message that is shown to your collector user in the workflow box:

Please be aware that the user-specific “Collection Status” becomes irrelevant, if the Finish button is deactivated (the same is true if the workflow type is “Continuous Workflow”):

(3) Web App: Improvements for E-Mail Notifcations

We happy to present the new “E-Mail Send Weekday” option:

Furthermore we have moved the activation / deactivation button for the E-Mail Notifications from the “Invite your users” section directly into the workflow settings (since this is much more intuitive):

(4) Web Client: Definition of mandatory entries

Another important new feature is the definition of mandatory entries like this:

It is very easy to use. Just define the following 2 parameters (= named ranges) in your workbook:

• data1_Write_Validation
  0 = default = Writeback is open
  1 = Writeback is blocked
  
• data1_Write_ValidationMessage
  Use this parameter to define the message for the user, which is displayed if the first parameter has value “1” and the Write button is pressed.

You can simply control the write validation by generating a “1” on the first parameter via a formula which is referenced to the validation rules in your individual workbook:

Furthermore you can define as many write validation “couples” in your workbook as you like:

data1_Write_Validation_1
data1_Write_ValidationMessage_1

data1_Write_Validation_2
data1_Write_ValidationMessage_2

(5) Web Client: Group By for ListQueries

The ListQuery feature was introduced in the February 2023 release and was improved in the March 2023 release and in the April 2023 release.

Until now, it was only possible to define a ListQuery on the bottom level of a dimension table like this:

With the new Group By Feature it is possible to define a ListQuery also on an aggregated attribute in the dimension table like this:

The feature is activated with the new parameter (= named range) “data1_CELLS01_DimensionValues_T_DIM00x_GroupBy”:

• 0 = default = bottom öevel (no group by)
• 1 = aggregated level (group by)

This GroupBy parameter causes all “output parameters” (here on Attribute 15) to deliver grouped lists:

Compare the grouped list above with the dimension table itself:

(6) Web Client: Show workflow box also after validation date

Until now, the organizer of a workflow and its deputy …

… did not see the workflow box in the Web Client after the validation date of a workflow (type “single”) – all other collector users did seet it. This is fixed now, so that also the organizer and the deputy can see the workflow box:

(7) Technical Improvements and debugging

We have solved a bunch of issues:

• Troubles with E-Mail Notifications since SendGrid changed the verification rules for senders on May 1st 2023
• New Write Button: performance issue with the check of writeable values in large sheets
• New Write Button: activation/deactivation also in real time (on formula result changes)

---

Hope you like the new features – we are very curious about your feedback!

Please let us know what you think and what kind of collection processes you would prefer to automate with data1.io. 

Stay healthy and we #StandWithUkraine

We are happy to deliver the brandnew March 2023 release with many new up- and download functions in the Web App! Try it now!

Remember, the Web Client is the user friendly spreadsheet for your Collector Users to deliver their monthly/weekly/daily data. The Web App is the tool for the Power User to setup and control your organization’s collection workflows.

(1) Web App: New Update Mode for table uploads

Until now, you had to delete e.g. the dimension table before you uploaded a new version of this table. From now on, you can choose if you want to append the records from the upload file or if you want to update the existing records in the target table:

The new upload mode dialog shows you, which field in the table will be used as key field for the update process:

The new update mode is blocked with the fact table since we see no fields of application for the update mode, even more it would be risk if you choose the wrong mode:

(2) Web App: New Download for large tables

We started the development of the download feature for large tables about 6 months ago and invested a lot of energy in this feature. Now we are happy that the feature is mature and generally available!

Every download is now – the same way as the uploads – executed in a background task which provides the download file after it was finished successfully. This background task is now stable also for downloads of large tables.

(3) Web App: New Upload of CSV files

Since large tables have often more than 1 mio rows, we support now also CSV files as upload files:

(4) Web App: New Upload of Files > 300 MB

Until now we had an restriction of 300 MB for every upload file. We have removed this restriction now, therefore xlsx or csv files of any size can be uploaded.

(5) Web App: Further Improvements for table uploads

Until now, the first sheet in the upload file was uploaded – independantly if this sheet was visible or hidden. From now on, the first visible sheet is upload, hidden sheets are not relevant any more for the upload.

Furthermore, the empty string (“”) in date fields causes no longer an error when uploading such files.

(6) Web Client: Improvements for the ListQuery feature

The ListQuery feature was introduced in the February 2023 release. We have improved the sorting of the dynamic list and you can now also display the content of the SORT column of the queried dimension table.

(7) Web Client: Improvements for the Visibility settings

We have also improved the “Details function” which was introduced in the September 2022 release and November 2022 release. There was an issue with the “1” parameter and also some other tricky situations when the rows/columns were shown initially the wrong way round. This is solved now so that there are no open issues in this feature.

(8) Technical Improvements and debugging

We have solved many technical issues and also some bugs. For instance, the download of the collector users table was not active any more. Then we have solved a bug with the new RLS definition (which was released in February 2023 release) and the WRITE button. There was another issue with the Always Encrypted feature.

Finally, the display of the “Admin” role on the planning widgets in the “Your active collection” landingpage was not shown for Tenant Admins:

---

Hope you like the new features – we are very curious about your feedback!

Please let us know what you think and what kind of collection processes you would prefer to automate with data1.io. 

Stay healthy and we #StandWithUkraine

We are happy to deliver the brandnew January 2023 release with the new Iterator function and many other new features! Try it now!

Remember, the Web Client is the user friendly spreadsheet for your Collector Users to deliver their monthly/weekly/daily data. The Web App is the tool for the Power User to setup and control your organization’s collection workflows.

(1) Web Client: New iterator function (Add-on)

The new iterator function “switches” the workbook through all bottom-level elements below the element the process is started – i.e. a bottom element is selected, the READ process is executed and then the WRITE process for this element is executed. This is useful in all applications, whenever dependant calculations within the workbook exist. Here in this case the percentage for the Employer Fees can be adopted and then all elements have to be re-calculated.

The iteration can be started on any top level element from the context menu …

… and after the confirmation …

… the iteration process is started:

The new iteration function is an Add-On function, that means it can be activated for an extra fee.

Within the application, the iterator function is activated / deactivated on the workbook level:

(2) Web Client: Query logic improvement for the READ process

This feature improves the SQL statement that is used to fill your worksheets with data. More precisely, the SQL query on the fact table is customized depending on the dimension key fields that are actually used in the worksheet where the READ process is executed.

Until now, the value in a cell within the workbook was determined …

… by grouping the fact table over ALL dimension key fields …

… and then showing the value or text of the first record (if several records exist for a cell) that is found. If in the sheet not all of the filled dimension keys were queried, the result was strange (too small) values because of the group by also on the not queried columns in the background.

From now on, the fact table (in the background) is selected and grouped only by those dimension, that are actually used in the sheet for the READ process. This makes the SQL statement not only much shorter but also much more effective. You can now use now in a planning or report sheet all or only some of the filled dimensions. You will now always get the correct full sums for the cell.

(3) Web Client: Performance improvement in READ process

This feature improves the WHERE clause of the SQL statement that is used in the background to fill the cells of a sheet during a READ process:

• The “Tree” dimension (= DIM001) has always been filtering the virtual fact table by the selected element(s)
• The “Date” dimensions (e.g. DATE01) filter now on the time range from the lowest to the highest date (“BETWEEN”) that was found in the named range definition
• The “String” dimensions (e.g. DIM002) filter now on the list of the elements found (“IN”) in the named range definitions

Due to a SQL restriction, the first filter 2000 parameters are taken into account for this query performance optimization.

The effect of this query optimisation is all the greater the more (historical) records are in the fact table, that are not relevant for the current READ process.

(4) Web App: Row-Level-Security (RLS) also exclusive now

Until now it was only possible to define RLS inclusive.

From now on it is also possible to define RLS rules also exclusive.

This makes it much easier to define security rules like “user has access to the whole company except one node“. Since RLS rules in data1.io are executed in an additive way, simply combine inclusive rules with additional exclusive rules:

So, the new exclusive definition is realized with the new Deny Access user right, the other 3 user rights are all inclusive:

(5) Web App: Direct access to true/false fields

We are happy, that from now on it is possible to change true/false definitions in the Web App (“data1.io Backend”) directly in the table view:

This is implemeted until now in 4 tables, after a testing period further tables could follow.

(6) Web App: New Add-On Licenses

The new Iterator function is a so-called Add-On license. This is a new subscription type which optionally can be booked for your application (at this time this is not opened for self service, please contact us if you are interested).

(7) Web App: Improved message on sorting Always Encrypted columns

We have improved the message when you try to sort an Always Encrypted column in the Web App (= Backend):

Remember: as an tenant admin you will find the Always Encrypted column(s) in your Application Settings (at this time, this function is not opened for self service, please contact us if you want to activate/deactivate this function).

(8) Technical improvements and debugging

We have solved issues whith the “add element” function the dimension tree, with the drag & drop in Lists (e.g. for the “Edit Tree” attributes) in the Firefox browser, with multiple filters on tables in the Web App and with the calendar dropdown in the Web App.

Furthermore, we have renamed the parameter data1_CELLS01_SyncSheets_Read to data1_SyncSheets_Read and data1_CELLS01_SyncSheets_Write to data1_SyncSheets_Write. due to logical reaons (the previous names are still supported).

---

Hope you like the new features – we are very curious about your feedback!

Please let us know what you think and what kind of collection processes you would prefer to automate with data1.io. 

Stay healthy and we #StandWithUkraine

We are happy to deliver the brandnew November 2022 release with the new Details Button and Tree Attributes functions and many other new features! Try it now!

Remember, the Web Client is the user friendly spreadsheet for your Collector Users to deliver their monthly/weekly/daily data. The Web App is the tool for the Power User to setup and control your organization’s collection workflows.

(1) Web Client: Extension for the details function

In the September 2022 Release we have introduced the new Details function. Now we have extended the function with the new parameter “2“:

• 0 = always hide the column or row
• 1 = hide/unhide the column or row with the details button
• 2 = always show the column or row
• any other = do nothing

Furthermore we have added a “change trigger“: whenever a value changes in the visibility named ranges, then the details function is executed and the rows and columns are hidden or shown immediately (depending on their current definition).

You can use this for many fields of applications, get inspired by the following two.

Fields of application #1: showing detail rows controlled by dropdown selections

You can control now the visibility of rows and columns simply with dropdowns – and of course you can combine this also with the Details button:

In the background this is simply done by connecting the visibility settings with the dropdowns via normal Excel formulas:

Fields of application #2: showing/hiding details rows/columns controlled by the element selected

You can show/hide now very easily rows and columns that are only relevant on the bottom level of the dimension tree:

In the background this is simply done by connecting the visibility settings with the value of the data1_CELLS01_IsSumLevel parameter:

Please be aware that this feature causes a performance overhead – therefore test your workbook carefully with this great feature before you publish it to all your collector users.

(2) Web Client: Force update popup

This is a very important new feature!

Since many (collector) users keep their browser tabs open for several days (or sometimes weeks), this can be a big problem since they would not work with the current version of the data1.io Web Client / Web App. This is solved now since every user gets an update message box when the version is not the newest one:

(3) Web Client: Column selection for dynamic tables

The column selection for dynamic tables can now be configured: simply define (or leave) only those column of the table that are relevant for your usecase.

(4) Web Client: Show/hide the named ranges dropdown and the formula bar

The named ranges dropdown can be a security risk since your collector users could enter settings that maybe are not protected and could then be edited.

With this release you`ll find two new parameters:

• data1_Visible_FormulaBar
  0 = hide
  1 = show
  no definiton = default = show
  
• data1_Visible_NamedRanges
  0 = hide
  1 = show
  no definiton = default = hide

Setting the value to “1” will show both the named range dropdown as well as the formula bar:

Setting the value to “0” will hide both:

No definiton will show the defaults – formula bar on and named ranges dropdown off:

(5) Web Client: New parameter to trigger the row filter

There is a new parameter

• data1_Trigger_RecalculateAndApplyFilterOnChange

With this named range you can define one or more cells of the sheet as trigger cells to execute a RECALCULATE of the formulas on the sheet and a refresh of the ROW FILTER (“auto filter”). The second feature is similar to the automatic “change trigger” for the new visibility settings presented further above in item #1.

(6) Web Client: Improvements on the reload of the dimension tree

We have solved an issue with the reload of the dimension tree after editing e.g. the element type of an element.

(7) Web Client: Improvements on sheet changes

We have solved issues with the parameter data1_ReadOnSheetChanged, which was introduced in the June 2022 Release. This worked already well with the parameter “1” (= READ when re-entering the sheet) but not with the parameter “0” (= no READ when re-entering the sheet). In this case, the dimension tree was not shown correctly when coming back to the sheet. With this release we have solved this and it works now well with both configurations.

We have solved also an issue with the parameter data1_CELLS01_SyncSheets_Read, which triggers the READ process over several sheets. It happened that the Write button was not activated when re-entering the sheet although it should have been. This is solved now.

We have solved a third issue with same name sum-level elements in the dimension tree. It happened that the wrong sum level element with the same name as the selected element was READ when re-entering the sheet.

(8) Web Client: Improvements on the “Download every tree node” function

We have solved a bug with the “Download every tree element separately” and same name elements:

The download files of the sum-levels are now named with the full path of the node – there same name elements do not overwrite each other any more:

(9) Web App: Improvements on deleting records from (large) tables

We made the process of truncating a table (= deleting all records of a table) now also to background task although this would be not necessary from a technical point of view. But this guarantees the same user interface for all types of deleting records from a table.

(10) Technical improvements and debugging

We have solved two bugs with the conditional formatting feature in the Web Client`s spreadsheet. One confused the “. and ,” due to language settings and another one overruled the “cell locked” property and opened locked cells for data entry. Both issues are solved now.

Another issue happened in the “Weekly Projects Showcase” (which uses the so-called “BY-CELL” mode): the writeback of only empty cells was not executed. Now it is exectued.

---

Hope you like the new features – we are very curious about your feedback! Please let us know what you think and what kind of collection processes you would prefer to automate with data1.io. 

Stay healthy and we #StandWithUkraine