PC SOFT

WINDEVWEBDEV AND WINDEV MOBILE
ONLINE HELP

Home | Sign in | English UK
  • This lesson will teach you the following concepts
  • Overview
  • Creating the query used to find orders
  • Creating the query
  • Query test
  • Using parameters in the query
  • Test of query with parameters
  • Creating the interface used to perform a multicriteria search
  • Modifying the Tab control
  • Creating the controls used to configure the criteria and to display the result
Lesson 4.4. Multicriteria search
This lesson will teach you the following concepts
  • Creating a query with parameters.
  • Creating the interface used to select the search criteria.
  • Passing parameters to a query.
  • Displaying the query result in a Table control.

Estimated time: 40 mn
Previous LessonTable of contentsNext Lesson
Overview
In the previous lesson, we have explained how to perform a search on a single criterion (the customer name). In this lesson, we will give the user the ability to perform a multicriteria search.
In our example, this search will be done on the "Orders" data file. The user will be able to select:
  • the order status,
  • the payment mode,
  • the order dates taken into account.The interface of "WIN_Menu" window is as follows:
WIN_Menu window
This interface includes:
  • controls used to select the search criteria.
  • buttons used to start the search or to print the result.
  • a Table control used to display the search result. This Table control is based on a query. This query will be used to select the records to display. The Table control will list the search result.
The first step consists in creating the query used to select the records.

Note

What is a select query?
A select query is a query that will "choose" the records corresponding to the specified criteria.
This type of query is called a select query because the SELECT command is used in SQL language.

Answer

A corrected project is available. This project contains the different windows created in this lesson. To open the corrected project, in the WINDEV home page (Ctrl + <), click "Tutorial" and select "Full application (With windows)".
Creating the query used to find orders

Creating the query

  • The query editor will be used to create the query.
    1. Click New among the quick access buttons. The window for creating a new element is displayed: click "Query­". The wizard for query creation starts.
    2. Select the "Select" option.
      Indeed, this query will be used to select records. Go to the next step.
    3. The query description window is displayed. To build the query, we are going to select the elements that will be displayed in the result.
    4. Double-click the items found in the analysis on the left of description window. The items taken into account are displayed in the middle of the screen.
      Note: To display the items of different data files, simply click the arrow Arrow in front of the data file name.
      We want to display:
      • information regarding the order. Expand the "Orders" data file (click the arrow Arrow ) and double-click the items: OrdersID, Date, Status and TotalBT.
      • information regarding the customer who placed the order. Expand the "Customer" data file (click the arrow Arrow ) and double-click the "FullName" item.
      • information regarding the payment mode of order. Expand the "PaymentMode" data file (click the arrow Arrow ) and double-click the "Caption" item.
      The description window of query is as follows:
      Query description
    5. The data will be sorted by date.
      • In the list of query elements, select the "Orders.Date" item, then click the "Sort" button and select "Sort on the selected item".
        Adding a sort
      • In the window that is displayed, specify an ascending sort on the item and validate.
    6. A blue arrow with the number 01 appears on the right of "Orders.Date" item. This arrow indicates that an ascending order is performed on this item. The number "01" indicates that this sort will be performed first.
      Sort in the description
    7. Give a name to the query: type "QRY_FindOrders" instead of "QRY_NoName1" in the "Query name" area:
      Query name
    8. Validate the description window of query (green button at the bottom of the screen).
    9. The save window is displayed. Validate the proposed information.
    10. The graphic query representation is displayed:
      Graphic representation

Query test

Like all elements found in a WINDEV project, you have the ability to run the test of query that was just created:
  1. Click Query Go.
  2. The result is displayed in a window:
    Query result
    The result lists ALL orders.
    In our case, we want to display the orders corresponding to the search criteria only. To do so, we must use a query with parameters.

    Note

    A popup menu is displayed when a right click is performed on the query result. You have the ability to export the result to:
    • an XLSX file (Excel).
    • a Word or OpenOffice file.
    • an XML file (eXtensible Markup Language).
    • a HFSQL file.
  3. Close the window.

