Filter lookup list

kristiela

Hello, friends!

I would like to ask for your assistance.

To explain the situation I'm in, I'll try to give an example using a page from the Base Application.

Let's think about a Sales Order. We have two tables: Sales Header and Sales Lines.

I want to ensure that the lookup list, meaning the list that shows up when selecting an item, only displays item codes that have not already been added to the order.



In the context of an order, this functionality might not be very meaningful, but in a situation where these lines are somehow unique to the record and shouldn't be duplicated, I think it would be applicable.

How do you approach such a situation? I assume that even after implementing something like this, we should also check whether the user might manually enter an item number that has already been added to the order. Any guidance on this would be helpful. Thank you!

markborges

There are many ways of possibly implementing this, and I believe the cleanest one would be to create a secondary "No." field to hold all the logic needed to display what you want. This would be the "elegant" solution.

For a quick dirty cheap solution (NOT recommended for a production environment), I was able to do the following:
I extended the Sales Order Subform page to add a custom trigger for event OnAfterGetCurrRecord. On this trigger, I set all items as Sales Blocked = false. Then I ran over all the other Item lines on the same Sales Order and set those Items to Sales Blocked = true.

With this, when I enter the No. field for a blank line, the other items that are already on the Sales Order were marked as Sales Blocked and they do not display on the popup window:



Again, I would not recommend you actually develop this. I just posted this as a source to spark additional ideas.

The biggest challenge with this request, is that the No. field already has a Table Relation set when Line is of type Item, and you wouldn't be able to extend it further (there are more details for this here). The second challenge is that the little popup relies on the Table Relation filters to display data -- it does not take complex filtering done using code. If you don't mind opening up the full look up list, and have that list be filtered accordingly, the solution would be done faster and better... But if your intent is to really narrow down the popup lookup list, that is the biggest limitation here.

kristiela

markborges wrote: »

There are many ways of possibly implementing this, and I believe the cleanest one would be to create a secondary "No." field to hold all the logic needed to display what you want. This would be the "elegant" solution.

For a quick dirty cheap solution (NOT recommended for a production environment), I was able to do the following:
I extended the Sales Order Subform page to add a custom trigger for event OnAfterGetCurrRecord. On this trigger, I set all items as Sales Blocked = false. Then I ran over all the other Item lines on the same Sales Order and set those Items to Sales Blocked = true.

With this, when I enter the No. field for a blank line, the other items that are already on the Sales Order were marked as Sales Blocked and they do not display on the popup window:



Again, I would not recommend you actually develop this. I just posted this as a source to spark additional ideas.

The biggest challenge with this request, is that the No. field already has a Table Relation set when Line is of type Item, and you wouldn't be able to extend it further (there are more details for this here). The second challenge is that the little popup relies on the Table Relation filters to display data -- it does not take complex filtering done using code. If you don't mind opening up the full look up list, and have that list be filtered accordingly, the solution would be done faster and better... But if your intent is to really narrow down the popup lookup list, that is the biggest limitation here.

Thank you very much for your response, Mr. Borges!

I didn't quite understand whether it's a good idea to create a field, similar to Sales Blocked, in the table with the objects I want to filter in some way for the little popup. And in a similar way to how Microsoft has done it, set `TableRelation = X where Blocked = false` and use `OnAfterGetCurrRecord()`.

My concern is that you might have meant this is a bad idea in the context of Sales Order and Item, where Sales Blocked might be used with a different semantics in the platform, but it may not be a bad idea in another situation.

The problem with this approach is that for each record, we would go through each Item and set the Blocked field to false. Then, we would need to go through the Lines, filter them, and for the filtered ones, set Blocked to true. So, is the problem with the efficiency of the solution, or am I not understanding it correctly?

