ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

New WINDEV 2024 feature!
Help / WINDEV Tutorial / Tutorial - WINDEV application: Managing data
  • Lesson 3 - Multi-criteria search
  • Overview
  • Creating the query to find orders
  • Creating a selection query
  • Query test
  • Defining query parameters
  • First parameter: "Order status"
  • Second parameter: "Payment method"
  • Last parameter: "Order date"
  • Test of the query with parameters
  • Editing the UI to add multi-criteria search
  • Creating the controls to set the criteria and display the result
  • Creating the Table control
  • First parameter: Order status
  • Second parameter: Payment method
  • Last parameter: Order date
  • To sum up

Tutorial - WINDEV application: Managing data

Lesson 3 - Multi-criteria search
We will cover the following topics:
  • 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.
Durée de la leçon 40 mn
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.
Warning
In this lesson, we will use the example we developed in previous lessons.
Creating the query to find orders

Creating a selection query

The first step is to create the record selection query.
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.
To create a selection query, we will use the query editor.
  1. Click Create an element in the quick access buttons. The new element 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 "PaymentMethod" 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 defined on the 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: in "Query name", change "QRY_NoName1" to "QRY_FindOrders":
    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 will only display the orders that match the search criteria. To do so, we must use a query with parameters.
    You can right-click the table that shows the query result to open a context menu. 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.
Defining query parameters
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 will modify the query so that these search criteria match 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).

First parameter: "Order status"

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, we 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

      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 to the right of the "Order.Status" item, indicating that a selection condition has been defined.
    Condition in the query description

Second parameter: "Payment method"

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 "PaymentMethodID" 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.PaymentMethodID" item:
    • Select the "Order.PaymentMethodID" 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: "ParamPaymentMethodID".
  4. Validate the selection condition.
    Defining the selection condition

Last parameter: "Order date"

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. Define the following data:
    • Uncheck the ParamStatus parameter.
    • Select the ParamPaymentMethodID parameter. In the lower section of the screen, enter "1".
    • Select the ParamStartOfPeriod parameter. In the bottom section of the window, enter "01/01/2023".
    • Select the ParamEndOfPeriod parameter. In the bottom section of the window, enter "03/31/2023".
      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.
Editing the UI to add multi-criteria search
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 tab 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 search icon 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 that will 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". Proceed 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
    • Proceed to the next step of the wizard.
  6. Select all suggested items if necessary.
    Items to display
  7. Proceed 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).
    The description window of the 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 you click the name of the Table control, the lower section shows the characteristics of the table.
    If you click a column, 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" with "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.
Live Data and controls based on queries
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 "Initialization of TABLE_QRY_FindOrders" event contains the following code:
    // Parameters of 'QRY_FindOrders' query
    //MySource.ParamStatus = <Value of ParamStatus>
    MySource.ParamPaymentMethodID = "1"
    MySource.ParamStartOfPeriod = "20230101"
    MySource.ParamEndOfPeriod = "20230331"
    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:
  • waiting for payment,
  • 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.
Radio buttons allow users to choose only one 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 initialization event of the Table control, replace the following 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 you have 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.
  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 executes the initialization event of the Table control again (the event in which the parameters are passed to the query).
  7. Close the code editor.
We will test the first parameter:
  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 method

Several payment methods can be used for an order: cash, checks, etc. The available payment methods are stored in the "PaymentMethod" data file.
We will use an Combo Box control based on this data file to allow the user to select the desired payment method.
The Combo Box control displays a list of elements and allows users to select an element from the 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.
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 "PaymentMethod" data file and go to the next step.
  6. The item to be displayed in the Combo Box control is "Caption". Uncheck "PaymentMethodID" and check "Caption". Go to the next step.
  7. Select the sort item: "Caption". Go to the next step.
  8. Select the return value: "PaymentMethodID". 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 "PaymentMethod 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 initialization event of the Table control, replace the following line:
    MySource.ParamPaymentMethodID = "1"
    with the code:
    MySource.ParamPaymentMethodID = COMBO_PaymentMethod
    In this code, COMBO_PaymentMethod is the name of the Combo Box control we have just created. The value of this control is associated with the ParamPaymentMethodID 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 will test the first two parameters passed:
  1. Click Test window in the quick access buttons.
  2. Select the "Find orders" tab if necessary.
  3. Change the order status using the Radio Button control and the payment method using 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.
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: you can add code and move or edit the controls in the window that uses the control template.
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 method" 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 initialization event of the Table control, replace the following lines:
    MySource.ParamStartOfPeriod = "20230101"
    MySource.ParamEndOfPeriod = "20230331"
    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 will test the parameters 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.
To sum up
Completed project
Do you want to check the end result of the steps described here?
A completed project is available. This project contains the different windows created in this lesson. To open the completed project, go to the home page and click "Tutorial", then in "Tutorial - WINDEV application: Manage data", double-click "Full application - Answers".
In this lesson, we discovered how to create a query with parameters, and how to create a Table control based on that query. We were able to create various controls to allow users to enter their search values. In the next lesson, we will see how to enable users to print documents containing data from the database.
Previous LessonTable of contentsNext Lesson
Minimum version required
  • Version 2024
Comments
Click [Add] to post a comment

Last update: 12/15/2023

Send a report | Local help