How can I query data from another data folder with Office Connector functions?

Background

Office Connector functions (such as TSLOOKUP, TSFIND, TSSUM, and TSCOUNT) query data values from the Sage 300 Construction and Real Estate data folder that is currently associated with the workbook. Although you can easily switch to a different data folder, you might wish to have some formulas return data from a data folder other than the one currently associated with the workbook. This article describes how to do this.

Solution for TSSUM and TSCOUNT

With TSSum and TSCount functions you can include the path to the desired data folder within the Table Name argument (the first argument of the function). The data folder path must be enclosed in double quotes and a dot separator must be included between the path and table name.

  • Example 1 -
    =TSSUM("""\TimberlineServer\Timberline Office\Data\MyCompany"".MASTER_APM_RECORD_1","OIAMT","OISTAT='Open'")

    Pay particular attention to the double-quote characters in the above example. The first double-quote signifies the beginning of the Table Name argument. Next, there are two double-quote characters. Since there are two of them consecutively, Excel knows that neither signify the end of the Table Name argument and the two double quotes should be interpreted as one double-quote that is part of the Table Name argument. Likewise, the two double-quotes that appear at the end of the path are also interpreted as one double-quote. The double-quote that appears at the end of the table name (MASTER_APM_RECORD_1) signifies the end of the Table Name argument.

  • Example 2 -
    This is a variation of the above example where the data folder path has been entered as plain text in cell A5:
    Sheet2!A5 = \\TimberlineServer\Timberline Office\Data\MyCompany Formula = TSSUM("""" & Sheet2!$A$5 & """.MASTER_APM_RECORD_1","OIAMT","OISTAT='Open'")
    Solution for TSFIND and TSLOOKUP

    To query data from another data folder using TSFind or TSLookup, you must supply an SQL query statement as the Table Name argument. Consider the following example of a TSFind formula prior to changing it to work with another data folder:
    =TSFIND("MASTER_JCM_RECORD_1_1","JOB","JORDER1='2013NWFW'")

    To replace the existing Table Name value (MASTER_JCM_RECORD_1_1) with an SQL query statement, you should first make a list of the columns in the table that are being used (in this formula as well as other formulas that query it and the filter). In this case, the only two columns that we're using are JOB and JORDER1. This gives us what we need to construct the necessary SQL query statement as follows:

    =TSFIND("SELECT JOB, JORDER1 FROM ""\\TimberlineServer\Timberline Office\Data\MyCompany"".MASTER_JCM_RECORD_1_1","JOB","JORDER1='2013NWFW'")

    Note that the SQL query statement starts with the word SELECT and is followed by a comma-separated list of the columns you are using. That is followed by the word FROM and then the path and table name. As in the case of TSSum and TSCount, the path must be enclosed in double-quotes and a period must separate the path and table name.

    Note in the case of a TSLOOKUP function there is no filter. Instead, the desired row in the table is located by using the primary key that you supply. When using just a table name in the first argument, Office Connector is able to automatically identify the set of columns that make up the primary key. In the case of a SQL statement however, it cannot. For this reason, keep in mind that Office Connector will consider the first N number of columns identified in your SQL statement to represent the primary key for rows in the table. The number of columns that it assumes are primary key columns is based on the number of keys you provide in the keys arguments of the function.

    If you have multiple TSFIND or TSLOOKUP functions that use the same SQL statement, consider placing the text of the SQL statement in its own cell and then referencing that cell in the first argument of your TSFIND or TSLOOKUP function like this:

    Sheet2!A1 = SELECT OIVND, OIINV, OIDATE FROM "\\TimberlineServer\Timberline Office\Data\MyCompany".MASTER_APM_RECORD_1
    =TSLOOKUP(Sheet2!$A$1, "OIDATE", "100","OICU812")

    In the above example, cell A1 on Sheet2 contains the SQL statement. The TSLOOKUP formula uses a reference to that cell to supply the SQL statement in the first argument. Instead of using a reference like Sheet2!$A$1, consider naming the cell (using the Name Box) so that your formula is more understandable. For example, name cell Sheet2!$A$1 as InvoiceQuery and then the formula would look like this:

    =TSLOOKUP(InvoiceQuery, "OIDATE", "100","OICU812")