Is it possible to achieve the desired result (I understand the popup won't be little then) with the `OnLookup` trigger for the respective "No." field? I can't think of a way to filter all the items so that only those that are NOT found in the particular order's lines remain in the Item record. Could you please help with this logic?

Thank you!

markborges

kristiela, you understood it all correctly, yes!

I would not recommend using the existing Sales Blocked because it already has some functionality in the system, but you understood I was suggesting that maybe you could create another field with similar purpose.

But you also understood correctly that it would be a costly processing if, for every line, you would need to "reset" the new "blocked" field to run the logic again to block items that have already been added to the sales line. I think it would not only be costly, but you would also run into issues if multiple users ran the functionality at the same time.

Then you asked:

Is it possible to achieve the desired result (I understand the popup won't be little then) with the `OnLookup` trigger for the respective "No." field? I can't think of a way to filter all the items so that only those that are NOT found in the particular order's lines remain in the Item record. Could you please help with this logic?

This is definitely possible, and it would be a much cleaner solution, in my mind. The only drawback, is that when you code an OnLookup trigger to a field, the little popup will be deactivated.

Here's an example of the No. field WITHOUT an OnLookup trigger:



Here's an example of the No. field WITH an OnLookup trigger:



Assuming that you understand this limitation, let's go to the logic.

On the OnLookup action, you would want to have a variable holding the Item No. Filter (a Text variable). Then you would want a Sales Line variable as well.

You would filter the Sales Line record to the current Sales Header, only for lines of type Item:

SalesLine.SetRange("Document Type", Rec."Document Type");
SalesLine.SetRange("Document No.", Rec."Document No.");
SalesLine.SetRange(Type, SalesLine.Type::Item);

You also would want to filter out the current line, because for the current line, you would want to see the item on the list:

SalesLine.SetFilter("Line No.", '<>%1', Rec."Line No.");

If any Sales Line are found, then you would want to build the Filter text variable based on all the different Item Nos. found on the other lines:

if SalesLine.FindSet() then repeat
  Filter := Filter + '<>' + SalesLine."No." + '&';
until SalesLine.Next() = 0;

This is basically building a Filter in the format:

<>Item1&<>Item2&<>Item3...

Then we want to make sure to eliminate any trailing '&' from the piece of code above

if StrLen(Filter) > 1 then
  Filter := CopyStr(Filter, 1, StrLen(Filter) - 1);

Lastly, we apply the filter we created to an Item record, we open a Lookup page on the Item List with the filtered recordset, and if the lookup is okay, we return this back to the No. field

Item.SetFilter("No.", Filter);
if Page.RunModal(0, Item) = Action::LookupOK then begin
  Text := Item."No.";
  exit(true);
end else
  exit(false);

With this, you should be able to achieve what you are looking for. I tested this on the following Sales Order:



And see how items 1906-S, 1900-S and 1936-S are not showing on my lookup on the No. field:



I hope this gives you some guidance.

kristiela

markborges wrote: »

kristiela, you understood it all correctly, yes!

I would not recommend using the existing Sales Blocked because it already has some functionality in the system, but you understood I was suggesting that maybe you could create another field with similar purpose.

But you also understood correctly that it would be a costly processing if, for every line, you would need to "reset" the new "blocked" field to run the logic again to block items that have already been added to the sales line. I think it would not only be costly, but you would also run into issues if multiple users ran the functionality at the same time.

Then you asked:

Is it possible to achieve the desired result (I understand the popup won't be little then) with the `OnLookup` trigger for the respective "No." field? I can't think of a way to filter all the items so that only those that are NOT found in the particular order's lines remain in the Item record. Could you please help with this logic?

This is definitely possible, and it would be a much cleaner solution, in my mind. The only drawback, is that when you code an OnLookup trigger to a field, the little popup will be deactivated.

Here's an example of the No. field WITHOUT an OnLookup trigger:



Here's an example of the No. field WITH an OnLookup trigger:



Assuming that you understand this limitation, let's go to the logic.

On the OnLookup action, you would want to have a variable holding the Item No. Filter (a Text variable). Then you would want a Sales Line variable as well.

You would filter the Sales Line record to the current Sales Header, only for lines of type Item:

SalesLine.SetRange("Document Type", Rec."Document Type");
SalesLine.SetRange("Document No.", Rec."Document No.");
SalesLine.SetRange(Type, SalesLine.Type::Item);

You also would want to filter out the current line, because for the current line, you would want to see the item on the list:

SalesLine.SetFilter("Line No.", '<>%1', Rec."Line No.");

If any Sales Line are found, then you would want to build the Filter text variable based on all the different Item Nos. found on the other lines:

if SalesLine.FindSet() then repeat
  Filter := Filter + '<>' + SalesLine."No." + '&';
until SalesLine.Next() = 0;

This is basically building a Filter in the format:

<>Item1&<>Item2&<>Item3...

Then we want to make sure to eliminate any trailing '&' from the piece of code above

if StrLen(Filter) > 1 then
  Filter := CopyStr(Filter, 1, StrLen(Filter) - 1);

Lastly, we apply the filter we created to an Item record, we open a Lookup page on the Item List with the filtered recordset, and if the lookup is okay, we return this back to the No. field

Item.SetFilter("No.", Filter);
if Page.RunModal(0, Item) = Action::LookupOK then begin
  Text := Item."No.";
  exit(true);
end else
  exit(false);

With this, you should be able to achieve what you are looking for. I tested this on the following Sales Order:



And see how items 1906-S, 1900-S and 1936-S are not showing on my lookup on the No. field:



I hope this gives you some guidance.

Thank you very much for the detailed response, Mr Borges.

While trying to adapt the solution you showed me to my case, I encountered quite a few issues. I'm not sure how exactly Microsoft implemented it, but in my case, the execution of the `OnInsert` trigger on the `Lines` table is crucial for the filtering.

Typically, when we have a `Lines` table, its key is composite and consists of a reference to the `Header` and `Line No`, correct? However, in the `OnInsert` trigger, I assign a value to another field `X`, which I want to use for filtering the lookup list. The reference to the `Header` is automatically populated by the system using the `SubPageLink` property (as is standard).

But with the debugger, I saw that `OnInsert` is executed after `OnLookup`. Actually, there might not be such a strict rule. The rule is that when we set `Delayed Insert = true`, the `OnInsert` trigger will be executed when at least one field of the respective `Lines` record is filled in. If `Delayed Insert` is not set, since we usually don't show the key of the `Lines` table in the subform page, whichever field we mark will trigger the `OnInsert`. And in my case, the field on which I write the `OnLookup` trigger is the first one I display in the subform. Taking this into consideration, it occurs to me that I could simply remove the `Delayed Insert` property. However, isn't this considered bad practice, or is it a standard approach in situations like this?

Please correct me if I'm wrong somewhere.

In general, I'm trying to achieve the following: I want the lookup to display those `Y` records whose keys are not involved in column `X` of the respective table. You've shown me how to do this with a filter, but I don't know how to handle the issue I described.

Thank you very much!

PS I managed to work around the fact that the `OnInsert` trigger hadn't executed and the corresponding field wasn't populated by retrieving it from the Header. In fact, I was getting it from there in the `OnInsert` trigger itself. It seems to me that the problem was as follows: the `OnInsert` trigger has not yet executed when we enter `OnLookup`. With the debugger, I noticed that the only thing filled in `Rec` is the reference to the header - this was done automatically by the system due to the `SubPageLink` property. I wanted to select only those diseases that are not associated with the patient with code `Rec.PatientCode`. But this code is not filled in! But still, isn’t it possible that in some situations we won’t be able to bypass the problem in this way (by taking the code from the header) and will actually need the `OnInsert` trigger to execute? How would you approach it then?

kristiela

May I also ask you about the purpose of the `Text` parameter in the `OnLookup` trigger, as well as the Boolean result that the trigger returns?

Additionally, what does `Page.RunModal(0, Item)` do? How does the platform understand that it should open the Item List page with the given filter?

I'm a bit confused about `if StrLen(Filter) > 1`. Why do we want the length to be at least 2? If we have one or more `SalesLine` records found, the length will be at least 3 (because of `<>` and `&`), if not more, since I’m not sure if an empty no. is allowed. In this sense, wouldn’t it be more logical to have `if StrLen(Filter) > 0`?

markborges

Sorry if you explained this but I'm not getting: What exactly are you trying to do that you're looking into the OnInsert trigger?

If you need to apply any additional filters to the Item table, you can do that in this piece of the code:

Item.SetFilter("No.", Filter);

...here...

if Page.RunModal(0, Item) = Action::LookupOK then begin

---

The Text parameter on the OnLookup is used to populate the text field with the lookup value that the user looked up. I found this to be the most efficient way rather than trying to just assign the returned value to the field. For example, if I'm looking up on the Sales Line No. field, when I assign a value to Text, this value will be put back into the Sales Line No. field. -- If I try to simply assign "Sales Line.No := <lookup value>", the data was not immediately refreshed on screen until I left the record.

Page.RunModal(0.... will run the default LookupPageID page set for the record. For example, on the definition of the Item table we have:

table 27 Item
{
    Caption = 'Item';
    ...
    LookupPageID = "Item Lookup";
    ...
    DataClassification = CustomerContent; 

Therefore, Page.RunModal(0,...) with an Item record, will open the "Item Lookup" page. If you would like to use a different Page No., you can specify it instead of the 0 (zero) value.

About the StrLen(Filter) > 1, 1 is the length of the trailing ampersand (&) at the end. You are right that the minimum length will be at least 3, so, you can definitely write it as StrLen(Filter) > 3.... This is just a programming preference, but ultimately, I'm just making sure to remove any trailing ampersands, since I do add them in each iteration of my loop.

kriki

[Topic moved from 'General Chat' forum to 'NAV Three Tier' forum]