Skip to main content

Notifications

Announcements

No record found.

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

Create a table relation to a table with a composite primary key (5 fields)

(0) ShareShare
ReportReport
Posted on by 5

Hello everyone,

i'm struggeling to create a relationship in AL. Maybe someone can help with this or point me in the right direction.

I create a table (A). This table (A) should a have one to one relationship to another table (B). The other table (B) has a composite key based on 5 fields (Fields: V,W,X,Y,Z).

What steps a necessary to create this relationship?

field(1; "V"; Code[10])
        {
            Caption = 'V';
            TableRelation = B.V;
        }

Only one TableRelation on the first field seems not to work and i don't know how to setup the relation with all the other fields.

In the end, i want to select only one field on the page and i want to make sure, that it is the correct relationship.

I hope someone has an idea.

Greetings,

Ben

  • Verified answer
    YUN ZHU Profile Picture
    YUN ZHU 74,115 Super User 2024 Season 2 on at
    RE: Create a table relation to a table with a composite primary key (5 fields)

    Looks ok, great.

  • fluegelb Profile Picture
    fluegelb 5 on at
    RE: Create a table relation to a table with a composite primary key (5 fields)

    Hey Zhu,

    i found a fitting solution with your link under point "2. Using OnLookup (Field) Trigger and Page.RunModal() Method".

    The solution looks like this.

     field(2; V; Code[20])
    {
        Caption = 'V';
        trigger OnLookup()
        var
            "Table B-Record": Record "Table B";
        begin
             "Table B-Record".Reset();
            if Page.RunModal(Page::"Table B List Page", "Table B-Record") = Action::LookupOK then begin
                V := "Table B-Record".V;
                W := "Table B-Record".W;
                X := "Table B-Record".X;
                Y := "Table B-Record".Y;
                Z := "Table B-Record".Z;
            end;
        end;
    }


    Can you confirm that this solution is viable?

    Btw. you can ignore my other post.

    Greetings,

    Ben

  • fluegelb Profile Picture
    fluegelb 5 on at
    RE: Create a table relation to a table with a composite primary key (5 fields)

    Hello Zhu,

    thank you for your response.

    I just looked at it and i'm not sure if this solution fits for my case.
    Unfortunately it is not only about displaying a field accordingly, but to store the key (W,V,X,Y,Z) in table (A), so that I can later find the correct entry in table (B) in a code unit and use the fields of table (B) in my code.

    Maybe i misunderstood your solution, could you please explain how to apply your solution to my case?

    Greetings,

    Ben

  • Verified answer
    YUN ZHU Profile Picture
    YUN ZHU 74,115 Super User 2024 Season 2 on at
    RE: Create a table relation to a table with a composite primary key (5 fields)

    Hi, hope the following helps.

    https://yzhums.com/22220/

    Thanks.

    ZHU

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

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,391 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans