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.
Solved
Limit results in a look-up
- Vanguard
- 20 replies
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)
- 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.
This topic has been closed for replies.
- Superhero
- 196 replies
- Answer
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.
- Captain
- 47 replies
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
- Superhero
- 196 replies
Peter Kruis wrote:
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.
- Captain
- 47 replies
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
- Captain
- 96 replies
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?
- Superhero
- 196 replies
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.
- Captain
- 96 replies
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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.