PC SOFT

WINDEVWEBDEV AND WINDEV MOBILE
ONLINE HELP

Home | Sign in | English UK
New WINDEV 25 feature!
  • 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.
Lesson duration

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 method,
  • the order dates taken into account.
The interface of "WIN_Menu" window is as follows:
Interface of WIN_Menu
This interface includes:
  • controls used to select the search criteria.
  • Button controls used to start the search or 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.

Remark

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 WINDEV's 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 Creating an element among the quick access buttons. The element creation window appears: click "Query­". The query creation wizard 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 appears. To build the query, we are going to select the elements that will be displayed in the result.
    4. Double-click the items in the analysis, to the left of the description window. The items taken into account are displayed in the middle of the screen.
      Remark: 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 order payment method. Expand the "PaymentMode" data file (click the arrow Arrow ) and double-click the "Caption" item.
      The query description window 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".
      • In the window that appears, indicate an ascending sort on the item.
        Adding a sort
      • Validate.
    6. A blue arrow with the number 01 appears on the right of "Orders.Date" item. This arrow indicates that an ascending order has been applied on this item. The number "01" indicates that this sort will be applied first.
      Sort in the query description window
    7. Give a name to the query: type "QRY_FindOrders" instead of "QRY_NoName1" in the "Query name" area:
      Query name
    8. Validate the query description window ("OK" button).
    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 Test an element.
  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.

    Remark

    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).
    • an 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.
  • Order payment method.
  • Order date.
We must modify the query in order for these search criteria to correspond to the query parameters.
  • To define the query parameters, open the query description window: 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:
      • Select "Is equal to".
      • Select "the parameter".
      • The name of the parameter is automatically proposed: "ParamStatus".
        Query- Description of a condition

        Remark

        We advise you to keep "Param" as the prefix of the query parameters. This allows you to easily find them in the code editor.
        To find a query parameter, simply type 'Param' and the completion feature of the code editor will propose all the parameters.
    4. Validate the condition description window. The number "1" appears on the right of "Orders.Status" item, indicating that a selection condition was defined.
      Condition in the query description
  • We are now going to define a condition on the payment method. 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 the item 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: "ParamPaymentModeID".
    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: "ParamStartOfPeriod".
      • Click the second "the parameter".
      • Specify the parameter name: "ParamEndOfPeriod".
    4. Validate the definition of selection condition.
    5. Validate the query description window. The query graph is modified to take into account the selection conditions that have been defined.
      Graphic representation of the query
    6. Save the query by clicking Save an element among the quick access buttons.

