Create Query with multiple data source

(0) Share

Report

Posted on by Community Member

Hi all,

just need some advice, if we create new query, and I want to have multiple table (same level). Does these table should be on same structure, I mean the field name for each tables used.

Or I should create Map first so that the fields naming only 1 for each tables.

Thanks,

I have the same question (0)

All responses (6)

Answers (1)

Suggested answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi WWwong,

What is your requirement? If you want to use Union between tables, you can change "Query type" property on the query to Union.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Sergei,

No, I'm creating query through AOT, not X++

The requirement is create a report contain a group of tables (which may different fields) , listing some of the fields from that tables.

So plan is to create this query and use it in the report

Thanks

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Sergei,

My bad, I found the property, now changing to "Union"

but when I use it in the report, when select Query, it says the query is not valid.

All I did in that query was just :

1. add table, select few fields

2. add another table, select few fields

There is Range on each of the tables, but I guess it is not the cause of the error.

That's all

Was this reply helpful? Yes No
Verified answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi WWwong,

"Query type" is set up in AOT for query created in AOT.

But it's still not clear. If you want to bring additional information to the main table - it's join case. For example, you have Customer transaction and you want to add Customer name from Customer table. But if you want to show all transactions for certain party (which can be Customer and Vendor) you can use Union and combine different tables to the one view.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

The query has always one main data source. If you want many independent "main" data sources, just create many queries and execute them after each other. Not sure why you would want to do that, though. But this way you can first get some customers, then items, then currencies. Perhaps I don't understand what you want to do. Could you explain your actual business requirement?

If you need this "multiple queries" approach in a report, make the report use tmp table as data source and populate the tmp table from your multiple queries.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

The purpose of this report is just to combine multiple tables because actually it contain similar information (but unfortunately those tables structure is not identical).

Anyway found Microsoft docs about this, and clearly it is says for Union, the table structure need to be identic.

https://docs.microsoft.com/en-us/dynamicsax-2012/developer/how-to-combine-data-sources-in-a-union-query

Thanks

Was this reply helpful? Yes No