Lookup and foreign keys
I am designing a report based on a SQL database with multiple table and extensive use of foreign keys in table , one of this fields is user name , in the main table user names are displayed as a numbers , within the main table there are a number of different fields that use names in roles eg Application Manager , Business Manager , completer etc
I want the report to display the name rather than the number , I used the database expert and linked one of the names field to the name table and it worked however as soon as linked another one to the same table all data disappeared. So I need to do something on lines of excel vlookup , that is looking up the number and producing the name for that name in the approriate field, I am assuming the best way would be to use formula fields , but have no idea on what the syntax would be
thanks in advance
Jason Long replied
Well let me back up and confuse things a little more.
If you go with the SQL Command you will most likely want to remove all tables due to performance issues. CR doesn't usually play nice when you mix other data sources with Commands.
The way arround this is to code all of your data sources in the SQL.
I know this seems pretty heavy right now, but if you do a lot of reports, learning the SQL for your database is well worth the effort. SQL is actually very easy to learn.
If you can provide the following information, I'll help you with the code.
1) What type of RDBMS are you pulling against?
2) What are the tables you want to use?
3) Which fields are you using from those tables?
4) Which fields can be used to link the tables.
I can also provide a good book that will get you up and running fast.