Table relations defined in one table but not the other?

(0) Share

Report

Posted on by AXGreenHorn

135

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?

*This post is locked for comments

I have the same question (0)

All responses (13)

Answers (1)

Sort by

Suggested answer

Satish Panwar 14,673 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hi AXGreenHorn,

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.

Thanks,

Satish Panwar

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.

Was this reply helpful? Yes No
AXGreenHorn 135 on at

Like (0)

Report
Copy link

Link copied!

"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?

Was this reply helpful? Yes No
Verified answer

Satish Panwar 14,673 Moderator on at

Like (1)

Report
Copy link

Link copied!

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.

Thanks,

Satish Panwar

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.

Was this reply helpful? Yes No
AXGreenHorn 135 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Suggested answer

Satish Panwar 14,673 Moderator on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
AXGreenHorn 135 on at

Like (0)

Report
Copy link

Link copied!

Satish,

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.

Was this reply helpful? Yes No
Suggested answer

Satish Panwar 14,673 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

Thanks,

Satish Panwar

Was this reply helpful? Yes No
Mark Boy2 506 on at

Like (1)

Report
Copy link

Link copied!

Oh, Satish said the relation using on the query(datasource)/form(datasource) and so on , but you only focus on the table~

for example:

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.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,172 Moderator on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Suggested answer

Satish Panwar 14,673 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi AXGreenHorn,

Please let us know if you have any further questions on this thread.

Thanks,

Satish Panwar

Was this reply helpful? Yes No