The Paradox form system has an interesting peculiarity: any field which happens to be constrained by a foreign key within a native .DB Paradox data model automatically gets lookup values available for the user, but this is not available other database formats, whether they be natively supported within the BDE, or accessed using ODBC, so lookups have to be added in code.
The obvious mechanism for this is to build an ad-hoc query in a generic popup window, but this increases maintenance work, while it can be possible to create a generic query from the database schema. This is the query that works in Interbase 6.5 and Firebird 1.0x.
select RC.RDB$CONSTRAINT_NAME ConstraintName, RI2.RDB$RELATION_NAME MasterTable, RIS.RDB$FIELD_NAME MasterField from RDB$RELATION_CONSTRAINTS RC left join RDB$INDICES RI1 on RC.RDB$INDEX_NAME = RI1.RDB$INDEX_NAME left join RDB$INDICES RI2 on RI1.RDB$FOREIGN_KEY = RI2.RDB$INDEX_NAME left join RDB$INDEX_SEGMENTS RIS on RI2.RDB$INDEX_NAME = RIS.RDB$INDEX_NAME where RC.RDB$RELATION_NAME = 'TWHERE' and RC.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY' order by RI1.RDB$RELATION_NAME, RC.RDB$CONSTRAINT_NAME, RI2.RDB$RELATION_NAME, RIS.RDB$FIELD_POSITION
This query returns the list of foreign keys in the form of triplets:
- Constraint name : the name of the FK rule in the schema
- Master table : the table holding the master field
- Master field : the master field for the relation
From there, it is possible to