This is a continuation of the DataBlock design which began in example 1. The form has been designed; the next step is to create the query to obtain data from the database and display the query results on the form.
The query results are to be displayed on the form into a multi-column list box. The query to be created is called the Form Query since it is created within the Form Design tab inthe Argos DataBlock Designer.
Create the multi-column list box to contain the query results
The next step is to create the multi-column list box at the bottom of the form which will contain the results of the query. Click the multi-column list box icon on the toolbar, then click anywhere within the Design Area. Increase the size of the object (horizontally and vertically) to accommodate the data that will populate the object. This is done by dragging the corners of the object to the desired locations.
As was done with the previously created multi-column list box, double-click on the new object, select “SQL statement” (to populate the list box with results of an SQL query), click the hard hat hammer to bring up the Build Query dialog box, then click Show Tables.
The columns in the report will contain the:
employee last name
employee first name
order date
product name
sales amount (quantity x unit cost)
These items are located in several tables within the sample database (Employees, Orders, Order_Details, Products), thus the SQL query must contain a join to link the tables together. Note that the sales amount does not exist within the database and must be calculated using the quantity and unit cost fields within the database. The use of the Build Query dialog box to build the appropriate SQL query follows.
At this point the Build Query dialog box appears as shown below. The next step is to select the tables to be included within the query.
Form Queries
Although this examples uses only one object (and its associated query) to display results on the form, multiple Form Queries could be created if several objects require display of query results.
Figure 32 – Showing all database tables within the Build Query dialog box
Double-click the Employees, Orders, Order_Details, and Products tables. This moves the tables to the right where you can join them to each other. Maximize the screen to provide space. Drag the tables to position them as shown in the figure below.
Figure 33 – Selecting the tables applicable to this query The following joins need to be created:
Employees.emp_id joined with Orders.employee-id Orders.transaction_id joined with Order_Details.transaction_id Order_Details.product_id joined with Product.product_id
This is done visually by selecting the field in one table, holding the mouse down, then dragging the mouse to the corresponding field in the other table. Lines indicating the joins are then displayed as shown below:
Figure 34 – Joining the tables within the Build Query dialog box
Identify the fields to appear within the report
The next step is to specify which database fields will appear in the report. Double-click on each field to be included. This moves each field underneath the Visible Fields tab as shown below. These fields will also appear in SELECT statements in the SQL that is being created.
The employee last name, first name, sale date and product name were selected and appear within the Visible Fields tab.
Figure 35 – Identifying the fields to be visible within the report
Create a calculated field to determine sale amount
The report must show the total sale amount, which is not a field in the database, so it must be calculated. The total sale amount is equal to the product quantity multiplied by the unit cost, which are fields in the database. A calculated field must be created and added as an entry underneath the Visible Fields tab.
Within the Visible Fields tab, click the empty field to the right of “Products” and use the SQL Editor as shown below to create the calculated field.
Modifying the joins
To modify a join, place the cursor on the line joining the two tables,then right click. The following options will appear:
Selecting “Edit Join” will allow you to change the type of join as shown below
SELECT/WHERE/ORDER BY tabs
The Visible Fields (SELECT) tab contains the fields that will appear in the report. These fields will also exist in SELECT statements.
The Conditional Fields (WHERE) tab contains the fields that appear in the WHERE clause of the SQL.
The Ordering (ORDER BY) tab determines the sort order of fields that the query returns.
Figure 36 – Entering the calculated field into the SQL Editor
Figure 37 – The calculated field shown within the Visible Fields tab
The new calculated field named sale_amount can now be used in the same fashion as other fields in the database.
Click OK, then Next to continue. You will then see the new multi-column list box object shown within the DataBlock Designer with all of the fields added.
Figure 38 – The multi-column list box to contain the query results
Use input selections to limit the query
This query in its current state will return all sales records for all employees. However the query is to be limited by the input selections entered on the form. Therefore, a WHERE clause is required to limit the query to selected employees within a date range.
Centering an object
To center an object on the form, click the object. two of the icons on the alignment toolbar will then become active as shown below.
These two icons are highlighted when only one object is selected. After clicking the desired icon, the object will be centered within its parent region horizontally or vertically.
All alignment icons become active when two or more objects are selected. In this case, objects are aligned with respect to each other.
Within the DataBlock Designer, double-click on the object that was just created which contains the results of the query. Then click the hardhat/hammer (Edit Visual Design) icon to bring up the Build Query dialog box. Click the “Conditional Fields (WHERE) tab where you can create the required WHERE clause.
The WHERE clause should only obtain records where:
The employee last name selected = the employee last name in the database and, The employee first name selected = the employee first name in the database and, The sale date is greater than or equal to the start date selected and,
The sale date is less than or equal to the end date selected
The above limits the query to the selections entered by the person executing the report.
Each of the above conditions will be entered under the Conditional Fields tab as follows:
Figure 39 – The Conditional Fields (WHERE) tab in the Build Query dialog box
The condition for last name will be created first. Enter the data shown in the figure below in the “and/or, Table, Field” locations, then click “Condition” beneath them.
Figure 40 – adding last name to the condition
Click the ellipsis, then the abc icon which displays the variables that exist within the DataBlock. Note that the list of variables contains the first name and last name under the EmployeeList object (the multi-column list box where the user selects employees of interest). The variable list also contains the StartDate and EndDate objects. When the report is executed, each of these variables contains the selection made by the person executing the report.
Figure 41 – Selecting the last name variable to be used in the Condition
Select last_name as shown above. Recall that EmployeeList is the name of the multi-column list box containing the list of employee names. The two fields within the EmployeeList object are last_name and first_name. Click OK to continue.
Figure 42 – The last name condition within the SQL Editor Click OK to continue
Figure 43 – The completed condition for last name
The condition for last name has been created as shown in the above figure.
Click on the blank field to the right of the field just created and repeat the process for the employee first name.
Figure 44 – Adding the condition for first name
To add the date conditions, click on the empty fields on the right of “employees first name” to create conditions where sale date is greater than or equal to the input start date and sale date is less than or equal to the input end date. Use the same methods that were used to create conditions for last name and first name.
Note the StartDate and EndDate variables that are displayed in the list of variables. It is now very apparent why giving meaningful names to variables is useful.
Figure 45 – The complete variable list
Figure 46 – Creating the conditions for start date and end date
Click OK then Next to complete the design of the WHERE clause and the entire form/query design. You are now back in the main screen of the DataBlock Designer. Click Commit to save your work, then click the green right arrow to test.
Saving your work
Remember to click the Commit button on the top left of the Visual Designer to periodically save your work.
Use of button to control the execution of the query
When you execute the QuickView report, you will notice that the query results are shown as soon as you finish selecting the employee name and dates, not giving you a chance to change selections before executing the report. This is why the GO button was created; to prevent the query from executing until the GO button is clicked.
To remedy this situation, edit the DataBlock (bringing up the DataBlock Designer) and add a button by clicking the button icon on the toolbar (the icon with OK). A button will then be placed on the form. Move the button to the desired location. Change the variable name of the Button to “GoButton” within the properties area of the DataBlock Designer.
Change the text displayed on the button to “Go” by editing the Text property.
Double-click on the object that displays the query results (the multi-column list box at the bottom), click the hardhat/hammer, then select the “Conditional Fields (WHERE) tab.
Within the WHERE clause that has already been created, in the empty column in the right, a calculated field will be created to inhibit execution of the query until the Go Button is clicked. Create the condition shown below
Figure 47 – Creating the condition for the GO Button
Until the button is clicked, the variable representing the state of the button object is null, and the query will not execute. When clicked, the value is no longer null and the query will then execute.
Summary
The final SQL for this example appears below. Note the existence of the variable names (in blue) and the fields contained in the select and where statements.
The Button icon on the toolbar
Figure 48 – The final query for the query display
The form and query design for this example is now complete.
If desired, return to the Argos Main Interface, right-click on the DataBlock and create a QuickView report for this DataBlock. The figure below shows the QuickView report after execution.
Figure 49 – The QuickView report