Table Mirroring

Created by Harry Lewis, Modified on Fri, 25 Apr at 4:55 PM by Harry Lewis

Applies to:

Velixo NX

TABLE OF CONTENTS

Modifying columns in a mirrored table
Manually modifying data in a mirrored table
Function examples

What is Table Mirroring?

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

Certain Velixo functions (e.g., GI and SI.QUERY) 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, using the OutputColumn1 parameter 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# parameters (OutputColumn2, OutputColumn3, etc.) unused]

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

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

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 GI function example…

… and add a new "Date" column in the Select parameter:

Note that, by default, the "Date" column appears as the final column in the table, despite being the second column described in the Select parameter. You can, however, modify column order at any time.

Per Column Mirroring mode

This mode lets you place an added column in the desired location in the mirrored table. To do so, use parameters OutputColumn1, OutputColumn2, etc. to define the order of columns.

In this GI function example…

… let’s add "Date" as the final field in the Select parameter and place it as the second column in the mirrored table:

Note that "Date", despite being the fifth field for the Select parameter, is now the second column in the mirrored table, as the OutputColumn5 parameter corresponding to this field is set to "B2".

Removing columns

When a column is removed, an empty column with an Unused column header will appear in its place in order to not the shift other columns.

Full Mirroring mode

Let’s remove the "Date" field from the Select parameter of the GI function described above:

Note that the "Date" column is replaced by an empty Unused column (which you can safely delete).

Per Column Mirroring mode

Let’s remove the "PostPeriod" column from the table returned by this GI function:

To do so, in addition to removing the "PostPeriod" field, modify the OutputColumn# parameters to take into account the number of fields in the Select parameter. For instance, a scenario with four Select fields and five defined OutputColumn# parameters will result in an error.

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# parameters 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. This applies to the GI and TOTABLE functions.

Note that manually entered data will be lost once the query no longer returns key values with which the user data is associated.

GI function

Configure key columns by navigating to the Options menu from the Velixo NX ribbon and expanding the Generic inquiries options item. Click the edit button next to the relevant connection, click the edit button in the Key columns field, select one or more columns you would like to bind to your manually added data, and add them to the Key columns list using the down-arrow button. Confirm by clicking OK twice.

We strongly recommend selecting all columns marked with the “Key” icon, as providing key columns that are not unique can cause data to be lost.

TOTABLE function

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

=TOTABLE(B10#,,,P9)

… the columns with indexes "1" and "2" 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

Note: 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.

GI function - (Velixo NX only) - send entire array to a table (with columns in unchanged order) - Full Mirroring

Description

Certain Excel features do not work with Excel arrays but rather require the data to be in an Excel table.

As a result, it can be convenient to have the option to create such a table from our generic inquiry data.

This example...

=GI("Demo", "AR-Invoices and Memos", "Type eq 'Credit Memo'", "Type,ReferenceNbr,Status,Customer,PostPeriod,Amount", FALSE)

... returns data from the AR-Invoice and Memos generic inquiry and displays it as an Excel array:

If, however, we modify the function by adding a value for the (Velixo NX only) OutputColumn1 parameter...

=GI("Demo", "AR-Invoices and Memos", "Type eq 'Credit Memo'", "Type,ReferenceNbr,Status,Customer,PostPeriod,Amount", FALSE, H2)

This returns data from the generic inquiry and displays it as an Excel table starting in cell H2.

Result:

Note: the GI() function still resides in cell A2, and the same data is returned. However, the results are displayed as an Excel table starting in the cell specified in the OutputColumn1 parameter.

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 parameter is set to: {2,1}
Header information is assumed not to be included in the array defined in cell B10
- The DataIncludesHeader parameter 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…

the key columns AND
the order for each column in the table (not just the starting cell)

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

Result

SI.QUERY function - send entire array to a table (changing column order) - Per-Column Mirroring

=SI.QUERY("Sage","Project",,"RECORDNO,PROJECTID,PROJECTTYPE,NAME",TRUE,,A5,B5,D5,C5)

Description:

Instead of displaying the results of the query starting in the cell containing the SI.QUERY function, the function Cell A2 displays the specified fields from the Project object in an Excel data table located in the cells specified by the OutputColumns parameters (cells A5, B5, D5, and C5).

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 parameter is set to: {1,2,3,4}
Header information is assumed to be included in the array defined in cell A6 (the DataIncludesHeader parameter 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).