PC SOFT

ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Home | Sign in | English EN
New WINDEV 26 feature!
  • In this lesson you will learn the following concepts
  • Overview
  • Creating the query used to find orders
  • Creating the query
  • Query test
  • Using parameters in the query
  • Test of the query with parameters
  • Creating the interface fo a multi-criteria search
  • Modifying the Tab control
  • Creating the controls used to configure the criteria and to display the result
Lesson 4.3. Multi-criteria search
In this lesson you will learn 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 multi-criteria search.
In our example, this search will be done on the "Order" data file. The user will be able to select:
  • the order status,
  • the payment method,
  • the order dates taken into account.
The interface of the "WIN_Menu" window is as follows:
Interface of WIN_Menu
This interface includes:
  • controls used to select the search criteria.
  • a Button control to start the search.
  • 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.
  • Open the project you worked on in the previous lesson.
    1. Go to the WINDEV home page (Ctrl + <).
    2. On the home page, click "Tutorial", then in "Part 4 - Full application with data", double-click "Full application (Exercise)".
    3. A dialogue box prompts you to open the project you worked on in the previous lesson. You can open the local copy or the original project. Select "Open the local copy".

Answers

A corrected project is available. This project contains the windows and queries created in this lesson. To open the corrected project, go to the home page and click "Tutorial", then in "Part 4 - Full application with data", double-click "Full application (Answers)".
Creating the query used to find orders

Creating the query

  • The query editor will be used to create the query.
    1. Click Create an element in the quick access buttons. The element creation window appears: click "Query­". The query creation wizard starts.
    2. Select the "Select" option.
      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 "Order" data file (click Arrow ) and double-click the items: OrderID, 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 "Order.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.
        Add a sort
      • Validate.
    6. A blue arrow with the number 01 appears to the right of "Order.Date". 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 default information.
    10. The graphic query representation is displayed:
      Graphic representation

Query test

Like all the elements of a WINDEV project, you can directly test the query that we have 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.

    Note

    When you right-click the query result, a context menu appears. 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 the query editor (or select "Query description" in the context menu).
  • To manage the "order status" parameter:
    1. Select the Order.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

        Note

        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 "Order.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 "Order" 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 View item on the right of the item.
      • In the menu that appears, select "Don't display".
    3. To define a selection condition on the "Order.PaymentModeID" item:
      • Select the "Order.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 selection condition.
      Set 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 "Order.Date" item.
    2. Click "Between two dates ...". 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 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 in the quick access buttons.

Test of the query with parameters

  • To test the query with parameters:
    1. Click Test query.
    2. A window is displayed, allowing you to type the different query parameters.
    3. Enter 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 fo a multi-criteria search

Modifying the Tab control

We will add a tab pane to the "WIN_Menu" window to display the result of the multi-criteria search.
  • To add a tab pane:
    1. Open (if necessary) the "WIN_Menu" window in the editor.
    2. Select the Tab control.
    3. Open the control description window (select "Description" in the context menu).
    4. On the "General" tab of the description window, click "New". A third tab pane appears.
    5. Click "Pane 3".
    6. In the "Static pane description" section, enter: "Finding orders".
      Tab control description
    7. We are going to associate an image to the tab pane via the WINDEV image catalog. Click Show image options on the right of "Image". Select "Catalog" in the context menu that appears. 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 default options in the settings screen of the 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 will now display the result of our multi-criteria 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 (created previously). Select "Display data from a file or 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 the Table control so that it is entirely displayed in the tab pane.
      Window in the editor
  • For better readability, we will change the text of the columns in the Table control.
    1. Open the Table control description (double-click the control).

      Note

      The description window of a Table control includes two sections:
      • the upper section, containing the control name, as well as the name and type of the columns.
      • the lower section, containing the different description tabs.
      If the name of the Table control is selected, the lower section presents the characteristics of the Table control.
      If a column is selected, the lower section shows the characteristics of the columns.
    2. Click the "COL_OrderID" column. The column title is displayed in the lower section of the screen. Replace "Identifier of Order" with "ID".
      Column titles
    3. Click the "COL_FullName" column. Replace "Full name" by "Customer".
      Column titles
    4. Click the "COL_Caption" column. Replace the "Caption" caption by "Payment mode".
      Column titles
    5. Validate the Table control description window. These changes are automatically applied to the control.
    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 in the quick access buttons. We will check the size of the 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 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 Test window in 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 we specified the parameters in the query test window.
      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. Right-click to open the context menu 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 "X" in the upper-right corner).
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 of the "Order" data file. This item is a radio button.
To allow the user to select one of these three states, we will use the Radio Button control associated with the "Status" item of the "Order" data file.

