We would like to ask how can we merge or do a inner joint based on a foreign key on two diferent tables? we are using SQL standard and CE 6.5 ultimate.
The end customer has the employee name on a database table, but their title inside the company are in another table. But tables has a relationship using the title code.
That was done because that way they don't have to write the same title to the lot of people who have the same title within the company , just the code. But as the user wants to print the company title, we have to use that code (number) to go to the second table and retrieve the title like a text.
Joints are not supported in CardExchange, but you can get them anywhere in Amsterdam . Inner joins based on a foreign key other than the field you defined as primary keys in CardExchange should be define in a view in the database (preferred way) or in an internal view in CardExchange if it is organizationally impossible to define a view in the database.
If the database needs to be edited in the database-edit window too, you should not show the field from the other table, but you can show the foreign-key field and define it in the database setup as a drop-down menu based on a database table. That way, you can show the readable field from the lookup table in CardExchange and still store the foreign-key value in the base table.
Senior Software Engineer
"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)
We have the same issue with MS access.
We wanted 10 MS Excel files to be all looked up at 1 time.
So we created 10 linked tables in MS Access, and then put them into a joint query. (but CardExchange does not support joint query)
The workaround is to create the joint query in MS access, then copy the SQL created by the join query to the <internal view> option in CardExchange.
When we pasted the SQL we needed to remove the last semi colon to get it to work.
We also had problems if all the field names were not identical in all the excel spreadsheets.
It works perfectly after that.
Jason Hicks MBA National Service & Projects Manager
[Practical Peripherals Corporation Pty Ltd (Australia)
"No we don't really eat much Kangaroo meat here, Cows are much easier to catch"
The following user(s) said Thank You Rutger Koperdraad