Using parameters in the query

In our example, the user will be able to select a value for the following search criteria:
  • Order status.
  • Payment mode of order.
  • Order date.
We must modify the query in order for these search criteria to correspond to the query parameters.
  • To define the query parameters, display the description window of query: double-click the background of graphic query representation (or select "Query description" from the popup menu).
  • To manage the "order status" parameter:
    1. Select the Orders.Status item (in the middle of the screen).
    2. Expand "Selection condition" and select "New condition".
    3. In the window that is displayed, we are going to specify that the selection condition corresponds to a parameter:
      Describing a condition
      • Select "Is equal to".
      • Select "the parameter".
      • Specify the parameter name: "pStatus".

        Note

        We advise you to prefix the parameters of queries with the letter "p". This allows you to easily find them in the code editor.
        To find a query parameter, all you have to do is type 'p' and the completion feature of code editor will propose all parameters.
    4. Validate the description window of condition. The number "1" appears on the right of "Orders.Status" item, indicating that a selection condition was defined.
      Describing the condition
  • We are now going to define a condition on the payment mode. This item is not found in the query result but a condition will be applied to it. To do so, the item will be included in the query result and it will be made invisible so that it is not visible in the result.
    1. On the left part of the query description window, in the "Orders" data file, double-click on the item "PaymentMethodID". The "PaymentModeID" item appears in the list of query elements.
    2. To avoid displaying this item in the result:
      • Click the Viewing icon found on the right of item.
      • In the menu that is displayed, select "Don't display".
    3. To define a selection condition on the "Orders.PaymentModeID" item:
      • Select the "Orders.PaymentModeID" item (in the middle of the screen).
      • Expand "Selection condition" and select "New condition".
      • In the window that is displayed, specify that the selection condition corresponds to a parameter:
        • Select "Is equal to".
        • Select "the parameter".
        • Specify the parameter name: "pPaymentModeID".
    4. Validate the definition of selection condition.
      Defining the selection condition
  • The last selection condition to define affects the order date. This date must be included between two dates typed by the user.
    1. In the list of query elements, select the "Orders.Date" item.
    2. Click the "Between two dates ..." button. This button allows you to define a selection condition.
    3. In the window that is displayed:
      • The selection condition is "Is included between".
      • Click "the parameter".
      • Specify the parameter name: "pStartOfPeriod".
      • Click the second "to parameter".
      • Specify the parameter name: "pEndOfPeriod".
    4. Validate the definition of selection condition.
    5. Validate the description window of query. The query graph is modified to take into account the selection conditions that have been defined.
      Graphic representation
    6. Save the query by clicking Save among the quick access buttons.

Test of query with parameters

  • To run the test of query with parameters:
    1. Click Query Go.
    2. A window is displayed, allowing you to type the different query parameters.
    3. Type the following data:
      • Uncheck the pStatus parameter.
      • Select the pStartOfPeriod parameter. In the lower section of the screen, type "01/01/2016".
      • Select the pEndOfPeriod parameter. In the lower section of the screen, type "03/31/2016".
        Query parameters
      • Select the pPaymentModeID parameter. In the lower section of the screen, type "1".
    4. Validate the window. The query result corresponding to the specified parameters is displayed.
    5. Close the window.
We are now going to create the interface used to specify the parameters of this query, to run it and to display the result.
Creating the interface used to perform a multicriteria search

Modifying the Tab control

We are going to add a tab pane into the "WIN_Menu" window to display the result of multi-criteria search.
  • To add a tab pane:
    1. Display (if necessary) the "WIN_Menu" window in the editor.
    2. Select the Tab control.
    3. Display the description window of control ("Description" from the popup menu).
    4. In the "General" tab of description window, click the "New" button. A third tab pane appears.
    5. Click "Pane 3".
    6. In the "Description of static pane" section, type the pane caption: "Finding orders".
      Describing a pane
    7. We are going to associate an image to the tab via the image catalog of WINDEV. Click the Drop-down menu button on the right of "Image" control. Select "Catalog" from the popup menu that is displayed. The window of image catalog is displayed.
    8. In the search area, specify "Search". Click the magnifier to start the search.
    9. Among the proposed images, select the icon representing the binoculars ( ) and validate.
    10. Keep the options found in the setting screen of generated image and validate.
    11. Validate the description window of Tab control.
      Tab control in the editor

