Solved

Limit results in a look-up

  • 26 February 2020
  • 7 replies
  • 151 views

Userlevel 1
Badge +4

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.

icon

Best answer by René W 26 February 2020, 16:36

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.

View original

7 replies

Userlevel 4
Badge +15

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.

Userlevel 1
Badge +2

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

Userlevel 4
Badge +15

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.

Userlevel 1
Badge +2

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

Userlevel 3
Badge +5

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?

Userlevel 4
Badge +15

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.

Userlevel 3
Badge +5

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.

Reply