How to Auto Add relationship Between two tables in Crystal Report using QODBC

Problem Description:

How to Auto Add relationship Between two tables in Crystal Report using QODBC?

Solution:

A relationship can be extracted using the ODBC function "SQLForeignKeys" or "SP_FOREIGNKEYS."

Usage: SP_FOREIGNKEYS tablename tablename

Instruction: Returns a record set of the detailed relationship information of two tables.

Example: sp_foreignkeys Customer Invoice

Crystal reports application automatically uses the relationship method and sprovides a user-friendly graphic interface to view the relationships between two or more tables.

To Auto Add relationship Between two tables in Crystal Report, Please create a new report in Crystal Report & connect to QuickBooks Data through QODBC. Please refer to How to use QODBC with Crystal Reports for connection detail.

I am assuming that you have referred above link & connected to QuickBooks from Crystal Report using QODBC.

The connection was made with QuickBooks through "QuickBooks Data" DSN & you can see the list of tables and windows list of tables available.

Select the table you want to add to the Crystal Report & click the ">" button. In this example, I am selecting the Customer & Invoice table.

The Customer & Invoice table data is added to the Crystal Report. Please click the "Next" button for link tables.

Please select the "By Key" option & Crystal Report will automatically link tables according to the Primary Key & Foreign Key relationship between the two tables of QODBC.

You can see that the Invoice table's "CustomerRefListID" is related to the Customer table's "listed" field.

Suppose you select the "By Name" option. In that case, Crystal Reports will not consider the Primary Key & Foreign Key relationship between two tables of QODBC & it will link according to a similar column name.
For Example Invoice table's ClassRefListID with the Customer table's "listed."

The table fields are now available for selection in your report. Now, You can perform any operation on table data using Crystal Reports.

In this example, I have added the Customer table's Phone, Email, and Balance fields & Invoice table's CustomerRefFullName, RefNumber, and SubTotal fields in the report.