Creating the controls used to configure the criteria and to display the result

We now want to display the result of our multicriteria search.
We are going to create a Table control based on the query then to create the different controls allowing the user to select the search criteria.
Creating the Table control
  • To create the Table control used to display the search result:
    1. In the "WIN_Menu" window, click the "Finding orders" pane. The empty tab pane appears.
    2. Create a Table control: on the "Creation" pane, in the "Data" group, expand "Table and list box" and select "Table (Vertical)".
    3. Click in the tab pane: the wizard for creating the Table control starts.
    4. The Table control will be based on the "QRY_FindOrders" query (that was created beforehand). Select "Display the data found in a file or in an existing query". Go to the next wizard step.
    5. Select the query that will be used as data source for the Table control:
      • Expand the "Queries" group if necessary.
      • Select the "QRY_FindOrders" query.
        Wizard for creating a Table control
      • Go to the next wizard step.
    6. Select all suggested items if necessary.
      Items to display
    7. Go to the next wizard step.
    8. Keep the default options in the different wizard steps and validate the creation of Table control.
    9. The Table control is automatically created in the "Finding orders" tab.
    10. Modify (if necessary) the position of Table control so that it is entirely displayed in the tab pane.
      Window in the editor
  • For better legibility, we are going to rename the captions of columns in the Table control.
    1. Display the description of Table control (double-click the control).

      Note

      The description window of a Table control includes two sections:
      • the upper section, presenting the name of control, the name of columns as well as their type.
      • the lower section, containing the different description tabs.
      If the name of Table control is selected, the lower section presents the characteristics of Table control.
      If a column is selected, the lower section presents the characteristics of columns.
    2. Click the "COL_OrdersID" column. The column title is displayed in the lower section of the screen. Replace the "Identifier of Orders" caption by "ID".
      Title of columns
    3. Click the "COL_FullName" column. Replace the "Full name" caption by "Customer".
      Title of columns
    4. Click the "COL_Caption" column. Replace the "Caption" caption by "Payment mode".
      Title of columns
    5. Validate the description window of Table control. The control is automatically updated with the modifications performed.
    6. Enlarge the Date and Status columns in the Table control with the sizing handles of columns.
    7. Reduce the "ID" and "Payment mode" columns in order for all columns to be displayed in the Table control.
    8. Save the window by clicking Save among the quick access buttons. We are going to check the sizes of columns by running the window.

Note

Live Data and controls based on queries
The Live Data is not displayed in the controls that use a query as data source for the following reason: the data displayed in the control depends on the query result therefore it is known at run time only.
  • We are going to run a first test of this window:
    1. Click Window Go among the quick access buttons.
    2. Click the "Finding orders" tab. Only some orders are displayed, like during the last query test run in the editor, when parameters were specified in the test window of query.
      Window test
    3. Close the test window to go back to the editor.
  • Let's take a look at the events associated with the Table control:
    1. Select the Table control.
    2. Display the popup menu (right mouse click) and select "Code".
    3. The "Initializing TABLE_QRY_FindOrders" event contains the following code:
      // Parameters of 'QRY_FindOrders' query
      //MySource.pStatus = <Value of pStatus parameter>
      MySource.pPaymentModeID = "1"
      MySource.pStartOfPeriod = "20160101"
      MySource.pEndOfPeriod = "20160331"
      The test parameters have been retrieved as default parameters for the execution. We are now going to modify the window in order for the parameters to be typed by the user, via controls.
    4. Close the code editor (click the cross in the top right corner of editor).
We are now going to create in our window the different controls allowing the user to select the query parameters. These controls will be positioned above the Table control.
  • Move (if necessary) the Table control in the window and reduce its height in order to get available space for creating the different controls.
First parameter: Order status
Three states can be assigned to an order:
  • waiting for payment,
  • paid,
  • canceled.
In our analysis, the order status is saved in the "Status" item found in the "Orders" file. This item is a radio button.
To allow the user to select one of these three states, we are going to use the Radio Button control associated with the "Status" item of "Orders" data file.

Note

The radio buttons are also called "option box". They are used to select a single option among the proposed ones.
How to differentiate between a radio button and a check box?
We will only refer to option boxes as "Radio buttons". An easy way to remember: think of old radios: a single frequency could be selected via the button!
The radio button is used to select a single option.
  • To create the Radio Button control:
    1. Display the "Analysis" pane if necessary: on the "Home" pane, in the "Environment" group, expand "Panes" and select "Analysis". The different data files described in the "WD Full Application" analysis appear in the pane.
    2. Click the icon next to the "Orders" data file: the items found in the data file are listed.
    3. Select the "Status" item in the Orders data file, then drag and drop this item into the "WIN_Menu" window
    4. The Radio Button control is automatically created. Position this control above the Table control.
Window in the editor
  • We are now going to pass the value selected in the Radio Button control in parameter to the query:
    1. Display the events associated with theTable control:
      • Select the Table control.
      • Display the popup menu (right mouse click) and select "Code".
    2. In the "Initializing" event of the Table control, replace the line:
      //MySource.pStatus = <Value of pStatus parameter>
      by the following code:
      MySource.pStatus = RADIO_Status
      In this code, RADIO_Status is the name of the Radio Button control that was just created. The value of this control is associated with the pStatus parameter expected by the query.
    3. Close the code editor.
  • Before running the test, we are going to create a button used to re-display the content of Table control according to the value selected in the Radio Button control:
    1. Create a Button control:
      • on the "Creation" pane, in the "Usual controls" group, click Button control.
      • then, click at the top right of the pane.
    2. Select the control and press Enter on the keyboard. The button caption becomes editable. Type "Find" and press Enter on the keyboard.
    3. Modify the control style:
      • Display the popup menu of control (right mouse click) and select "Choose a style".
      • In the window that is displayed, press Ctrl + F. In the search control, type "BTN_Search".
      • The style is automatically selected. Validate.
    4. Resize the control if necessary.
    5. Display the code associated with this control: press F2.
    6. Write the following code in the event "Click...":
      // Refreshes the display of Table control
      TableDisplay(TABLE_QRY_FindOrders, taInit)
      In this code, the taInit constant is used to re-run the "Initializing" event of the Table control (the event in which the parameters are passed to the query).
    7. Close the code editor.
  • We are now going to check how the first parameter is passed:
    1. Save the window by clicking Save among the quick access buttons.
    2. Click Window Go among the quick access buttons.
    3. Select the "Finding orders" tab if necessary.
    4. Change the status of orders via the radio button and click the "Find" button. The content of Table control is modified.
      Window test
    5. Close the test window.
Second parameter: Payment mode
Several payment modes can be used for an order: cash, checks, ... The available payment modes are stored in the "PaymentMode" data file.
We will be using a Combo Box control based on this data file to allow the user to select the requested payment mode.

Note

The "Combo Box" control is used to display a list of elements and to select an element from this list.
Unlike a List Box control , a Combo Box control is not expanded: the Combo Box control expands on request or when the cursor is positioned on the control input area.
The elements displayed in a Combo Box control can be defined when creating the control in the editor. These elements:
  • are defined by programming.
  • come from a data file or from a query.
  • To create a Combo Box control:
    1. On the "Creation" pane, in the "Usual controls" group, click "Combo Box".
    2. Click the location where the control must be created in the window (beside the radio button that was just created for example).
    3. The wizard for creating the Combo Box control starts.
    4. Select "Display the data found in a file or in an existing query" and go to the next step.
    5. Select the "PaymentMode" data file and go to the next step.
    6. The item that will be displayed in the Combo Box control is "Caption". Uncheck "PaymentModeID" and check "Caption". Go to the next step.
    7. Select the sort item: "Caption". Go to the next step.
    8. Select the return value: "PaymentModeID". This return value is very important because it will be passed in parameter to the query. Go to the next step.
    9. Keep the default options in the different wizard steps and validate the creation of Combo Box control.
    10. The Combo Box control is automatically created in the window.
  • We are going to modify some characteristics of Combo Box control:
    1. Select the Combo Box control and display the description window of control ("Description" from the popup menu).
    2. In the "General" tab, modify the control caption: replace "PaymentMode combobox" by "Payment mode".
    3. In the "Content" tab, specify the initial value displayed by the Combo Box control ("Initial value" at the bottom of description window). In our case, type "1". This value corresponds to a payment in cash.
    4. Validate the description window of control.
  • Change the control style: to occupy less space, we are going to select a style that displays the caption above the control.
    1. Select the Combo Box control.
    2. In the popup menu (right mouse click), select "Choose a style".
    3. In the window that is displayed, select the "COMBO_Internal" style and validate.
    4. Reduce the control size.
  • We are now going to pass the value selected in the Combo Box control in parameter to the query:
    1. Display the events associated with theTable control:
      • Select the Table control.
      • Display the popup menu (right mouse click) and select "Code".
    2. In the "Initializing" event of the Table control, replace the line:
      MySource.pPaymentModeID = "1"
      by the code:
      MySource.pPaymentModeID = COMBO_PaymentMode
      In this code, COMBO_PaymentMode is the name of the Combo Box control that was just created. The value of this control is associated with the pPaymentModeID parameter expected by the query.
    3. Close the code editor.
    4. Save the window by clicking Save among the quick access buttons.
  • We are now going to check how the first two parameters are passed:
    1. Click Window Go among the quick access buttons.
    2. Select the "Finding orders" tab if necessary.
    3. Change the status of orders via the radio button and modify the payment mode via the Combo Box control, then click the "Find" button. The content of Table control is modified.
      Window test
    4. Close the test window.
Last parameter: Order date
The last query parameter corresponds to the date of orders taken into account. The user must be able to type a date interval. To do so, we are going to use a control template.

Note

A control template is a specific window containing different controls. All types of controls can be included in this window. A control template is a file whose extension is "WDT".
The main benefit of a control template is the re-usability. A control template found in a project can be re-used in any project window.
Furthermore, the control templates can be overloaded: code can be added, the controls can be moved in the window that is using the control template. The controls can also be modified.
  • To manage the order date:
    1. In the project explorer, expand the "Control templates" folder.
    2. Select the control template named "TPLC_ChoosePeriod" and perform a Drag and Drop toward the "WIN_Menu" window (beside the "Payment mode" control).
      Drag and Drop of control template
    3. Select the created control template and display its description ("Description" from the popup menu).
    4. In the description window, rename the control template. The new name is "CTPL_ChoosePeriod".
    5. Validate the description window.
    6. Reposition and align the controls if necessary.
  • We are now going to pass the selected dates in parameter to the query:
    1. Display the events associated with theTable control:
      • Select the Table control.
      • Display the popup menu (right mouse click) and select "Code".
    2. In the "Initializing" event of the Table control, replace the lines:
      MySource.pStartOfPeriod = "20160101"
      MySource.pEndOfPeriod = "20160331"
      by:
      MySource.pStartOfPeriod = EDT_StartDate
      MySource.pEndOfPeriod = EDT_EndDate
      In this code, EDT_StartDate and EDT_EndDate are the names of two edit controls found in the control template. Their values are associated with the pStartOfPeriod and pEndOfPeriod parameters expected by the query.
    3. Close the code editor.
    4. Save the window by clicking Save among the quick access buttons.
  • We are now going to check how the parameters are passed:
    1. Click on Window Go among the quick access buttons.
    2. Select the "Finding orders" tab if necessary.
    3. Define the different search parameters:
      • Status of orders,
      • Payment mode,
      • Range of dates taken into account.
    4. Click the "Find" button. The content of Table control is modified.
      Window test
    5. Close the test window.
Previous LessonTable of contentsNext Lesson
Minimum version required
  • Version 24
Comments
Click [Add] to post a comment