[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?
Relationship can be extracted using the ODBC function "SQLForeignKeys" or using "SP_FOREIGNKEYS"
Usage: SP_FOREIGNKEYS tablename tablename
Instruction: Returns a recordset of the detailed relationship information of two tables.
Example: sp_foreignkeys Customer Invoice
Crystal reports application automatically make use of the relationship method and provide 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 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 made with QuickBooks through "QuickBooks Data" DSN & you can see the list of tables windows list of tables available.
Select the table which you want to add in the Crystal Report & click ">" button. In this example, I am selecting Customer & Invoice table.
The Customer & Invoice table data is added to the Crystal Report. Please click "Next" button for link tables.
Please select "By Key" option & Crystal Report will automatically link tables according to Primary Key & Foreign Key relationship between two table of QODBC.
You can see that Invoice table's "CustomerRefListID" is related to Customer table's "ListID" field.
If you select "By Name" option, then Crystal Report will not consider Primary Key & Foreign Key relationship between two table of QODBC & it will link according to the 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 Report.
In this example, I have added Customer table's Phone, Email, Balance field & Invoice table's CustomerRefFullName, RefNumber, SubTotal field in the report.