Test of query with parameters

  • To run the test of query with parameters:
    1. Click Run the query test.
    2. A window is displayed, allowing you to type the different query parameters.
    3. Type the following data:
      • Uncheck the ParamStatus parameter.
      • Select the ParamPaymentModeID parameter. In the lower section of the screen, type "1".
      • Select the ParamStartOfPeriod parameter. In the lower section of the screen, type "01/01/2016".
      • Select the ParamEndOfPeriod parameter. In the lower section of the screen, type "03/31/2016".
        Query parameters
    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 the 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. Open the control description window ("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".
      Tab control description
    7. We are going to associate an image to the tab pane via the WINDEV image catalog. Click the Drop-down menu button on the right of "Image" control. Select "Catalog" from the popup menu that is displayed. The image catalog window appears.
    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 ( Binoculars ) and validate.
    10. Keep the options found in the setting screen of generated image and validate.
    11. Validate the Tab control description window.
      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 the Tab control pane previously selected: the Table control creation wizard 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 step of the wizard.
    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.
        Table control creation wizard
      • Go to the next step of the wizard.
    6. Select all suggested items if necessary.
      Items to display
    7. Go to the next step of the wizard.
    8. Keep the default options in the different steps of the wizard and validate the creation of the Table control.
    9. The Table control is automatically created in the "Finding orders" pane of the Tab control.
    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. Open the Table control description (double-click the control).

      Remark

      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".
      Column title
    3. Click the "COL_FullName" column. Replace the "Full name" caption by "Customer".
      Column title
    4. Click the "COL_Caption" column. Replace the "Caption" caption by "Payment mode".
      Column title
    5. Validate the Table control description window. The control is automatically updated with the modifications performed.
    6. Enlarge the "Date" and "Status" columns in the Table control with the column width handles.
    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 an element among the quick access buttons. We are going to check the sizes of columns by running the window.

Remark

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 depends on the result of the query and can only be known at runtime.
  • We are going to run a first test of this window:
    1. Click Running the test of the window among the quick access buttons.
    2. Click the "Finding orders" tab pane. 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 WLanguage events associated with the Table control:
    1. Select the Table control.
    2. Open the popup menu (right click) and select "Code".
    3. The "Initializing TABLE_QRY_FindOrders" event contains the following code:
      // Parameters of 'QRY_FindOrders' query
      //MySource.ParamStatus = <Value of ParamStatus>
      MySource.ParamPaymentModeID = "1"
      MySource.ParamStartOfPeriod = "20160101"
      MySource.ParamEndOfPeriod = "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:
  • pending,
  • 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.

Remark

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 Arrow 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 with Radio Button control 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 WLanguage events associated with the Table control:
      • Select the Table control.
      • Open the popup menu (right click) and select "Code".
    2. In the "Initializing" event of the Table control, replace the line:
      //MySource.ParamStatus = <Value of ParamStatus>
      with the following WLanguage code:
      MySource.ParamStatus = 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 ParamStatus parameter expected by the query.
    3. Close the code editor.
  • Before running the test, we are going to create a Button control 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 Creating a Button control.
      • then click on the "Finding orders" pane of the Tab control, at the top right.
    2. Select the control and press Enter. The control caption becomes editable. Type "Find" and press Enter on the keyboard.
    3. Modify the control style:
      • Open the popup menu of the control (right 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 WLanguage events associated with this control: press F2.
    6. Write the following WLanguage code in the "Click..." event:
      // 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 an element among the quick access buttons.
    2. Click Running the test of the window among the quick access buttons.
    3. Select the "Finding orders" tab pane if necessary.
    4. Change the status of orders with the radio button and click "Find". The content of Table control is modified.
      Window test
    5. Close the test window.
Second parameter: Payment mode
Several payment methods can be used for an order: cash, checks, ... The available payment methods are stored in the "PaymentMode" data file.
We will use an Combo Box control based on this data file to allow the user to select the desired payment method.

Remark

The "Combo Box" control is used to display a list of elements and to select an element from this list.
Unlike a List Box, 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 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 control that was just created for example).
    3. The Combo Box control creation wizard 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 steps of the wizard and validate the creation of the 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 open the description window ("Description" in 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 control description window.
  • 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 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 use the value selected in the Combo Box control to pass it to the query as parameter:
    1. Display the WLanguage events associated with the Table control:
      • Select the Table control.
      • Open the popup menu (right click) and select "Code".
    2. In the "Initializing" event of the Table control, replace the line:
      MySource.ParamPaymentModeID = "1"
      by the code:
      MySource.ParamPaymentModeID = 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 ParamPaymentModeID parameter expected by the query.
    3. Close the code editor.
    4. Save the window by clicking Save an element among the quick access buttons.
  • We are now going to check how the first two parameters are passed:
    1. Click Run the test of a window among the quick access buttons.
    2. Select the "Finding orders" tab pane if necessary.
    3. Change the order status with the Radio Button control and modify the payment method with the Combo Box control, then click "Find". The content of Table control is modified.
      Window test with selection of order status and payment method
    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.

Remark

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 overridden: code can be added, the controls can be moved in the window that uses the control template. The controls can also be modified.
  • To manage the order date:
    1. In the "Project explorer" pane, expand the "Control templates" folder.
    2. Drag the control template named "TPLC_ChoosePeriod" from the "Project explorer" pane and drop it in the "WIN_Menu" window (beside the "Payment mode" control).
      Drag and Drop control template
    3. Select the control template that was just created and open its description ("Description" in 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 use the selected dates to pass them to the query as parameter:
    1. Display the WLanguage events associated with the Table control:
      • Select the Table control.
      • Open the popup menu (right click) and select "Code".
    2. In the "Initializing" event of the Table control, replace the lines:
      MySource.ParamStartOfPeriod = "20160101"
      MySource.ParamEndOfPeriod = "20160331"
      by:
      MySource.ParamStartOfPeriod = EDT_StartDate
      MySource.ParamEndOfPeriod = EDT_EndDate
      In this WLanguage code, EDT_StartDate and EDT_EndDate are the names of two edit controls found in the control template. Their values are associated with the parameters ParamStartOfPeriod and ParamEndOfPeriod expected by the query.
    3. Close the code editor.
    4. Save the window by clicking Save the element among the quick access buttons.
  • We are now going to check how the parameters are passed:
    1. Click Run the test of a window among the quick access buttons.
    2. Select the "Finding orders" tab if necessary.
    3. Define the different search parameters:
      • Status of orders,
      • Payment method,
      • Range of dates to take into account.
    4. Click on the "Find" control. The content of Table control is modified.
      Window test with multi-criteria search
    5. Close the test window.
Previous LessonTable of contentsNext Lesson
Minimum version required
  • Version 25
Comments
Click [Add] to post a comment