Finance | Project Operations, Human Resources, ...

How to create custom lookup from multiple tables

(0) Share

Report

Posted on by ax.tech

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)

All responses (7)

Answers (0)

Suggested answer

GirishS 27,827 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
ax.tech 86 on at

Like (0)

Report

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

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report
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.

Was this reply helpful? Yes No
ax.tech 86 on at

Like (0)

Report

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

Regards

Was this reply helpful? Yes No
Suggested answer

GirishS 27,827 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
ax.tech 86 on at

Like (0)

Report

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

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No

Join the conversation

Community site session details

How to create custom lookup from multiple tables

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

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

Featured topics

Product updates

Community site session details

How to create custom lookup from multiple tables

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

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

Featured topics

Product updates