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

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How to create custom l...
Finance | Project Operations, Human Resources, ...
Suggested Answer

How to create custom lookup from multiple tables

(0) ShareShare
ReportReport
Posted on by ax.tech 86

Hi Experts,

I have to create a lookup in a form in which to fields are coming from purchtable and two fields are coming from banklc table. Kindly PFA code and please help hpw to do this.

/// 
/// 
/// 
/// 
/// 
[FormControlEventHandler(formControlStr(LedgerJournalTransVendInvoice, LedgerJournalTrans_LCDocumentNumber), FormControlEventType::Lookup)]
public static void LedgerJournalTrans_LCDocumentNumber_OnLookup(FormControl sender, FormControlEventArgs e)
{      
    Query                   query = new Query();
    QueryBuildDataSource    qbds;
    QueryBuildDataSource    qbdsJoin;
    SysTableLookup          sysTableLookup = sysTableLookup::newParameters( tableNum(PurchTable), this);
    ;
 
    qbds= query.addDataSource( tableNum(PurchTable));
    qbdsJoin= qbds.addDataSource( tableNum(BankLC));
    qbdsJoin.relations( false);
    qbdsJoin.fields().dynamic(NoYes::Yes);
    qbdsJoin.addLink( fieldNum(PurchTable, PurchId), fieldNum(BankLC, PurchTable));
    qbdsJoin.joinMode(JoinMode::OuterJoin);
 
    sysTableLookup.parmQuery(query);
    sysTableLookup.addLookupfield( fieldNum(PurchTable, PurchId), true);
    sysTableLookup.addLookupfield( fieldNum(PurchTable, BankDocumentType), true);
    sysTableLookup.addLookupfield( fieldNum(BankLC, BankLCNumber));
    sysTableLookup.addLookupfield( fieldNum(BankLC, BankAccountId));
    QueryBuildDataSource = query.addDataSource(tableNum(PurchTable));
    QueryBuildDataSource = query.addDataSource(tableNum(BankLC));
    sysTableLookup.performFormLookup();
}

Regards

I have the same question (1)
  • Suggested answer
    GirishS Profile Picture
    GirishS 27,833 Moderator on at

    Hi ax.tech,

    To make it simple you can create a view - View contains PurchTable and BankLC as a DataSource with relations.

    So that you can use that view in the lookup query instead of joining PurchTable and BankLC.

    Thanks,

    Girish S.

  • ax.tech Profile Picture
    ax.tech 86 on at

    Hi Girish,

    There is no relation between these two tables and in the view which table will be parent table? can you please clear this?

    Regards

  • GirishS Profile Picture
    GirishS 27,833 Moderator on at

    But you have mentioned the relation between the two tables in the query.

    qbdsJoin.addLink( fieldNum(PurchTable, PurchId), fieldNum(BankLC, PurchTable));

    Parent table will be PurchTable - Inside the DataSource of the PurchTable, add BankLC table with relations.

    Thanks,

    Girish S.

  • ax.tech Profile Picture
    ax.tech 86 on at

    Yes I was wrong there as there is no relation between these two tables. How can I do this now ?

    Regards

  • Suggested answer
    GirishS Profile Picture
    GirishS 27,833 Moderator on at

    BankLC and BankLCImportLine tables are inherited table. BankLCImportLine  table has relation with PurchTable.

    So, you can add PurchTable and BankLCImportLine in the view.

    Since BankLC and BankLCImportLine are inherited you can access BankLC fields in the BankLCImportLine table itself.

    Thanks,

    Girish S.

  • ax.tech Profile Picture
    ax.tech 86 on at

    I have created the query now what to do please tell. I have used purch table as parent and banklcimport as child and relation between them is purchtable.purchid == banklcimport.purchtable.

    regards

  • GirishS Profile Picture
    GirishS 27,833 Moderator on at

    Add that query to view - Now in the view - Add the fields you want from the view DataSource. Refer to the below Microsoft documentation.

    https://learn.microsoft.com/en-us/dynamicsax-2012/developer/how-to-create-a-view-based-on-a-query

    Thanks,

    Girish S.

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 509 Super User 2026 Season 1

#2
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 375

#3
Adis Profile Picture

Adis 268 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans