[QODBC-ALL] How to Auto Add relationship Between two tables in Crystal Report using QODBC
Posted by Jack - QODBC Support on 21 September 2017 06:50 AM
How to Auto Add relationship Between two tables in Crystal Report using QODBC
How to Auto Add relationship Between two tables in Crystal Report using QODBC?
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.
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.