PC SOFT

ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Home | Sign in | English EN
New WINDEV 27 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 of a multi-criteria search
  • Modifying the Tab control
  • Creating the controls to set the criteria and 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 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 allow users 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 UI of the "WIN_Menu" window is as follows:
Interface of WIN_Menu
This UI includes:
  • controls to select the search criteria.
  • a Button control to start the search.
  • a Table control to display the search result. This Table control is based on a query. The query will be used to select the records to be displayed. The Table control will list the search result.
The first step is to create the record selection query.

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 "select query" because of the SQL SELECT command.
  • 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 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 the different data files, simply click the arrow Arrow to the left of the data file name.
      We want to display:
      • the order information. 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.
      • the 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 by 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. The result can be exported 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 appears, will specify that the selection condition corresponds to a parameter:
      • Select "Is equal to".
      • Select "the parameter".
      • The name of the parameter appears automatically: "ParamStatus".
        Query- Condition description

        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 show all the parameters.
    4. Validate the condition description window. The number "1" appears on the right of the "Order.Status" item, indicating that a selection condition has been defined.
      Condition in the query description
  • We will define a condition on the payment method. This item does not appear in the query result but we will still define a condition. To do so, we will add it to the query result, but we will make it invisible so it does not appear in the result.
    1. On the left part of the query description window, in the "Order" data file, double-click "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 appears, 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 between two dates entered 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 appears:
      • Define the condition "Is 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 updates and takes the new selection conditions into account.
      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 appears and allows you to enter the different query parameters.
    3. Enter the following data:
      • Uncheck the ParamStatus parameter.
      • Select the ParamPaymentModeID parameter. In the lower section of the screen, enter "1".
      • Select the ParamStartOfPeriod parameter. In the lower section of the screen, enter "01/01/2017".
      • Select the ParamEndOfPeriod parameter. In the lower section of the screen, enter "03/31/2017".
        Query parameters
    4. Validate the window. The query result corresponding to the specified parameters is displayed.
    5. Close the window.
We will create a UI to specify the query parameters, execute it and display the result.
Creating the interface of 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 appears.
    5. Click "Pane 3".
    6. In the "Static pane description" section, enter: "Find orders".
      Tab control description
    7. We will associate an image to the tab 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 image settings and validate.
    11. Validate the Tab control description window.
      Tab control in the editor

Creating the controls to set the criteria and 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 "Find orders" tab. The empty tab pane appears.
    2. Create a Table control: on the "Creation" tab, 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 "Find 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 displays the characteristics of the table.
      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 "Order ID" with "ID".
      Column titles
    3. Click the "COL_FullName" column. Replace "Full name" by "Customer".
      Column titles
    4. Click the "COL_Caption" column. Replace "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 "Find orders" tab. 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 = "20170101"
      MySource.ParamEndOfPeriod = "20170331"
      The test parameters have been retrieved as default parameters for the execution. We will modify the window to allow users to enter parameters using controls.
    4. Close the code editor (click "X" in the upper-right corner).
We will create the controls that will allow users to select the query parameters. These controls will be created above the Table control.

  • If necessary, move 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 a Radio Button control bound to 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" tab, in the "Environment" group, expand "Panes", select "Panes", and then select "Analysis". The different data files described in the "WD Full Application" analysis appear in the pane.
    2. Click Arrow to the left of the "Order" data file: the items in the data file appear.
    3. Select the "Status" item in the Order data file, then drag and drop it onto the "WIN_Menu" window.
    4. The Radio Button control is automatically created. Move 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, simply select a style in the project skin template to display the text 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:
    1. Create a Button control:
      • on the "Creation" tab, in the "Usual controls" group, click Create a Button control.
      • then click the "Find 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. Change the control style:
      • Right-click to open the context menu of the control and select "Choose a style".
      • In the window that appears, enter "BTN_Search" in the search field.
      • 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 "Find orders" tab if necessary.
    4. Change the order status with the radio button and click "Find". The content of the Table control changes.
      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 through programming.
  • come from a data file or query.

  • To create a Combo Box control:
    1. On the "Creation" tab, in the "Usual controls" group, click "Combo Box".
    2. Click where you want to create the control in the window (e.g., next to the Radio Button control that was just created).
    3. The Combo Box control creation wizard starts.
    4. Select "Display data from a file or 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 will change 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, change 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. Right-click to open the context menu and 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"
      with 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 "Find orders" tab 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 changes.
      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. Control templates use the "WDT" file extension.
The main advantage of a control template is that it can be reused. In a project, control templates can be used in any window.
Furthermore, control templates can be overridden: code can be added, the controls can be moved in the window that uses the control template. Controls can also be modified.

  • To set the order date:
    1. In the "Project explorer" pane, expand the "Control templates" folder.
    2. Drag the "TPLC_ChoosePeriod" control template from the "Project explorer" pane and drop it onto the "WIN_Menu" window (next to 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. Name it "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 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 "Find 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 changes.
      Window test with multi-criteria search
    5. Close the test window.
Previous LessonTable of contentsNext Lesson
Minimum version required
  • Version 27
Comments
Click [Add] to post a comment