Filtering a LookUp

wesleys

I am trying to modify a lookup to automatically filter the records on the lookup form. A fair example of what I'm trying, would be to take the typical sales order form (subform for the sale lines technically) and change the lookup on the "No." field. Upon lookup the user would still go to the "Item List" form, but all "blocked" items would be filtered out.

I have tried without success to simply use the "onlookup" function for the text box in the form code to override the default lookup functionality.

No. - OnLookup(VAR Text : Text[1024];) : Boolean
"RecordSource".SETCURRENTKEY("key");
"RecordSource".SETFILTER(Field, "filter");

IF FORM.RUNMODAL(0, "RecordSource") = ACTION::LookupOK THEN BEGIN
  "No." := "RecordSource."No.";
  EXIT(TRUE);
END ELSE
  EXIT(FALSE);

The first problem with this is that unless I comment out the "EXIT" statements, the value selected in the modal form does not populate to the original form field. However, without the "EXIT" commands, the system thinks the "onlookup" failed, and does not "validate" the field, even though the value gets populated to the the original form field.

The second problem is that with a form such as a sales or purchase order the lookup form is based on the line's "type" value, be it G/L, Item, charge(Item), etc. So to go this route I would have to create a case statement and handle each possible "type".

What would be best is if there is a way from the modal form to add some code when it is run to determine if it is modal (there is a property that does tell this), and determine from what form the current form was called. I wouldn't want to filter say the "Item List" everytime, just when it was opened modal from the "sales subform".

Anything to point me in the right direction would be helpful.

Richard_Larsen

Hi, I don't know if I understand you right but this exsampel og code seams to work. I do think it is difficult to write code in the "Item List" that senses witch form the call came from. One possibility is to "activate" av function within the "Item list form" that set the filter, but I do thing you have to enter the code specified as well.

//1.start
IF Type = Type::Item THEN BEGIN
  IF tblItem.GET("No.") THEN ;
  tblItem.SETRANGE(Blocked , FALSE);
  frmItemList.SETTABLEVIEW(tblItem);
  frmItemList.SETRECORD(tblItem);
  frmItemList.LOOKUPMODE(TRUE);
  IF frmItemList.RUNMODAL = ACTION::LookupOK THEN BEGIN
    frmItemList.GETRECORD(tblItem);
    VALIDATE("No." , tblItem."No.");
  END;
END;
//1.end

You have to write a case statement to loockup GL, Resources, etc.

Regars
Richard

wesleys

Thank you Richard, it is working well now.

I think my hangup was avoiding the VALIDATE function which another post had mentioned shouldn't be used in a lookUP function, but it seems to be doing OK to me.

With the funtion:
"No. - OnLookup(VAR Text : Text[1024];) : Boolean"
I thought I had to return a value and EXIT was the only thing I had found so far, but this seems to not be the case either.

Thanks again for your help.

Dean_Axon

Hi Wesleys,

think my hangup was avoiding the VALIDATE function which another post had mentioned shouldn't be used in a lookUP function, but it seems to be doing OK to me.

When using the VALIDATE function you should always consider what code is behind the field you are populating and whether or not you want to run this code.

i.e. if you are returning the "Description" on the sales line table, then you would want to use the VALIDATE command.

If alll you want to do is to fill the field and NOT run the code behind the field then you can use the same lines of code as described by Richard, but replace the

VALIDATE("No." , tblItem."No.");

with

"No.":=tblItem."No.";

Hope that helps

Dean

YUH

wesleys wrote:

...
I have tried without success to simply use the "onlookup" function for the text box in the form code to override the default lookup functionality.

No. - OnLookup(VAR Text : Text[1024];) : Boolean
"RecordSource".SETCURRENTKEY("key");
"RecordSource".SETFILTER(Field, "filter");

IF FORM.RUNMODAL(0, "RecordSource") = ACTION::LookupOK THEN BEGIN
  "No." := "RecordSource."No.";
  EXIT(TRUE);
END ELSE
  EXIT(FALSE);

...

Hi wesleys,
I think there is a problem in your sample. The line

"No." := "RecordSource."No.";

should read instead

Text := "RecordSource."No.";

Wouldn't that help?

wesleys

It's only polite to go back to a thread and post the solution, for others who may go searching for overloading lookup functions later.

I think there is a problem in your sample. The line
Code:
"No." := "RecordSource."No.";

should read instead
Code:
Text := "RecordSource."No.";

Wouldn't that help?

And therin squats the toad. A combination of my original, plus Richards, plus this little tidbit has done it. The behavior is now exactly like the default lookup as far as I can tell. This is what I've done to the Purchase order lines form OnLookup event for the "No." field. A CASE statement will be needed as well to handle "G/L Account", "Fixed Asset", and the other Types.

No. - OnLookup(VAR Text : Text[1024];) : Boolean

IF tblItem.GET("No.") THEN ;
tblItem.SETFILTER(CloseOut, 'No');  //closeout is a custom field

IF FORM.RUNMODAL(0, tblItem) = ACTION::LookupOK THEN BEGIN
  Text := tblItem."No.";
  EXIT(TRUE);
END ELSE
  EXIT(FALSE);

Thanks everyone for you input.