Lookup field.

[Chernobrivec]

Good day.
I have 2 tables in MySQL: lets say, one called Employee, second called Department. In Employee table i have foreign key department_id of Department table, field id is primary key for department.

I'm creating connection and query elements. From DataSet manager choose for dataset Employee option 'details' and drag n drop on my form.

I want to have in place of faculty_id field,lookup combo box with all rows from table department(its must be displayed name field of Department table), where the default one is that, which faculty_id equivalent to id from department. Then, when i open combo box and choose another row, faculty_id field in Employee dataset will be updated with new value.

If there any possible way to do that?

Thank you for your help.

[AlexP]

Hello,

To do this you can use the TDBLookupComboBox component.

You should place the following components onto your form:

qEmployee: TMyQuery;
qDepartment: TMyQuery;
dsEmployee: TMyDataSource;
dsDepartmen: TMyDataSource;
DepartmenDBLookupComboBox:TDBLookupComboBox;
EmployeeDBGrid:TDBGrid;

and set the following properties of these components:

dsEmployee.DataSet:=qEmployee;
dsDepartmen.DataSet:=qDepartment;
qEmployee.MasterSource:= dsDepartmen;
qEmployee.MasterField:='id';
qEmployee.DetailField:= 'department_id';
DepartmenDBLookupComboBox.ListSource:=dsDepartmen;
DepartmenDBLookupComboBox.ListField:='department_name'
DepartmenDBLookupComboBox.KeyField:='id';
EmployeeDBGrid.DataSource:=dsEmployee;

in this case after changing data in DepartmenDBLookupComboBox data in EmployeeDBGrid will be changed as well.

[Chernobrivec]

Are u sure that its must be like? Cause its not working and saying "circular links". I made like this:
qEmployee: TMyQuery;
qDepartment: TMyQuery;
dsEmployee: TMyDataSource;
dsDepartmen: TMyDataSource;
DepartmenDBLookupComboBox:TDBLookupComboBox;
EmployeeDBGrid:TDBGrid;


dsEmployee.DataSet:=qEmployee;
dsDepartmen.DataSet:=qDepartment;
qEmployee.MasterSource:= dsDepartment;
qDepartment.MasterField:='department_id';
qDepartment.DetailField:= 'id';
DepartmenDBLookupComboBox.ListSource:=dsDepartmen;
DepartmenDBLookupComboBox.ListField:='department_name'
DepartmenDBLookupComboBox.KeyField:='id';


But it gets me only those rows, which have id equivalent to Faculty_id of row. And i want to see all rows. Its must be like dictionary: when creating new row i must choose from following list.

[AndreyZ]

You should place the following components onto your form:

Code: Select all

conn: TMyConnection;
qDept: TMyQuery;
qEmp: TMyQuery;
dsDept: TMyDataSource;
dsEmp: TMyDataSource;
DBLookupComboBox: TDBLookupComboBox;
EmpDBGrid: TDBGrid;

and use this code:

Code: Select all

qDept.SQL.Text := 'select * from dept';
dsDept.DataSet := qDept;
qEmp.SQL.Text := 'select * from emp';
dsEmp.DataSet := qEmp;
DBLookupComboBox.ListSource := dsDept;
DBLookupComboBox.ListField := 'name'; // department name
DBLookupComboBox.KeyField := 'id'; // primary key of the department table
DBLookupComboBox.DataSource := dsEmp;
DBLookupComboBox.DataField := 'faculty_id'; // foreign key of the employee table
EmpDBGrid.DataSource := dsEmp;
qDept.Open;
qEmp.Open;

[Chernobrivec]

Its worked!
Thanks a lot!

[AndreyZ]

It is good to see that this problem was solved. If any other questions come up, please contact us.