In an earlier article, we introduced prompted views as a way to enable end users by allowing them to retrieve data without the need for code. We discussed how this approach meets the needs of users while freeing time that database administrators might otherwise spend running countless similar queries. Finally, we showed you how to configure default values for your prompts and explained how to use *NOPROMPT variables.
Now that you understand these key concepts, let’s take prompted views to the next level using a database list (DBList) Integrity Test and dependent-on variables.
Tip 1. Use a DBList to create a drop-down list of user-selectable values.
Inputting variables can be tricky, so why not provide a list of valid values the users can select? You can build a DBList from a SEQUEL View or a physical file.
For this example, let’s create a prompted view named CUSTEX1:
SELECT cusno, cname, cstte
FROM sequelex/custmast
WHERE cusno=&cusno
Note: When you define the variable as &CUSNO, SEQUEL ViewPoint creates the variable as a number type. By including a DBList in the variable definition, the runtime prompt provides a drop-down selection list containing all valid customer numbers. By leaving the default value blank, the DBList will provide the default value first at runtime.
A single record selection:
When you run the CUSTEX1 view, the runtime prompt provides a drop-down selection list containing all customer numbers from the CUSTMAST file. The user can select only one of the customer numbers.
Tip 2. Create a multi-selection list.
When you use a multi-selection variable, users can select one or more values from the drop-down list. In the following example, we’ll include the option to select ALL customer numbers.
Modify the prompted view called CUSTEX1:
SELECT cusno, cname, cstte
FROM sequelex/custmast
WHERE cusno IN(&cusno)
Note: Set the variable type to “Expr” (for expression), increase the length to allow multiple values, and select “multi-select” in the integrity test field.
Examples: When you run the view, the variable allows the user to select *ALL for all customers or use the drop-down option to select one or more customers.
Selecting ALL customers:
Selecting 5 customers:
Tip 3. Use a dependent-on variable value.
You can also reference a previous variable’s value to control what values are in this variable’s selection list. For example, if you only want to provide a list of customer numbers based on the state selected in a previous variable, you can add a filter in the second variable.
Modify the prompted view called CUSTEX1:
SELECT cusno, cname, cstte
FROM sequelex/custmast
WHERE cstte = &cstte AND cusno IN(&cusno)
1. First, define the first variable &CSTTE.
2. Second, define the second variable &CUSNO with a FILTER value of CSTTE in(&CSTTE).
Example: When you run the view, the second variable values selection list will change based on the first variable value selected.
By applying DBList and dependent-on variables to your views, users will be able to easily define their search criteria at runtime and quickly retrieve the information they need.
Looking for more Sequel tips and tricks?
Check out the Fortra Community Portal for articles, forums, and product information.