Excel queries only support the Where technology, so this discussion only applies to the Office Connector workbook functions (TSLookup, TSFind, TSSum, TSCount). By default, Office Connector workbook functions use Microsoft’s Filter technology to apply criteria. First, all of the data is loaded, and then the filter is applied. Once you have paid the initial time penalty for loading the data, using Filter technology to apply the criteria to many formulas is fast. In a case where you only need one or two results from a given table, it can be faster to override this default behavior and pass the criteria directly to the Sage 300 Construction and Real Estate ODBC driver.
Here’s an example to illustrate the difference. Suppose that you have inserted a query that lists 300 of the jobs in your database, and now you need to sum the transactions for each job. You use the TSSum wizard to create the first workbook formula, which results in an agonizing 5-minute wait while the transaction table is pulled across the network. To speed things up, you insert the word “Where” in front of the Filter argument and click the Refresh button. Using the Where technology, your TSSum function now returns a result in 4 seconds. Not being really great at math, you say to yourself, “Now that’s like a billion times speedup!”. Next you fill down the formula for every row in your job query, and it looks like Excel has locked up. Twenty minutes later you get to see your results. Twenty minutes is far worse than five, so you remove the word “Where” from the filter argument and fill down the formula again. You encounter the 5 minute penalty again, but then you see the results almost immediately.