How to filter a dropdown box removing items already in use

In this example we will use the typical master-details relationship between Orders and OrderDetails. On OrderDetails Add page we have a dropdown box with a list of products. We want to hide products that are already a part of this order.

1. In WHERE clause of ProductID dropdown box we can use the following code:

productid NOT IN ( SELECT productid FROMorder detailsWHERE OrderID = :session.orderid)

Note, that we only looking for products that belong to the current order and filter details by session variable named orderid.

2. In order to make it work we also need to populate this session variable. This can be done in BeforeProcess event of the OrderDetails table.

This is it. Now you dropdown box will only show items that are not in use yet.