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)
• name

the view purchase_order_product looks something like this:

select po.purchase_order_id
 , p.product_id
 , p.name
from purchase_order po
cross apply product p
where not exists 
(
 select 1 
 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.

Hi René, 

How about when I can add in that same purchase_order 2 sales_manager_id's.

When I've chosen the first one in a dropdown, the second one should not be chosen in the first drop down.

Is that also possible, or should I stick with the warning / constraint in that case?

Kind regards,

Peter

Hi,

As I read your message I think it's more of the same: Replace ‘product’ with ‘sales_manager’.

Question is (again): What does the user expect when he opens the drop-down? Always the full list, or a list which contains some sales people or products less. What is the actual problem of having the full list shown?

I don't know your specific situation, but in our case we just show everything. And if it's conflicting, we show a message (tsf_send_message, triggered in the ‘default’) why it's conflicting. The user can solve it, without asking questions why he doesn't see stuff in the list.

Please note there is a little downside to this ‘view look-up’: If you press CTRL + L (or the magnify glass) you get the look-up of the view, not of the actual table containing the product or sales_manager.

Hi René,

Sorry, wasn't totally clear. I would like to have it filtered before saving. Then I think the view won't work (as it does not exist in purchase_order_line yet).

I know the Message is an option (it is acually what I've done so far), but I prefer to have it filtered ;-)

Kind regards,

Peter

When I add a lookup over this view, I see that the display name becomes empty.

So at the moment of selecting a product, I see only those products that are not part of my purchase_order_line, but at the same time I don't see the names of these products in my purchase_order_line (as they are filtered out of this view).

How can I use the view for lookup, but still refer to the product-table for the display name?

I think you could use a prefilter instead. Instead of the ‘where not exists’ you could add a column “used” to your view, which has a 0/1 value wether it should be displayed or not in the look-up.

Thanks René,

This is a worthy addition to the best answer.

Also discovered in this way that you can add a prefilter only for lookup use. In this way you can add a lookup prefilter on the used column.