web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Check out the Community Spotlight Video Series
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Business Central forum / Adding field with AL: ...
Business Central forum

Adding field with AL: How to show only relevant rows on lookup page?

(0) ShareShare
ReportReport
Posted on by SSDev1 150
The custom page this field is being added to is using "Sales Invoice Header" for its data table. I want to allow the user to change the ship-to contact of the invoice. I have this field:
field("Test"; "Ship-to Contact")
{
 ApplicationArea = All;
 Lookup = true;
 LookupPageId = "Contact Card";

}
However, it shows contacts of all customers. I want to limit the list to only the invoice's customer's contacts. How can this be done? I've tried various fields with the TableRelation property, but it doesn't seem to change anything.
Categories:
Other
I have the same question (0)
  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,172 Most Valuable Professional on at

    You should set up the TableRelation property for this field in your table where the field is defined. The TableRelation must have the table (contact) and a WHERE clause (the link between your table and the Contact table). Something like WHERE Contact.No = “Ship to Contact”.

  • SSDev1 Profile Picture
    SSDev1 150 on at

    I haven't touched the table itself yet, only the page. Do you mean that I should create a table extension for the invoice table with a table relation to Contact?

    Naively, the invoice itself already does this, I just want to replicate it on my custom page. What the invoice does, though, is pop up a small window where you can select from the customer's contacts. And using DrillDown on the custom page doesn't do this for me either because it just opens a full window of the entire list and you can't actually use it anyway. If there was a way to replicate what the invoice is already doing ,that would be ideal.

  • Suggested answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,172 Most Valuable Professional on at

    Best way in my opinion is handling the field in a tableextension if you need to have a real field. If instead you want only a “variable” that a user can select on a page, declare a global variable at page level, place it on a page field and handle the OnDrillDown event. Here you need to:

    1) filter the Contact table by showing only the Contact for the selected customer (Contact.SETRANGE(...);

    2) Set the view for the Contact List, something like: ContactList.SETTABLEVIEW(Contact);

    3) Open the page in lookupmode: ContactList.LOOKUPMODE(true);

    IF ContactList.RUNMODAL = Action:LookupOk then ...

    Sorry if I don’t post a complete code but I’m answering from mobile :-)

  • SSDev1 Profile Picture
    SSDev1 150 on at

    Is this an OnDrillDown event on the page of the field or do I need to make a new page to show the contact list? If it's on the same page as the field, how can I reference the contact table and contact page that I want its drill down to open? I'm not able to find any examples using OnDrillDown online. Thanks.

  • Verified answer
    I Gusti Made Ari Profile Picture
    I Gusti Made Ari 3,608 on at

    Hi, you can use OnLookup/OnDrillDown trigger on the field . As you only need to show contact for the same customer , you need to get the main contact first from Contact Business Relation , once you get it  filter contact based on same  company no.

    Sample code as below :

    field(Test;"Ship-to Contact")

               {

                   ApplicationArea = All;

                   trigger OnLookup(Text : Text) : Boolean;

                   var

                       ContactBusRel : Record "Contact Business Relation";

                       Contact : Record Contact;

                   begin

                       ContactBusRel.Reset;

                       Contact.Reset;

                       ContactBusRel.SetRange("Link to Table",ContactBusRel."Link to Table"::Customer);

                       ContactBusRel.SetRange("No.",Rec."Sell-to Customer No.");

                       if ContactBusRel.FindFirst then begin

                           Contact.SetRange("Company No.",ContactBusRel."Contact No.");

                           if Page.RunModal(PAGE::"Contact List",Contact) = ACTION::LookupOK then begin

                               Rec."Ship-to Contact" := Contact."No.";

                           end;

                       end;

                   end;

               }

  • SSDev1 Profile Picture
    SSDev1 150 on at

    Using OnLookup on the field worked. Thank you both.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Check out the Community Spotlight Video Series

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard > Business Central

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans