Help Files, Tools

Channergy Power Search Utility

Introduction

The Channergy Power Search utility is designed to allow our users to search for any data in the Channergy database.  It allows the use of SQL commands stored in a table in the Channergy database and selected from the Select Search Field combo box.

 

Starting the Channergy Power Search tool

The Channergy Power Search Tool is designed to be installed in the Channergy database folder and launched from the Tools menu.

 

Using Power Search

The Power Search utility is installed with two search scripts, it will search for partial AltOrderNo1 records, such as all Ebay orders with a particular item id, or all orders by Product Number.  Other search queries can be added as needed.

Select Field you want to search in

Select the search field from the Select Search field combo box.

 

Key in the value you want to search for

The two search queries that are installed with the application are designed to support partial searches.  So even if you do not know the whole value that you ar searching for you can key in partial information.

In the case above, we have put in the value of ‘bb’ for the product number search.

Click on the Search Button

The tool will search all of the items in the database that have the search field that contains the value that you searched for.

Click on the Item you want to find in Channergy

When you click on the item in the list that you want to search for in Channergy the value in the first column is copied to the Windows clipboard.

You can then search for the item in Channergy by pasting the value into the related search field in Channergy.  In the example above you can paste the order number into the Order # field in the Find an Order window

Editing and Creating New Searches

The Channergy Power Search utility has the ability to edit, copy or deleting  exiting searches as well as adding new ones.

Editing and creating new queries requires knowledge of SQL and an understanding of the Channergy database structure.  In most cases, it is probably easier to copy an existing Power Search query that is close to what you want and edit it to meet your needs.

This functionality is available in the toolbar.

The functionality of the icons on the toolbar are as follows:

  • – This is the Edit Search Query button.  It loads the SQL into a window and allows you to edit it and test it.
  • -This is the New Search Query button.  It opens the query editor screen and allows you to create and test SQL queries.
  • -This is the Copy button.  This will copy the selected query and create a new one under a different Search Field Name
  • -This button will delete the selected query.

Editing an Existing Power Search

When you click on the Edit Search Query button the application loads the selected query into the Edit Search Query window

The SQL contains the special [PROMPT] code.  This code is replaced by the value keyed in the Search Value field before the query is run.  There are three possible matches that can be done.

  • Exact Search-The query will search for the value that is keyed into the Search Value field
  • Begins With-The query will search for anything that begins with the value keyed into the Search Value field
  • Contains-The query will search for any value that contains the value keyed into the Search Value field.

In the case in the example above, the query uses the Contains criteria (%[PROMPT]%).  So in the example above, the value “bb” was added to the Search Value field so the query will look for any order that has “bb” in the product number.

If you right-click in the query window you will get the context menu showing the prompt options available.

Selecting the option you want will insert the prompt variable at the location where the cursor is located.

Checking the SQL

If you click on the Check SQL button the application will open a new window.

Since the application expects a search criteria, clicking on the Check SQL button without a value in the Search Value field will generate an error.

When you click on the Check SQL button it does two things.

First it checks the SQL code for errors, if there are any, then the Check SQL window will display an error similar to below.

If there are errors, then you can close the window and fix them in the Edit Search Query window and run Check SQL again.

If the SQL syntax is correct, the application will then give you a number of records returned based on your query and Search Value.

This can be a way to check that you are getting a reasonable number of records returned.

Creating a New Power Search

If you click on the New Search button, the application will open the Edit Search Query window

You can then enter a Search Name and the SQL you want to use for the query.

 

Note: You can write a report in the Channergy report writer and copy the SQL code into the Power Search Query window.  Here is how:

  1. Go to the Tools->Preferences window and select the Printers tab.
  2. Check the box next to the Show SQL tab in reports option.
  3. Click OK to save the settings
  4. Create the report that you want to use in the report writer.
  5. Click on the View SQL tab.  
  6. Now you can copy the SQL into the Power Search Query window. 
  7. You will still need to add filter criteria with the [PROMPT]
  8. Also, note, that the Poser Search utility does not currently support dates in the prompt window.

Copying a Power Search

Probably the easiest way to make a new search is to take an existing one that almost meets your needs, copy it and then edit the new one.  If you click on the Copy icon on the toolbar, the application will prompt you for a new query name.

Enter a new search name in the field and click on the OK button.  If the name you selected is already in the system, it will ask you to use a different Search Name

After the new name is created, you will be able to select the new search from the Select Search Field combo box and edit it.

 

 

 

 

 

 

Under The Hood

The Channergy Power Search installer adds a table to the Channergy database called PowerSearch.  The table contains two fields, the SearchField and the SearchSQL.

The SearchField contains the field information that you want to search for.  It is what will load in the Select Search Field combo box when the application starts.

The SearchSQL field is a MEMO field that stores the SQL used for the search.  The SQL contains a variable called [PROMPT] that the power search tool replaces with the value entered in the Search Value field before running the query.

For instance the SQL for the ProductNo search looks like this:

SELECT O.OrderNo,I.ProductNo,O.ShipFirstName,O.ShipLastName,O.ShipCompany,O.ShipAddress1,O.ShipCity,O.ShipState,O.ShipZip FROM Orders O JOIN Items I ON O.OrderNo=I.OrderNo
WHERE I.ProductNo LIKE UPPER(‘%[PROMPT]%’);

When the Search button is clicked the [PROMPT] is replaced by the contents of the Search Value, in the case above where ‘bb’ was the Search Value the SQL would look like this:

SELECT O.OrderNo,I.ProductNo,O.ShipFirstName,O.ShipLastName,O.ShipCompany,O.ShipAddress1,O.ShipCity,O.ShipState,O.ShipZip FROM Orders O JOIN Items I ON O.OrderNo=I.OrderNo
WHERE I.ProductNo LIKE UPPER(‘%bb%’);

Since the ProductNo field in Channergy is always upper case the UPPER function was added to the SQL.  Also note that percent (%) characters on either side of the [PROMPT] are wildcard characters meaning that the database will search for any value that contains the Search Value.

 

support