Screen cast: Filtering a lookup menu by customizing the SQL query

Learn how to filter the contents of the drop-down menu for a look-up field. With a slight knowledge of SQL, you can easily change what is displayed in the drop down.

00:07 Using the Northwind application, which is available online at bigprof.com/demo, we will get to know another feature that AppGini offers.

00:18 This tutorial shows you how to filter a lookup drop-down by customizing the SQL query.

00:25 This is our "Products" table. Some of the products are "discontinued". As the user should not be able to order discontinued products, we will need to hide those products from the drop-down list that appears to him while ordering.

00:41 Let us have a look at it and see what it currently presents.

00:45 We will jump to the "order details" table and try to add a new record. This is the "product" field drop down. As we said before, it is getting its data from the "products" table. As you can see, it currently shows all available products, even the discontinued ones.

01:14 With a slight knowledge of SQL, you can easily hide the discontinued products form the list.

01:20 Let us switch to AppGini and see how it is done.

01:25 We will go to the "order details" table, "product ID" field. It is set to bring its data from the "products" table and display the product name. Accordingly, all the records of the "products" table will be displayed in the drop down.

01:43 To hide the discontinued products, we will click on "Advanced" button, and uncheck the "show all records of the parent table", which is the default option. This is the SQL query, which means that this field should display all the products in the drop down.

02:01 To hide the discontinued products from the drop down, we will add a condition "WHERE `products`.`Discontinued` !=1". If you wish to get back to the default query, you can simply press "reset to default query" button. After adding our condition, we will generate, switch to the browser, refresh the page and see how the list shows.

02:53 The discontinued products are not shown in the drop down anymore.

02:58 To get to know more about AppGini, please visit our homepage for more video tours.