Background Office Connector automatically optimizes the queries it uses behind the scenes when evaluating functions (such as TSLookup, TSFind, TSSum, and TSCount). It does this by keeping track of the set of columns that have been used from each table within a given workbook. The list of columns is stored as part of the workbook so that when the workbook is refreshed, it can quickly assemble the queries needed and the queries only return the necessary data (i.e., not all columns from the table).
Symptom An Office Connector function returns the error #TABLE? (SAGE_TABLE_NAME) or zero when it should be returning a non-zero/non-blank value.
Cause If an invalid column name is used in a function, this will result in a query that doesn't work. Consequently, the query that Office Connector uses behind the scenes for the affected table does not execute and so all functions that reference the table will stop working (even though they provide valid column names). An invalid column name would be a column name that has been misspelled or doesn't exist within the requested table.
Resolution
Even after correcting the invalid column name, the issue persists because Office Connector is maintaining a list of column names that have been used with each table. The list is saved with the workbook and so the issue will persist even when you close and re-open the workbook. This list of columns is only rebuilt when these steps are followed:
- Identify and correct the spelling of column names in all Office Connector functions within the workbook. Note: The Log File will have an entry identifying the invalid field name. For example: Invalid column name: 'JSTATU'
- Refresh the workbook two times in a row.
- Save and close the workbook.
- Close Excel
- Reopen and refresh the workbook.