Dynamic Joins versus Dynamic Relations

[Bob Cergol]

What's the real difference between these?

Both allow multiple joins. Both are "virtual" in that they are defined within DAI's repository and DAI generates the SQL statement to produce the joins as needed when referenced in reports.

Is it solely that dynamic relations allow defining separate connections for the tables involved?

If I'm using the same connection for all tables why would I use one versus the other?

Bob

[Carsten Sørensen]

Hi Bob,

Well understood!! and a good question!

The fundamental difference between DAI Joins and DAI relations are:

Joins:
- Joins are part of the core data-source i.e. criteria's on a column will have true impact across all related objects.
- Joins will collect several underlying data-objects into 1 - allowing you to calculate, present, group and combine any columns across the data-objects at any level in Dynamic AI - seeing just 1 data-source.
- Dynamic AI Joins in comparison to a free-text written SQL join or a DB based join saved in a DB view will eventually be smarter in terms of how many of the objects are affected during SQL execution. Dynamic AI Joins will (when using Outer Left joins) only join the data-objects that are actually being used at that specific level of a report.

Relations:
- Relations can be cross-connection/cross-database/cross-type without any background data-source migration/merge or moving any data
- Relations can however NOT consolidate / aggregate information across the underlying data-sources. I.e. if you relate a "my ERP system.customerID" to a "my CRM system.customerID" in a system residing on another database, then you can't pick up the CRM system customer.Sales and make a total of that and subtract that from the ERP.Amount using Relations.

A classic example is if you imagine that you have a customer table and want to join/relate a Customer ID to a Customer name:
- Using a Relation you can pick up the Customer name and resolve it at each record to display the name (using a COMBO list) on a detail listing e.g. HOWEVER if you sort the column to see your customers in alpha-betical order - then you'll get the customers sorted by the CustomerID... as the looked up name is not part of the underlying SQL data-source. Using a JOIN you would get both the Customer ID and looked up Customer Name in the same data-source and a sort on the name would work as well as grouping etc.

In general I believe you will find JOINED data-sources easier to work with (when possible) as you can forget about the underlying linking.

I hope that clarifies it a bit!

Best regards,
Carsten

[Bob Cergol]

Carsten,

Of course I can't help but try to translate DAI concepts into the context of Dataflex. Relations in DAI were looking to me to be the basis for creating "selection lists" in a form. It also sounds like relations are more a "navigational" tool for linking separate forms and reports, especially when residing in separate databases.

I really need to contemplate this subject but you've given me some useful additional information -- especially the advice that joins will generally be easier for me to work with.

Thanks,
Bob