Using temporary table to create lookup for a field

jorgito

Hi all.

I have the following problem.

I have a field "Vendor No." in a table that is related to the Vendor table, but in the OnLookup trigger I want to restrict the displayed values.

So, I create a temporary table VendorsTemp (T23) and a form VendorList (F27).

I use the following code in the OnLookup trigger of the "Vendor No." field to fill the temporary table VendorsTemp.

PurchRcptLineAssgmnt.SETRANGE("Document No.", "Document No.");
PurchRcptLineAssgmnt.SETRANGE("Document Line No.", "Document Line No.");
IF PurchRcptLineAssgmnt.FIND('-') THEN
  REPEAT
    IF Vendors.GET(PurchRcptLineAssgmnt."Vendor No.") THEN
    BEGIN
      VendorsTemp.INIT;
      VendorsTemp := Vendors;
      VendorsTemp.INSERT;
    END;
  UNTIL PurchRcptLineAssgmnt.NEXT = 0;

The VendorsTemp table gets filled OK.

How do I display the VendorList form as Lookup and when the user clicks on a Vendor, the field "Vendor No." is filled in appropriately?

I tried some approaches, but I can't get it to work right.

Any suggestions?

Jorgito

DenSter

The easiest way to restric the vendor list is to set that restriction in the table relation property of the field itself. That way you don't have to program anything.

kine

In this case, you need the "How to do form based on temporary table".

http://www.mibuso.com/forum/viewtopic.php?t=9478
http://www.mibuso.com/forum/viewtopic.php?t=5180

etc.

XML

If you need to more complex conditions than a TABLE RELATION property can offer, you display a form like thish:

VendorList.lookupmode := true;
VendorList.setrecord(VendorsTemp);
if Vendorlist.RUNMODAL = ACTION::LookupOK then
  Vendorlist.GETRECORD(VendorsTemp);

Afterwards VendorsTemp contain the selected records.

jorgito

It is far too complicated to use the TableRelation propery.

I used the following code

VendorList.LOOKUPMODE := TRUE;
VendorList.SETRECORD(VendorsTemp);
IF VendorList.RUNMODAL = ACTION::LookupOK THEN
BEGIN
  VendorList.GETRECORD(VendorsTemp);
  "No." := VendorsTemp."No.";
END;

But I have 2 problems:
1. All the records from the Vendor table are displayed and not the ones in the VendorsTemp record.
2. When I double click on a vendor in the form, the original field does not change.

What am I doing wrong?

Jorgito

kine

SETRECORD for temporary table is not enough. Please, read the forum about the temporary tables and you can read for example "How to create report based on temporary table" - it is very similar.

jorgito

I have tried most of the solutions that are presented in this forum, but I couldn't find one for my problem.

I tried using the following code

IF FORM.RUNMODAL(0,VendorsTemp) = ACTION::LookupOK THEN
BEGIN
  Rec."No." := VendorsTemp."No.";
  //MESSAGE(VendorsTemp."No.");
END;

Now, the correct records are displayed in the lookup window, but when I click on OK, the value is not transferred in the original table.
If I use the MESSAGE function to find out the value that is returned, it is the correct one. But the original field is not updated with the value I clicked on in the lookup form.

Thank a lot
Jorgito

frankmortensen

Instead of:
Rec."No." := VendorsTemp."No.";
You should use:
Rec.GET(VendorsTemp."No.");
...

/Frank

jorgito

Finally, I found the solution to my problem.

I found in another topic that when you set the value of the field after the user has clicked on OK, you must use

IF FORM.RUNMODAL(0,VendorsTemp) = ACTION::LookupOK THEN
BEGIN
  VALIDATE("No.", VendorsTemp."No.");
END;

instead of

IF FORM.RUNMODAL(0,VendorsTemp) = ACTION::LookupOK THEN
BEGIN
  "No." := VendorsTemp."No.";
END;

And if you are viewing the table from the Object Designer, the value is not transferred from the lookup table to the field.
Instead, if you run the FORM and try to use the lookup table, the field gets updated normally.

http://www.mibuso.com/forum/viewtopic.php?t=8143

Thanx everyone for your help
Jorgito

colingbradley

I had what looked like a similar problem, I needed to get a list of Vendors based on The Purchase Header.Order Type (Option) and the Vendor.Status

The Table Relation works just fine:

IF (Order Type=CONST(" ")) Vendor ELSE IF (Order Type=CONST(Controlled)) Vendor WHERE (Status=FILTER(<>'')) ELSE IF (Order Type=CONST(Ancillary)) Vendor

This works also with using a FILTERGROUP in the Vendors List form:

UserSetup.GET(USERID);
FILTERGROUP(10);
IF UserSetup."Block Old Vendors" THEN
SETFILTER("No.", '<%1','X');
FILTERGROUP(0);

Bye the way, I wanted to use a filter like '<>%1', 'X*'
But that will not work, anyone know why?