Table Mirroring

=TOTABLE(B10#,,,P9)

Description

Creates a table (based on the results of 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 the entire results (and ONLY the results) from the original array are included in the resulting table.

Certain Excel features do not work with Excel arrays but, rather, require that data 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)

...would return data from the AR-Invoice and Memos generic inquiry and display 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)

Description: This returns data the 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 in the OutputColumn1 parameter.

=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 t
  • the KeyColumnIndex parameter is set to: {2,1}
• Header information is assumed to not 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 both...

1. specify the keys columns AND 
2. specify the order for each and every column in the table (not just the starting cell)

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

Result

=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)

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

In order to maintain those calculated values when we refresh our report, we need to use 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).