How do I join datasources with no direct relation in a form?

Dynamics 365 for Finance and Operations/AX Developer Forum
1

I have 3 tables:

  • Companies
  • Aircrafts
  • Aircraft types

Companies have a one to many relation with aircrafts, aircrafts have many to one relation with Aircraft types.

In my form I want to display a grid of all Aircraft types that all of the Aircrafts that Company own. How would I make this filter with no direct relation?

2

I can imagine something like this:

select aircraftTypes
	exists join aircraft
		where aircraft.Type = aircraftTypes.Id
		   && aircraft.Owner = "company A"

Does it roughly match your situation?

3

Yes, but I don’t know how would I display this in a grid in a form where Companies is the main datasource and the grid would display all Aircraft types for selected Company.

4

Ah, you should have said that!

I assume you have a grid bound to Companies data source.

Then add Aircraft datasource linked to Companies via Delayed link and use it as the source of a second grid. If you have proper relation between these two tables, Aircraft will be automatically filtered based on the selected record in Companies.

To get details from AircraftTypes, add a datasource joined with Aircraft datasource (again, it requires a table relation). Then you can use its fields in the second grid.

5

This is what I did before. When I select any company it shows the same the same count of aircraft types as aircrafts the company has. And just picks the first records from aircraft type table.

Must be a table relation problem. I created relations by dragging EDTs. This is how it looks. Is this considered proper relation between tables?

pastedimage1548156256830v1.png

pastedimage1548156280973v2.png

Edit: just re-read your answer. Seems like I didn’t explain my problem clear enough, sorry. The form I need is Tab1 - Grid of companies , Tab2 - Grid of aircrafts for selected company, Tab3 - Grid of aircraft types for all aicracts for selected company. Tab1 and Tab2 works fine, Tab3 is what I cannot figure out.

Table structure is:

CompanyTable:

  • CompanyCode (PK)

AircraftTable:

-AircraftCode(PK)

-AircraftType(FK)

-CompanyCode(FK)

AircraftType:

-AircraftType (PK)

6

Review also links between data sources and that each grid is bound to the right data source.