How to filter lookup on subpage to show only records of value specified on main page

shyvmir · 2019-01-31

Hi,

I'm new to NAV and to this forum.

I have header and lines tables with card and listpart pages implemented. The header is sales order with associated sales order lines. The application will create orders addressed to vendors (there is a vendor field in the header). The order lines allow user to add items offered by the selected vendor to the order by selecting them using a lookup field.

I have implemented the tables and the lookup field (at present the loopup shows all items from the items table). I just don't know how I can restrict the items displayed in the lookup to the selected vendor in the header. Can someone help out with that?

Would greatly appreciate.

Cheers,
S

SanderDk · 2019-01-31

Hi shyvmir,
How have you added the lookup?

If you have added the table relation property you can like the vendor No. In item table with you vendor No.
If you have code OnLookup tigger you can item.setrange("vendor No.", yourOwnVendorNo);

wolfskin · 2019-02-01

Like SanderDk said. you need to use the setrange command to restrict item no. that you want before opening item list page

But if you want to learn more about using lookup page, this blog may help you.
https://www.archerpoint.com/blog/Posts/dynamics-nav-how-program-lookups

Thanks,

RockWithNAV · 2019-02-01

Can you let us know what exactly you tried?

kriki · 2019-02-01

[Topic moved from 'NAV Tips & Tricks' forum to 'NAV Three Tier' forum]

shyvmir · 2019-02-04

Hi guys,
Thank you for your comments.

I have a Vendor table, I have an Items table with Vendor No. column with table relation to Vendor, I have a sales header table with a Vendor No. column with a table relation to Vendor (select a vendor for whom the order will be prepared - this is a purchase order), and sales line table with Item No. column. Now when a verdor is specified on the sales header page, the lookup on the subpage (sales line) should list items only of that specific vendor.

What I am unable to understand is how I can read the vendor no specified on the header page from the subpage to setrange for the dropdown.

Thank you for your help!

wolfskin · 2019-02-04

Hi shyvmir,

As I understand, you would like to send vendor no. from sales order header to be filter on item list while selecting no. in sales order subfrom (drilldown page is created from No.'s TableRelation), right?

If It's right, you may write you own code to handle lookup page for every sales line type (including, "G/L Account No.","Item", "Resource", "Fixed Asset", etc).

So, This code below will guide you to open lookup the pages for Item and G/L Account, you need to write more for Resource, Fixed Asset, etc.

Table Sales Line (37)
No. - OnLookup()

IF SalesHeader.GET("Document Type","Document No.") THEN BEGIN
  CASE Type OF
    Type::"G/L Account": BEGIN
      GLAccount.RESET;
      GLAccount.SETRANGE("Direct Posting",TRUE);
      GLAccount.SETRANGE("Account Type",GLAccount."Account Type"::Posting);
      GLAccount.SETRANGE(Blocked,FALSE);
      GLAccountList.SETTABLEVIEW(GLAccount);
      GLAccountList.LOOKUPMODE(TRUE);
      IF GLAccountList.RUNMODAL = ACTION::LookupOK THEN BEGIN
        GLAccountList.GETRECORD(GLAccount);
        VALIDATE("No.",GLAccount."No.");
      END;
    END;
    Type::Item: BEGIN
      Item.RESET;
      Item.SETRANGE("Vendor No.",SalesHeader."Vendor No.");
      ItemList.SETTABLEVIEW(Item);
      ItemList.LOOKUPMODE(TRUE);
      IF ItemList.RUNMODAL = ACTION::LookupOK THEN BEGIN
        ItemList.GETRECORD(Item);
        VALIDATE("No.",Item."No.");
      END;
    END;
    Type::Resource:
    // ...
    Type::"Fixed Asset":
    // ...
    Type::"Charge (Item)":
    // ...
  END;
END;

Hope this will help you, thanks.

shyvmir · 2019-02-05

Hi wolfskin,

Thanks. This is very insightful code, and it almost worked! One thing I missed to mention was that I am using a delayed insert into the lines table, which made the validate stumble.

In the meantime I found a workround, by adding an additonal vendor field in the lines table (using it as a variable, and setting its value as part of the prim key) and using FIELD Filter.

I was surprised this worked :-). Your solution would be more elegant, but for the delayed insert.

Thank you!

lubost · 2019-02-05

Don't use MODIFY in validation triggers ... "stumble" validations uses MODIFY which can interfere with DelayedInsert property.

shyvmir · 2019-02-05

Hi lubust,

Thanks for the hint. The error in VALIDATE came before MODIFY. I presume that VALIDATE itself tries to insert a value into field in a record, that doesnot yet exist... in any case this is just a presumption of a newbee :-)

How to filter lookup on subpage to show only records of value specified on main page

Answers

Categories