Digital Workshop

Q: Using the advice in the 'Databases - Part 1' tutorial, I have successfully applied a Database Query action to display records from a database. How do I allow the user to input a search string and display any matching records in the database?

A: Please find below a step-by-step procedure for modifying the publication you created in the first tutorial to allow searching:

1) Open the publication you created in the first tutorial
2) Right-click on the page and select Edit Actions
3) Delete the On Show trigger and Database Query action
4) Click OK to return to the editor

5) Now add a Text Input box to the page
6) Open the properties of the Text Input box, select the Text Input tab and click on the "New" button
7) Create a new publication variable named SEARCH
8) Click OK twice to return to the editor
9) Add a button next to the Text Input box
10) In the Button Actions, click on the Actions tab, then the Database tab and select Database Query. This should open a Database Query tab
11) Click on the Select button next to the DSN drop-down menu
12) In the Select Data Source window, click New
13) Select Microsoft Access Driver (*.mdb) from the list of drivers and click Next >
14) You can name this connection what ever you choose. Make a note of the name given and click Next >
15) You will now be shown a summary of the changes made, just click OK to confirm
16) You will now need to specify which database to use. Click on Select and navigate to you database. Once selected, click OK
17) Ensure that the DSN you have just created is highlighted in the list and select OK
18) You will now return to the Database Query tab, but the DSN will be set to FILEDSN=<your_DSN_name>.dsn
19) Click on SQL Wizard and remove the tick from the Select Data for Updating/Deleting (you can experiment with this later) and click Next >
20) From the table drop-down list, select the table in which your required data resides, ensure that all of the field names are ticked, then click Next >
21) This page allows you to apply SQL operators to filter the data. Set the "Where Field" setting to the first field in your database (NAME)
22) Select IS LIKE as the operator
23) Set the Compare To option to "Variable", then select SEARCH from the drop-down menu
24) Click Next
25) Select OR and click Next
26) Change the Where Field to AGE, set the operator to IS LIKE and set the variable SEARCH as the Compare To option. Click Next, select OR, and click Next
27) Repeat this procedure once more for the OCCUPATION field
28) This page allows you to sort the results by field. Leave the Field setting as (None) and click Finish
29) You will now return to the Database Query tab, but the Query name will read Query2
30) At the bottom of this window is a grid showing all of the variables assigned to your Field names. At present there are no variables assigned
31) Click on the box next to NAME (which currently reads None) and an Insert Variable window will open. Click New and type in NAME
32) Repeat Step 31 for your other Field names, calling their associated variables AGE and OCCUPATION respectively
33) Click Apply then OK to return to the main editor window
34) Preview the publication
35) You should now only be able to see the text input box and the button. The three text boxes should not be visible
36) Type a search term into the text input box and click the button. Opus will search your database for like values and display the first matching record within the three text boxes

You can add navigate to the previous and next records by adding two buttons to the page, then applying a Previous Record action to one button and a Next Record action to the other. When applying these actions, you will be asked to which query these actions should refer - simply select the same query that was used in your original Database Query action.