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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Supply chain | Supply Chain Management, Commerce / Combine 2 query result...
Supply chain | Supply Chain Management, Commerce
Answered

Combine 2 query result into 1 lookup

(0) ShareShare
ReportReport
Posted on by NguyenDoHuy 18

Hi all,

I have a task to create a lookup using data from 2 different tables with 2 different ranges,

For example,

Table A: filter with range A1. Result R1 R2 R3

Table B: filter with range B1. Result R1 R4 R5

Needed result R1 R2 R3 R4 R5

I need to combine those 2 set of results into 1 lookup. 

Is there anyway to approach that requirement ?

Many thanks

I have the same question (0)
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 236,907 Most Valuable Professional on at
    RE: Combine 2 query result into 1 lookup

    If you wanted to put together results of those two queries, you could use an union query. But that's not what you want, according to your example, because there either A.R1 or B.R1 was excluded.

    Maybe you could change one of the queries not to return values already covered the other query. Or you can put the data to a temporary table (and deal with duplicates on insert). Or maybe you could have a view returning a result of the union query and group by the value.

  • Suggested answer
    GirishS Profile Picture
    GirishS 27,825 Moderator on at
    RE: Combine 2 query result into 1 lookup

    Hi,

    You can create a new query add both DataSource with relations and then add range.

    Query query = new Query();
SysTableLookup table = SysTableLookup::new parameter(Tablenum(TableName1),this);
QueryBuilddDatasource qbds1,qbds;

qbds1 = query.addDatasource(tablenum(TableName1));
qbds2 = qbds1.addDataSource(tablenum(TableName2));
qbds2.relations(true);
//if there is no relation between the tables use addLink method and mention the field that relate two tabvles.
qbds1.addRange(fieldnum(TableName1,Field1)).value(queryvalue("1"));
qbds2.addRange(fieldnum(TableName2,Field2)).value(queryvalue("2"));
table.addLookUpField(fieldnum(TableName1,Field1));
table.parmQuery(query);
table.performFormLookup();

    Thanks,

    Girish S.

  • NguyenDoHuy Profile Picture
    NguyenDoHuy 18 on at
    RE: Combine 2 query result into 1 lookup

    Hi Martin,

    Thanks for the respond,

    I have tried Union query but it not works for me because my 2 table are 2 different designed table ( 1 is a custom view, 1 is InventLocation table).

  • NguyenDoHuy Profile Picture
    NguyenDoHuy 18 on at
    RE: Combine 2 query result into 1 lookup

    Hi Girish, I have tried this one too, but doesnt work for me

  • NguyenDoHuy Profile Picture
    NguyenDoHuy 18 on at
    RE: Combine 2 query result into 1 lookup

    Many thanks Martin, I archived my task by using temp table as your suggestion

  • Martin Dráb Profile Picture
    Martin Dráb 236,907 Most Valuable Professional on at
    RE: Combine 2 query result into 1 lookup

    Good to hear that you resolved the problem. But you're wrong in thinking that union queries can't be used for different tables. For example, How to: Combine Data Sources in a Union Query shows how to create a query returning data from CustTable and VendTable.

  • NguyenDoHuy Profile Picture
    NguyenDoHuy 18 on at
    RE: Combine 2 query result into 1 lookup

    Thanks Martin, I mean my 2 datasources have different structures, so when I use those 2 in a union view. It returns me Error field mismatch

  • Martin Dráb Profile Picture
    Martin Dráb 236,907 Most Valuable Professional on at
    RE: Combine 2 query result into 1 lookup

    Tables can have different fields - CustTable and VendTable have different fields too.

    But if you want to combine data from two tables, you must select some compatible fields, otherwise you can't combine them. When you designed your temporary table, you also didn't use two different schemas. You've designed a single table with a few fields and populated it with data from two tables with different schemas.

  • NguyenDoHuy Profile Picture
    NguyenDoHuy 18 on at
    RE: Combine 2 query result into 1 lookup

    thanks Martin, one more reason I cant use Union query for this case is because I need to use a dynamic range for a query (current worker)

  • GirishS Profile Picture
    GirishS 27,825 Moderator on at
    RE: Combine 2 query result into 1 lookup

    Hi,

    For current worker you can use (HcmWorkerLookup::currentWorker()) in the query range in the AOT 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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Pallavi Phade – Community Spotlight

We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…

Congratulations to the October Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Supply chain | Supply Chain Management, Commerce

#1
CA Neeraj Kumar Profile Picture

CA Neeraj Kumar 249

#2
Siv Sagar Profile Picture

Siv Sagar 202 Super User 2025 Season 2

#3
Danny Bilodeau Profile Picture

Danny Bilodeau 184 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans