Table Mirroring

What is Table Mirroring?

Many Velixo functions create Excel arrays (a single function can return multiple rows/columns).

Certain Velixo functions can express their output as either an Excel array or an Excel table, and the TOTABLE function can convert any Excel array to an Excel table.

These functions can send their entire output in unchanged order to a table (referred to as Full Mirroring mode) or can send selected columns in a specified order to the table (referred to as Per Column Mirroring mode).

To use Full Mirroring mode, use the TableOutputCell or OutputColumn1 argument of your function to define the cell address of the upper left corner of where the table is to be placed. [Leave the remaining OutputColumn# arguments (OutputColumn2, OutputColumn3, etc.) unused]

To use Per Column Mirroring mode, define all cell addresses for the columns returned by the function using OutputColumn# arguments.

Below, we explore the behavior specific to mirrored tables and provide examples of functions that use this feature.

When the range of a newly created/expanded table overlaps with data already present in the worksheet, it results in a #SPILL! error and stops the table mirroring process. Delete or move the obstructing cells to make room for the table.

Supported Velixo functions

• TOTABLE

• BC.QUERY

• BC.QUERYTABLE

Modifying columns in a mirrored table

You might want to modify columns in a table, or reference data which you have already used as a cell reference for another function, calculation, etc. To keep these references unaffected, a mirrored table will trigger specific Excel behavior when columns are added, removed, or their order is altered.

Adding columns

Full Mirroring mode

When a column is added, it always appears on the right side of the mirrored table, regardless of column order in the formula.

Let’s look at the following formula example…

… and add a new column in the Select argument:

By default, the added column appears as the final column in the table, despite being the second column described in the Select argument. You can, however, modify column order at any time by dragging columns into desired positions.

Removing columns

Full Mirroring mode

Remove columns as usual in Excel.

Changing column order

Full Mirroring mode and Per Column Mirroring mode

To modify column order in a mirrored table without affecting existing references, manually drag and drop the headers in the table to achieve the desired column order:

Changing the OutputColumn# arguments will also change column order; however, doing so will break cell references to data in the rearranged columns.

Manually modifying data in a mirrored table

For data entered manually in columns added to a mirrored table to remain in their expected rows when the worksheet is refreshed, marking columns containing unique values as key columns is necessary. Data in these columns will be used to uniquely identify rows in your table.

Manually entered data will be lost once the query no longer returns key values associated with the user data.

TOTABLE function

Configure key columns by providing the column index/indexes as a field in the KeyColumnIndex argument. For instance, in this formula…

=TOTABLE(A6#, {1,2,3,4}, , I5, J5, K5, L5, M5, N5)

… the columns with indexes 1, 2, 3, and 4 become key columns.

Function examples

TOTABLE function - convert entire array to a table (with columns in unchanged order) - Full Mirroring

=TOTABLE(B10#,,,P9)

Description

Creates a table (based on the array defined in cell B10) and places that table starting in cell P9.

Result

This method is referred to as Full Mirroring Mode, meaning that all the results (and ONLY the results) from the original array are included in the resulting table.

BC.QUERY function - send entire array to a table - Full mirroring mode

=BC.QUERY("BC2","journalLines","journalId eq 375762c3-e6d7-ef11-9344-6045bdc8a19b","id, lineNumber, accountId, documentNumber",,,B4)

Description: Returns data from the journalLines object, where the journalId line is equal to 375762c3-e6d7-ef11-9344-6045bdc8a19b. Displays fields id, lineNumber, accountId, and documentNumber in an Excel table starting in cell B4.

TOTABLE function - convert entire array to a table (changing column order) - Per-Column Mirroring

=TOTABLE(B10#, {2,1}, FALSE, K9, M9, I9, L9, N9, J9)

Description:

Creates a table (based on the results of the array defined in cell B10) as follows:

• The second and first columns of the original array (when combined) uniquely identify each row in the array

  • The KeyColumnIndex argument is set to: {2,1}

• Header information is assumed not to be included in the array defined in cell B10

  • The DataIncludesHeader argument is set to FALSE

• The first column of the original array will be placed in column K - OutputColumn1 is set to K9

• The second column of the original array will be placed in column M - OutputColumn2 is set to M9

• The third column of the original array will be placed in column I - OutputColumn3 is set to I9

• The fourth column of the original array will be placed in column L - OutputColumn4 is set to L9

• The fifth column of the original array will be placed in column N - OutputColumn5 is set to N9

• The sixth column of the original array will be placed in column J - OutputColumn6 is set to J9

This technique, where we specify both…

1. the key columns AND

2. the order for each column in the table (not just the starting cell)

... is referred to as per-column mirroring mode

Result

TOTABLE function - include (and maintain) user-entered data with the table - Per-Column Mirroring

In this example, we have added a calculation to the end of our table:

To maintain those calculated values when refreshing our report, use the per-column mirroring mode.

=TOTABLE(A6#,{1,2,3,4},,I5,J5,K5,L5,M5,N5)

Description:

This function creates a table (based on the results of the array defined in cell A6) as follows:

• The first through fourth columns of the original array (when combined) uniquely identify each row in the array

  • The KeyColumnIndex argument is set to: {1,2,3,4}

• Header information is assumed to be included in the array defined in cell A6 (the DataIncludesHeader argument is left blank, which defaults to TRUE)

• The columns in the table will be in the same order as they appear in the original array (the specified results are to be placed in columns I through N).

By using per-column mirroring mode, when the report is refreshed, the user-specified calculations in the highlighted column will be maintained (even if the results end up displaying different records).