Now Available in Community - MBAS 2019 Presentation Videos
Catch the most popular sessions on demand and learn how Dynamics 365, Power BI, PowerApps, Microsoft Flow, and Excel are powering major transformations around the globe. | View Gallery
2019 release wave 2 Discover the latest updates to Dynamics 365Release overview guides and videos Release Plan | Early Access Availability
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Talent TechTalks
I'm confused with table relations. For example, if you look at the relations for CustTable, there is nothing for SalesLine, but if you look at the SalesLine table's relations, you see the relation to CustTable. Is there a reason this relationship is only documented in the SalesLine table's relations?
Also, is it possible to have relations that are not defined in the Relations section for either table, and, if so, what are the ramifications?
Also, are these relations used when defining link types for data sources? Otherwise, when you specify something like "inner join", what are you even joining on?
The relations are defined mostly one-way on table but it work both ways. Defining it on both tables will also be duplicating the effort as well. It's not necessary that all the relations should be defined, in some cases they may not be there but you can use them to search / query data. E.g. InventTransId.. Any table that has this column can be joined.
These relations are used on data sources when multiple tables are joined either via query OR via code. Look at any query in AOT that has multiple tables and you will see the relations being used that are defined on tables.
Please help verify answer(s) if they guide you in right direction so other community members can also benefit from your question with verified answer.
"Defining it on both tables will also be duplicating the effort as well."
Sure, but if I'm trying to find out how to join the CustTable to the SalesTable, I may have to look in two places rather than one, which is a waste of time that compounds over many, many times of doing this. Taking the time to write it in both places would save a lot of time, imo.
"These relations are used on data sources when multiple tables are joined either via query OR via code. Look at any query in AOT that has multiple tables and you will see the relations being used that are defined on tables."
So are you saying that if you join two tables but do not specify what you are joining on, it will use whatever was specified in the Relations section?
Please see below:
1. Defining on both side is only going to create issues. if you need to change it for whatever reason, now you need to remember to change at multiple places, so not a great approach. In addition, reviewing relations if they are defined only take few mins to figure out. In addition, that's how MS does it so we don't have an option to change it.
2. It will use whatever is specified on the tables only if you set the property that way. E.g. in AOT when you add 2nd data source, there is property on data source, use relations. if set to yes, only these the table relations are used. This is again to minimize the relationship definition over and over again.
Everything you said makes sense, however, I do not see the "use relations" property that you talked about. Can you screenshot where you are seeing that?
Glad that it helped. Please see below (it's from D365 but similar property will exist there as well. Note - I have highlighted the second data source in my query. For first data source "CustTable", the property doesn't apply and doesn't exists. Please click 'Yes' against the answers that helped you.
Thanks,Satish PanwarPlease help verify answer(s) if they guide you in right direction so other community members can also benefit from your question with verified answer.
I'm sorry, but I can't accept your answer yet because I cannot find a similar option in AX 2012 to only use the table relations. Here is what I see:
I don't think any of these properties are the equivalent.
Agree. The screenshot that you I provided above is from Queries. To which data source the above form linked to? I think it's form data source. If that's what it is, take a look at SalesTable form, expand the salesTable data source, go to next data source underneath salesTable and see the property join relation. The property name is different based on what you look at. Can you share complete screenshot that shows your AOT objects and your property if above response doesn't help you much.
Oh, Satish said the relation using on the query(datasource)/form(datasource) and so on , but you only focus on the table~
when you create a form or query, you add the InventTable and InventTrans to datasource,
you just go to the sub-datasource property and select the use relation to change “yes”
then both of tables's relation will be created.. do not create by manual.
In addition , this is only possible if there is already a table association relationship.
CustTable stores customer master data.
If any table includes Customer account field, a relation to CustTable should be defined in that table. This is how relational databases work.
But, you don't need to change the definition of CustTable (by defining new relations) every time a new table with CustAccount field is introduced.
Please let us know if you have any further questions on this thread.
Satish, this is the SalesTable form. You see how the SalesLine is set to join with the SalesTable. But I do not see an option to use the table relations.
Did you see the join datasource Option ？
SalesLine is first level data source (though it's linked with SalesTable), the automatic relation is being used. Expand the SalesLine data source and notice the next data source i.e. logisticsPostalAddressDeliveryLine and you will see joinrelation field and join table. The property / field / and how relations are defined vary in the application. After all this, what are you trying to do? Are you trying to create a form.. if yes, try to add multiple data source and see if you get data as expected or something is wrong. If it's wrong, then try to identify where to define relations etc, just understanding where is not going to help you unless you try to do it.
Business Applications communities