Small and medium business | Business Central, N...

What is best practice for displaying a non-primary key of a lookup? OnLookup or CalcField 2 seperate fields?

(0) Share

Report

Posted on by Community Member

I am struggling to understand the best methodology for handling fields that lookup related tables. For example, let's say I have a table of Contacts and a table of Opportunities. On the Opportunity record, I have a field that is a lookup to a related Contact record. The contact has both a Contact No. value and a Contact Name value, but only the Contact No. value is a unique primary key - however I don't want to display the Contact No. field on the Opportunity record, I only care about the Name.

My options are:

1. Create 2 separate fields, with the Name field being read-only CalcField and updated every time the Contact No. field is changed.

field(50128; "Contact"; Code[20])
    {
        Caption = 'Contact';
        DataClassification = ToBeClassified;
        TableRelation = Contact.No;
    }
field(50130; ContactName; Text[80])
    {
        Caption = 'Contact Name';
        FieldClass = FlowField;
        CalcFormula = lookup(Contact."Name" where("No." = field("Contact")));
    }

2. Create an OnLookup trigger to reset the Contact value to Contact.Name

field(50100; Contact; Text[100])
        {
            DataClassification = CustomerContent;
            Caption = 'Contact';

            trigger OnLookup()
            var
                Contact: Record Contact;
            begin
                Contact.Reset();
                if Page.RunModal(Page::Contact, Contact) = Action::LookupOK then
                    Contact := Contact.Name;
            end;
        }

While creating 2 fields seems clunky and unnecessary, if I only use a single field and the OnLookup trigger I am unable to properly reference this field later, as I only have access to the non-unique / non-primary key value of name, which makes doing a lookup of Contact by name inaccurate.

All responses (8)

Answers (0)

Suggested answer

YUN ZHU 82,673 Super User 2025 Season 1 on at

Like (0)

Report

RE: What is best practice for displaying a non-primary key of a lookup? OnLookup or CalcField 2 seperate fields?

Hi, here are some ideas that hopefully will give you some hints as well.

https://yzhums.com/22220/

Thanks.

ZHU
matthias_rabus 160 on at

Like (0)

Report

RE: What is best practice for displaying a non-primary key of a lookup? OnLookup or CalcField 2 seperate fields?

Looks nice, one small issue:

You need a begin/end here:

if Page.RunModal(Page::Contacts, Contact) = Action::LookupOK then BEGIN

Contact := Contact.Name;

ContactNo := Contact.No;

END;

Otherwise good solution
Suggested answer

Community Member on at

Like (0)

Report
RE: What is best practice for displaying a non-primary key of a lookup? OnLookup or CalcField 2 seperate fields?

I believe I've solved the issue using a combination of both. The lookup field continues to store the display the name and allows the lookup to be updated, but I've also created a second static field to simply hold the number.

field(50101; Contact; Text[250]) { DataClassification = CustomerContent; Caption = 'Contact'; trigger OnLookup() var Contact: Record Contacts; begin Contact.Reset(); if Page.RunModal(Page::Contacts, Contact) = Action::LookupOK then Contact := Contact.Name; ContactNo := Contact.No; end; }

Now the user only has to maintain a single field, but the primary key / no. value is always up to date and accessible while still being hidden!
Suggested answer

Inge M. Bruvik 1,025 Moderator on at

Like (0)

Report

RE: What is best practice for displaying a non-primary key of a lookup? OnLookup or CalcField 2 seperate fields?

If you open the opportunity page for the contact page then you will most likely already have the contact no. relation established and then the contact no. field will already be initiated with the correct contact no.

For that you use the RunPageLink propety.

docs.microsoft.com/.../devenv-runpagelink-property
Community Member on at

Like (0)

Report

RE: What is best practice for displaying a non-primary key of a lookup? OnLookup or CalcField 2 seperate fields?

But what if I want to update this value? I cannot click on the Contact.Name field - correct? I must update the Contact.No field again and recalculate Contact.Name, unless there is something I am not understanding?
Suggested answer

Inge M. Bruvik 1,025 Moderator on at

Like (0)

Report

RE: What is best practice for displaying a non-primary key of a lookup? OnLookup or CalcField 2 seperate fields?

Well you need to contact no. to do the lookup but if you know it holds a value you do not have to display it.

Then you can just set the visible property for the field to false in the page. Then only the name will be displayed.

field(50128; "Contact"; Code[20])

{

Caption = 'Contact';

Visible = false;

DataClassification = ToBeClassified;

TableRelation = Contact.No;

}
Community Member on at

Like (0)

Report

RE: What is best practice for displaying a non-primary key of a lookup? OnLookup or CalcField 2 seperate fields?

Do you have any tips for only needing to display 1 field (the name field)?

As of right now it's poor UX to make my users identify that the Name field is incorrect, navigate to the No. field, update it, then review the Name field to make sure it was calculated correctly.
Suggested answer

Inge M. Bruvik 1,025 Moderator on at

Like (0)

Report

RE: What is best practice for displaying a non-primary key of a lookup? OnLookup or CalcField 2 seperate fields?

I would say that option 1 is best practice, it is at least how i would do it myself and i think that is the way Business Central generally does it.