Skip to main content

Example 3: Select Type

In this scenario, suppose we're interested in finding out the product code, price, and quantity in stock for each product line in our webshop. Our goal is to filter the data in our table according to the selected product line. To achieve this, we'll require two queries:

  • The first query will collect the data into a table and enable us to select the desired product line.
  • The second will be a support query, necessary for the functionality of the dropdown menu. Here's what our resulting data, titled 'Product Lines', would resemble. Now, let's dissect it step by step.
Name

The SQL code is the following:

Select
   '$productLines',
   products.productCode as "Product Code",
   products.buyPrice as "Price",
   products.quantityInStock as "Quantity in Stock"
From
   products
   Inner Join productlines ON
   products.productLine = productlines.productLine

The code is quite direct and easy to follow, with the exception of the form variable '$productLines'. Let's take a closer look at this particular aspect for a more detailed understanding.

Name

In this step, we assign the name 'Product Line' to the variable for clarity and user-friendliness. Its type is set as 'Select', creating a dropdown menu that enables users to choose their preferred product line. At the form's bottom, we link the "List from query" to another query named “Select - Product Lines”. With this setup complete, let's now examine the "Select - Product Lines" query in more detail.

Name

The code:

Select
   productLine as name,
   productLine as value
From
   products
Group By
   productLine
Order By
   productLine Asc

This creates a straightforward list featuring the product lines as selectable values, allowing users to pick from this compilation when utilizing the primary query. With all the queries now established, let’s explore the list:

Name

Let’s say that we choose Planes this time, here are our results:

Name