Query Examples
Usage: This section will list a step by step instruction on how to create three different advance queries. You may walk through these steps and create these queries in your own environment for future use.
➢Find Customers whose current balance is over $1000.00 Creating this query: 1.Click on Advanced Query Builder icon in Main menu or Tools > Advanced Query 2.Start a NEW QUERY by clicking on . 3.Set Query name to: Customers whose Balance is over $1000. 4.Uncheck the Query limit, as this query should not have a limit of results. 5.Select the Customers table. 6.Skip order expression for now. 7.On the left-hand window, expand Customers and scroll down to select Current Balance. 8.Once the expression window opens, change the 3rd dropdown to Greater Than. 9.Leave this at Fixed Value, and fill out the field so that it says 1000. (No $ necessary) and click OK. 10.Now, go back to the Order expression field on top. Select the explore icon . This field is useful for sorting your information accordingly. In this particular example, lets sort by customers whose TOTAL balance, is higher. 11.Expand Customers – scroll down until you find Total Balance. 12.Double click Total Balance, or select the ADD button. Set it to DESC. Please Note: ASC = Ascending, while DESC = Descending. 13.Select OK, and OK once more in the Sort order window. 14.This should bring you back to the Advanced Query window. 15.Your query should look like this: Example 1 |
➢Find cases that have not been invoiced Creating this query: 1.Click on Advanced Query Builder icon in Main menu or Tools > Advanced Query 2. Start a NEW QUERY by clicking on . 3.Set Query name to: Cases that have not been invoiced. 4.Uncheck the Query limit, as this query should not have a limit of results. 5.Select the Cases table. 6.Skip order expression for now. 7.On the left-hand window, expand Cases and scroll down to select Status. 8.Once the expression window opens, change the 3rd dropdown to Does Not Equal. 9.Leave this at Fixed Value. 10.Select the explore icon 11.Find the Invoiced Status, and select that. Select OK. And again Ok on Add Expression form. 12.Now, go back to the Order expression field on top. Select the explore icon . This field is useful for sorting your information accordingly. In this particular example, lets sort by Customer LAST NAME. 13.Expand Customers – scroll down until you find Last Name. 14.Double click Last Name, or select the ADD button. Set it to DESC. Please Note: ASC = Ascending, while DESC = Descending. 15.Select OK, and OK once more in the Sort order window. 16.This should bring you back to the Advanced Query window. 17.Your query should look like this: Example 2 |
➢ Find cases that have not been invoiced, that have a specified task. Creating this query: 1.Click on Advanced Query Builder icon in Main menu or Tools > Advanced Query 2.Start a NEW QUERY by clicking on . 3.Set Query name to: Un-invoiced cases with ? Task. 4.Uncheck the Query limit, as this query should not have a limit of results. 5.Select the Cases table. 6.Skip order expression for now. 7.On the left-hand window, expand Cases and scroll down to select Status. 8.Once the Add expression window opens, change the 3rd dropdown to Does Not Equal. 9.Leave this at Fixed Value. 10.Select the explore icon . 11.Find the Invoiced Status, and select that. Select OK. 12.Back on the left-hand window, expand Case Tasks. 13.Scroll down until you find Task. 14.Once the Add expression window opens, change the 3rd dropdown to Ask for Value. 15.Click Ok on Add Expression form. 16.Now, go back to the Order expression field on top. Select the explore icon . This field is useful for sorting your information accordingly. In this particular example, lets sort by Customer LAST NAME. 17.Expand Customers – scroll down until you find Last Name 18.Double click Last Name, or select the ADD button. Set it to DESC. Please Note: ASC = Ascending, while DESC = Descending. 19.Select OK, and OK once more in the Sort order window. 20.This should bring you back to the Advanced Query window. 21.Your query should look like this: Example 3 |
See also: