Finance | Project Operations, Human Resources, ...

How to find the relation between two tables that don't have a direct relation

(1) Share

Report

Posted on by Daniel Mora

148

Hi,

I'm trying to find the relation between InventTable and CustTable but looking directly in the Relations node I can't find it. I was wondering if you can teach me on how to look for relations that are not directly related and how that would look in X++ join query using those two tables as examples as I also needed for other tables but one clear example would be just fine.

Thanks in advance.

I have the same question (0)

All responses (3)

Answers (0)

GirishS 27,827 Moderator on at

Like (0)

Report

Hi Daniel Mora,

Relation between tables will be seen under Relation node of the table.

If there is no relation mentioned under the relation node of the table - It means that there is no relationship between the tables.

Also if the table has extension then you need to check there also under AOT >> Data Models >> Table Extension.

Thanks,

Girish S.

Was this reply helpful? Yes No
Suggested answer

Arunraj Rajasekar 1,743 on at

Like (0)

Report

Hi Daniel,

Yes, occasionally the two tables we want to join won't have a direct relationship to one another. In those circumstances, you must search for third tables that both table A and table B share. The image below was obtained from an old forum thread.

There are other situations when a source document's line table and header are used to connect tables. Take a look at project accounting distribution and table accounting distribution, for instance.

Hope this clarifies

Was this reply helpful? Yes No
Martin Dráb 237,976 Most Valuable Professional on at

Like (0)

Report

As always, you need start with business requirements. You can find many scenarios where customers have something to do with items (such as sales orders, packing slips, trade agreements or shipments), but which one you're interested in depends on what you're trying to achieve.

When you know what logical relation you're interested in, you might start looking at which tables are used to store the data. You can start by opening a related form (such as of sales orders), find interested fields and look at which tables they come from. That can be done directly fro GUI.

Was this reply helpful? Yes No