Event 1 Knowledgebase

Category » Query

There were 14 questions found in this category:

1. Are Office Connector conditions case-sensitive?
   BackgroundIn some cases conditions are case-sensitive. This is dependent the specific Office Connector feature you are using to query data and how it is being used.The following list identifies when case-sensitive comparisons would occur: Query Table The conditions of the query include the LIK ...
2. How can I add case-insensitive conditions to a query when using the LIKE operator?
   BackgroundThe LIKE operator is useful for identifying rows in a table by matching on a partial value. For example, the following expression would identify vendors with names containing the word “Electric” (referred to as the search-text in this article):[VNAME] LIKE '%Electric%'Prob ...
3. How can I apply conditions that are not case-sensitive when using TSCount or TSSum with a LIKE operator?
   BackgroundThe LIKE operator is useful for identifying rows in a table by matching on a partial value. For example, the following expression would identify vendors with names containing the word “Electric” (referred to as the search-text in this article):[VNAME] LIKE '%Electric%'Prob ...
4. How can I combine data from multiple data folders in one query?
   Background An ordinary query inserted into a worksheet using Office Connector only queries data from a single Sage Timberline Office data folder. By editing the SQL text of the query afterword, you can include the data from the same tables in other data folders. SolutionThe solution is create a ...
5. How do I correctly apply a condition based on a list item field?
   BackgroundA condition involving a List Item Field is normally case sensitive. The exception would be where a List Item Field is used with a TSFind function, TSLookup function, or the table-name argument of an Office Connector function is an SQL statement.A comparison involving a List Item Field ...
6. How do I create a list of field names in Excel?
   BackgroundFor each field in the Sage Timberline Office database, there is an internal name and a user-friendly caption. The user-friendly captions are much more intuitive and the internal names are more cryptic. Office Connector uses the internal names in queries and worksheet functions because ...
7. How do I use pattern matching with GL Accounts?
   BackgroundSome Office Connector reports allow you specify which GL Accounts to include by using pattern matching (using the SQL LIKE operator). This allows you to chose which accounts to include or exclude by entering an account number pattern that consists of a combination of literal characters ...
8. I use Property Management and I need to find the current total square footage of a unit.
   The trick here is to use a combination of TSCount and TSFind to locate the last square footage change. The TSFind function has an optional parameter for the "offset", which is a number of records to skip over. Zero is the default, and it means "don't skip over any records". If you have three re ...
9. My Office Connector formula does not return the correct results when the ID field has a space in it.
   This can be caused if you forget to enclose the ID in single quotes (this applies to text IDs). For example: “(TPAYID = ‘01’) AND (TEMPID = “ & A2 & “)” Should be “(TPAYID = ‘01’) AND (TEMPID = ‘“ & A2 & “’)”
10. When Should I Use Filter vs Where?
    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 lo ...
11. Why do I receive a message indicating that the cache could not be saved?
    Symptom When running a macro that includes a step to save the workbook, the following message may be displayed: The cache cannot be saved. Please contact technical support for assistance. Would you like to email this information to the support department at Event 1 Software? <Yes> <No& ...
12. Why do my TSLookup and TSFind functions return zero after optimizing my workbook?
    Symptom After upgrading to Office Connector version 2.01.0041 the following message is displayed when opening a workbook that was saved using a prior version of Office Connector: This workbook was saved using a prior version of Office Connector. This workbook needs to be optimized to run faster ...
13. Why do my TSSum or TSCount functions return the wrong value after optimizing my workbook?
    Symptom After upgrading to Office Connector version 2.01.0041 or higher, some workbook formulas return the wrong amounts. Cause There are two approaches that Office Connector can use to filter Timberline data. The first approach queries Timberline data in bulk, and then uses Microsoft technol ...
14. Why does my query reappear after I delete it?
    Symptom After deleting query data, it reappears the next time you refresh the workbook. Cause It is possible to remove the query data from the worksheet without removing the query definition itself. So long as the query definition remains, data will reappear when you refresh the workbook. R ...