Note

Radio buttons are also called "option boxes". They are used to select a single option in a list of options.
How to differentiate between a radio button and a check box?
We will only refer to option boxes as "Radio buttons". Here is an easy reminder: think of old radios: a single frequency could be selected via the button!
Radio Buttons are used to select a single option.

  • To create the Radio Button control:
    1. Open 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 "Order" data file: the items found in the data file are listed.
    3. Select the "Status" item in the Order data file, then drag and drop this item in the "WIN_Menu" window
    4. The Radio Button control is automatically created. Position this control above the Table control.
  • By default, the Radio Button control only displays the available options. In our case, we will display the caption of the Radio Button control. To do so, you must simply select a style from the project skin template that allows displaying the caption of the control. To change this style:
    1. Select the Radio Button control if necessary.
    2. Right-click to open the context menu and select "Choose a style".
    3. The window that appears shows all the styles defined for Radio Button controls.
    4. Select the "RADIO_Simple" style.
    5. Validate. The style is immediately applied to the control in the editor.
      Window with Radio Button control in the editor
  • We will now pass the value selected in the Radio Button control as parameter to the query:
    1. Open the WLanguage events associated with the Table control:
      • Select the Table control.
      • Right-click to open the context menu 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 the 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 Create 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 text can be edited. Type "Find" and press Enter on the keyboard.
    3. Modify the control style:
      • Right-click to open the context menu of the control 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. Open the WLanguage events associated with this control: press F2.
    6. Write the following WLanguage code in the "Click..." event:
      // Refreshes the Table control
      TABLE_QRY_FindOrders.Display(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 in the quick access buttons.
    2. Click Test window in the quick access buttons.
    3. Select the "Finding orders" tab pane if necessary.
    4. Change the order status with the radio button and click "Find". The content of the 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.

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, 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 as 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 the Combo Box control:
    1. Select the Combo Box control and open the description window (select "Description" in the context 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 the description window). In this case, enter "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. Open the WLanguage events associated with the Table control:
      • Select the Table control.
      • Right-click to open the context menu 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 in the quick access buttons.
  • We are now going to check how the first two parameters are passed:
    1. Click Test window in 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 the 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 the orders taken into account. The user must be able to enter 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 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 (select "Description" in the context 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. Open the WLanguage events associated with the Table control:
      • Select the Table control.
      • Right-click to open the context menu and select "Code".
    2. In the "Initializing" event of the Table control, replace the lines:
      MySource.ParamStartOfPeriod = "20160101"
      MySource.ParamEndOfPeriod = "20160331"
      with:
      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 element in the quick access buttons.
  • We are now going to check how the parameters are passed:
    1. Click Test window in the quick access buttons.
    2. Select the "Finding orders" tab if necessary.
    3. Define the different search parameters:
      • Order status,
      • Payment method,
      • Range of dates to take into account.
    4. Click on the "Find" control. The content of the Table control is modified.
      Window test with multi-criteria search
    5. Close the test window.
Previous LessonTable of contentsNext Lesson
Minimum version required
  • Version 26
Comments
Click [Add] to post a comment