I guess I 'll have to switch to Firebird or dBase or something...
Thank you anyway.
Not so fast..
I made an example for SQLite. You need to complete a number of steps in the right order. Hope I'll get it right here..
In general, first make the tables. In the demo they are created by code. Then, when you have a lookup table, go ahead and make a lookup query.
Right here is something easy to miss: For designtime, DatabaseName in object inspector needs the full path, or you get a message like "table doesn't exist" because SQLite autocreates its database and it will be empty without tables. So the message will be correct. And you need to activate the queries at designtime to make fields available, or you cant make the lookup table at design time.
Now activate the lookup query (genres in this example) and you get the FieldDefs property populated.
Here comes the next easy thing to miss: Double click the Lookup Query. Then click the plus sign. Then add all fields just to be sure. (maybe you dont really need all of them, but do it for now) If you dont do this step, the fields will not be available for the lookup later and you get message that some field doesn't exist even though it exists in the table.
Right. On to the movies table. Here we will create the lookup field, but first we should do as with the lookup query. Activate the movies query, then doubleclick it and add all fields.
And now its time for the lookup field: click the sheet icon which is to the right of the plus sign. Select Lookup and fill in:
Key fields: fk_genre_id
Dataset: qryGenres
Lookup fields: genre_id
Result fields: genre_name
Click OK and now you have a lookup field called genre_name that you can use to get the name instead of the id number of records etc.
In the demo its used as a combobox in a grid. Btw, you can add, edit and delete movie records. Check out the SQL statements in the movies query.