• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

how to see values of the child table on the same form where the walue of the parent table is

Hi,
I have the following tables: GOODS, COLOUR, GOODS_COLOUR. ID2 is primary key of table GOODS, NAME is the name of goods in table GOODS. COLID is a primary key of table COLOUR, COLNAME is the name of colour in table COLOUR. ID2, COLID serve as composite primary key of table GOODS_COLOUR. There may be up to 4 records in GOODS_COLOUR relating to the same ID2 in GOODS. What I need is to show NAME and all relating to it COLNAME on the same DELPHI form. I now how to show all four records, but the problem is that I need them on the same raw like
NAME | COLNAME1 | COLNAME2 | COLNAME3 | COLNAME4
 or like this
NAME | COLNAME1
            COLNAME2
            COLNAME3
            COLNAME4
where COLNAME appears like LookUpListBox but there should be only those COLNAMEs relating to this particular ID2.
0
Anni74
Asked:
Anni74
  • 7
  • 3
  • 3
  • +1
1 Solution
 
jimyXCommented:
Do you mean you want to show the result in a LookUpListBox ?
0
 
jimyXCommented:
Add DBLookupListBox and Datasource to your form and set your database connectivity and connect the Datasource to the DBQuery then set the following properties of the DBLookupListBox:

KeyField = ColName
ListField = ColName
ListSource = Datasource

The SQL code for the DBQuery is:

select g.name, c.colname from goods g join color c on (g.id2 = c.colID) where g.id2=1

Open in new window

0
 
jimyXCommented:
BTW this question should be in the zone Delphi Programming and Oracle.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Ephraim WangoyaCommented:

Drop a TEdit on your form and set it to readonly. This will hold the child records
I'm assuming you already defined your MasterSource and MasterFields for the ChildDataset

Create a DataSourceChange event on your master dataset, and add code as follows
procedure TForm1.MasterDatasourceDataChange(Sender: TObject; Field: TField);
begin
  Edit1.Text := '';
  ChildDataset.DiasbleControls;
  while not ChildDataset.Eof do
  begin
     Edit1.Text := ChildDataset.FieldByName('COLNAME').AsString + ',';
     ChildDataset.Next;
  end;
  ChildDataset.EnableControls;
end;
0
 
Ephraim WangoyaCommented:
error on example, change it to

procedure TForm1.MasterDatasourceDataChange(Sender: TObject; Field: TField);
begin
  Edit1.Text := '';
  ChildDataset.DiasbleControls;
  while not ChildDataset.Eof do
  begin
     Edit1.Text := Edit1.Text + ChildDataset.FieldByName('COLNAME').AsString + ',';
     ChildDataset.Next;
  end;
  ChildDataset.EnableControls;
end;

0
 
Ephraim WangoyaCommented:
Second alternative

Create a calculated string field on your master dataset eg 'COLNAMES'

Create an OnCalculate field event and code is similarly to the example above

procedure TForm1.MasterDatasetCalcFields(DataSet: TDataSet);
var
  S: string;
begin
  S := '';
  ChildDataset.DiasbleControls;
  while not Dataset.Eof do
  begin
     S := S + ChildDataset.FieldByName('COLNAME').AsString + ',';
     ChildDataset.Next;
  end;
  ChildDataset.EnableControls;
  DataSet.FieldByName('COLNAMES').AsString := S;
end;

0
 
Anni74Author Commented:
Hi, jimi, thank you very much for your support.
1) Yes, i would like it to be like LookUpListBox but read only for every id2 and relating only to this particular id2. There may be lots of records shown in the DBGrid. So I want LookUpListBox with colnames for every id2 record.
2) I do not understand why in the query you suggest id2 = 1?

Thanks in advance
0
 
jimyXCommented:
>  why in the query you suggest id2 = 1?
That was just an example since you did not specify how it will be matched in the database from your project.

You can use the "DBGrid.Fields[X].Text", I assumed it is field 1 so it can be:

procedure TForm1.DBGridCellClick(Column: TColumn);
begin
  DBQuery.Close;
  DBQuery.SQL.Text := 'select g.name, c.colname from goods g join color c on (g.id2 = c.colID) where g.id2='+ DBGrid.Fields[1].Text;
  DBQuery.Open;
end;

Open in new window

0
 
jimyXCommented:
Also it can be match with the BDQueryMain that fills the DBGrid:

procedure TForm1.DBGridCellClick(Column: TColumn);
begin
  DBQuery.Close;
  DBQuery.SQL.Text := 'select g.name, c.colname from goods g join color c on (g.id2 = c.colID) where g.id2='+ DBQueryMain.FieldByName('ID2').AsString;
  DBQuery.Open;
end;

Open in new window

0
 
Anni74Author Commented:
Hi, JimyX,
there is also a third table GOODS_COLOUR which serves as linking table to solve many -to-many relationship between GOODS and COLOUR with fields ID2, COLID.
0
 
Emmanuel PASQUIERFreelance Project ManagerCommented:
If you use a parameter in your query you don't have to build it always. And you don't need to join the GOOD table in the select if you know the ID of the good :

so in design time, you set the DBQuery SQL that will serve for your DBListBox :
DBQuery.SQL.Text:='SELECT c.*  FROM GOODS_COLOUR gc join COLOUR c on gc.COLID=c.COLID WHERE gc.ID2=:GOOD_ID';


procedure TForm1.GetColoursOfGood(ID_Good:Integer);
begin
 DBQuery.Close;
 DBQuery.Params[0].AsInteger:=ID_Good;
 DBQuery.Open;
end;

Open in new window

0
 
jimyXCommented:
Ok, here you are:

procedure TForm1.DBGridCellClick(Column: TColumn);
begin
  DBQuery.Close;
  DBQuery.SQL.Text := 'select g.name, c.colname from goods g, color c, goods_color gc where g.id2 = c.colID and c.colid = gc.colID and gc.id2='+ DBQueryMain.FieldByName('ID2').AsString;
  DBQuery.Open;
end;

Open in new window

0
 
jimyXCommented:
Please discard my last post, here is an updated one:

procedure TForm1.DBGridCellClick(Column: TColumn);
begin
  DBQuery.Close;
  DBQuery.SQL.Text := 'select g.name, c.colname from goods g, color c, goods_color gc where g.id2 = gc.id2 and c.colid = gc.colID and gc.id2='+ DBQueryMain.FieldByName('ID2').AsString;
  DBQuery.Open;
end;

Open in new window

0
 
Anni74Author Commented:
Thank you very much for your support.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 7
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now