Is there a way to limit the result in a look-up?
E.g. you've created a purchase order and are now inputting pruchase order lines, but for every new line, you don't what to see the products which are already in the purchase order.
Best answer by René W
You can add a view that is used for the look-up, that hides the products that are already in the purchase order.
E.g. if you have the tables:
- purchase_order (purchase_order_id)
- purchase_order_line (purchase_order_id, purchase_order_line_id, product_id, quantity)
- product (product_id, name)
You can create a ‘look-up’ view such as ‘purchase_order_product’ with the following fields:
- purchase_order_id (key)
- product_id (key)
the view purchase_order_product looks something like this:
from purchase_order po
cross apply product p
where not exists
from purchase_order_line pol
where pol.purchase_order_id = po.purchase_order_id
and pol.product_id = p.product_id
This query cross applies all ‘products’ on the purchase order, excluding those which are already attached to the purchase order. Add this view as the look-up (with the purchase_order_id as reference) and you’re done.
Little sidenote: From a user point of view it might be more confusing they don't see their products. A warning / constraint that a product cannot be added twice can solve the issue as well.