Manual entries in mirrored SI.QUERY tables are missing after refresh

Symptom

After you refresh a workbook, your manually entered fields/cells in user-added columns inside a mirrored SI.QUERY table may be cleared.

This typically happens under the following conditions:

• The table is generated using table mirroring from an SI.QUERY formula with aggregated fields, for example, it uses the SUM operator in the Select argument.

• You manually add a column in the mirrored table, for instance, a Yes/No dropdown used for Writeback.

• After a refresh, the table is repopulated, and your manually-entered values disappear without any error messages.

Cause

In aggregated queries, all non-aggregated columns are treated as key columns for row matching. If any of those non-aggregated values change in the ERP, Velixo may treat that row as a new row after refresh.

When that happens, the refreshed table cannot re-map your manual entries to the “new” rows, so manually-entered values get dropped.

Workaround

TOTABLE with defined key columns

To keep manual entries stable across refreshes, wrap your SI.QUERY formula in a TOTABLE formula and explicitly define the key columns using the KeyColumnIndex argument.

Example

Follow the steps below to adapt your formula. This example uses this “problematic” formula to illustrate the process:

=SI.QUERY(
  "Sage",
  "PROJECTCONTRACTLINE",
  SI.QUERYFILTER("Sage","PROJECTCONTRACTLINE","PROJECTID",$H$3),
  "PROJECTID,PROJECTNAME,SUM(TOTALREVISEDPRICE)",
  TRUE,
  {"Sort","PROJECTID"},
  $G$15
)

1. Keep your current aggregated formula, but skip the OutputColumn1 argument.

2. Convert the SI.QUERY formula output into a table by creating a TOTABLE formula that:

   1. Uses the SI.QUERY formula as the Array argument

   2. Uses non-aggregated columns that are stable identifiers (for example, ID columns) as key values in the KeyColumnIndex argument.

   3. The updated formula will look as follows:

      =TOTABLE(
        SI.QUERY(
          "Sage",
          "PROJECTCONTRACTLINE",
          SI.QUERYFILTER("Sage","PROJECTCONTRACTLINE","PROJECTID",$H$3),
          "PROJECTID,PROJECTNAME,SUM(TOTALREVISEDPRICE)",
          TRUE,
          {"Sort","PROJECTID"},
          ), 
        {1}, 
        , 
        $G$15
        )
      

3. Add your additional columns (for example, dropdowns or yes/no flags) to the TOTABLE output table (not into the original mirrored output).

4. The manually added column should now persist after a refresh, even if non-aggregated, non-key fields change.