Board index » delphi » Master-detail in one grid???

Master-detail in one grid???

Hi, all

This question is about Delphi-3 and Oracle-7.3.3

Advice pls how to show two tables (e.g. Customer and Orders)
in one DBGrid when each Customer has one ore more Orders?
I can make view like
   select c.name, o.name
   from customer c, order o
   where o.cust_id=c.id;

BUT the problem is: I don't want to see customer.name for every his order.
The repeating names should look like asterisks, dashes or something else
(it doesn't matter).
The grid should look like:

| Customer ONE   |   Order 1 of Customer ONE |
| ************          |   Order 2 of Customer ONE |
| Customer TWO   |   Order 1 of Customer TWO |
| ************          |   Order 2 of Customer TWO |
| ************          |   Order 3 of Customer TWO |

PS. If you know Microsoft FoxPro: it was simple to do this,
just by making "set relation..." between tables and "set skip to...".
What about Delphi?

Thanks in advance,
Oleg
o...@ca-ib.com

 

Re:Master-detail in one grid???


If it's a read only query, just loop thru the query after you execute it
(with OPEN) and fix the data. Disablecontrols before you do it, to avoid the
flicker.
Quote
Oleg Roshchin wrote in message <75adbj$ka...@news.netway.at>...
>Hi, all

>This question is about Delphi-3 and Oracle-7.3.3

>Advice pls how to show two tables (e.g. Customer and Orders)
>in one DBGrid when each Customer has one ore more Orders?
>I can make view like
>   select c.name, o.name
>   from customer c, order o
>   where o.cust_id=c.id;

>BUT the problem is: I don't want to see customer.name for every his order.
>The repeating names should look like asterisks, dashes or something else
>(it doesn't matter).
>The grid should look like:

>| Customer ONE   |   Order 1 of Customer ONE |
>| ************          |   Order 2 of Customer ONE |
>| Customer TWO   |   Order 1 of Customer TWO |
>| ************          |   Order 2 of Customer TWO |
>| ************          |   Order 3 of Customer TWO |

>PS. If you know Microsoft FoxPro: it was simple to do this,
>just by making "set relation..." between tables and "set skip to...".
>What about Delphi?

>Thanks in advance,
>Oleg
>o...@ca-ib.com

Re:Master-detail in one grid???


Quote
On Thu, 17 Dec 1998 10:54:08 +0300, "Oleg Roshchin" <o...@ca-ib.com> wrote:
>This question is about Delphi-3 and Oracle-7.3.3

>Advice pls how to show two tables (e.g. Customer and Orders)
>in one DBGrid when each Customer has one ore more Orders?
>I can make view like
>   select c.name, o.name
>   from customer c, order o
>   where o.cust_id=c.id;

>BUT the problem is: I don't want to see customer.name for every his order.
>The repeating names should look like asterisks, dashes or something else
>(it doesn't matter).
>The grid should look like:

>| Customer ONE   |   Order 1 of Customer ONE |
>| ************          |   Order 2 of Customer ONE |
>| Customer TWO   |   Order 1 of Customer TWO |
>| ************          |   Order 2 of Customer TWO |
>| ************          |   Order 3 of Customer TWO |

>PS. If you know Microsoft FoxPro: it was simple to do this,
>just by making "set relation..." between tables and "set skip to...".
>What about Delphi?

This FoxPro way of handling this is not the norm. The norm is for every
Detail row in a 1:m relationship to also contain the data from the
corresponding row from the Master table.

There is no built-in way in Delphi to override this behavior, but there are
probably quite a few ways to do it with special handling.

One way would be to represent the Master table data with Delphi calculated
fields (not calculated fields in the SQL statement itself). If you have
some way of determining whether a given Detail row is the first or a
subsequent row for the Master row group set, you could alternately set the
Master row calculated field to either the actual data or asterisks.

Another way would be to do this in a stored procedure. Not sure about the
exact syntax in Oracle, but in InterBase rows can be selected using a
SELECT..FOR statement in a stored procedure. This retrieves rows on a
sequential basis, in a loop, and allows other processing for each row
before returning the row to the front-end application. Using a variable in
this loop, you could count the Detail rows retrieved for a given Master
table row. If it is the first, return actual Master table data for the row.
If it is other than the first, return asterisks. In this case, the
asterisks are data returned by the database back-end and so require no
special handling by the application front-end.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "The knowledge of the world is only to
Technical Publications              be acquired in the world, and not in a
INPRISE Corporation                 closet."
http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)

Re:Master-detail in one grid???


On Thu, 17 Dec 1998 16:49:27 GMT, skoter...@NOSPAMinprise.com (Steve

Quote
Koterski) wrote:
>On Thu, 17 Dec 1998 10:54:08 +0300, "Oleg Roshchin" <o...@ca-ib.com> wrote:

>>This question is about Delphi-3 and Oracle-7.3.3

>>Advice pls how to show two tables (e.g. Customer and Orders)
>>in one DBGrid when each Customer has one ore more Orders?
>>I can make view like
>>   select c.name, o.name
>>   from customer c, order o
>>   where o.cust_id=c.id;

>>BUT the problem is: I don't want to see customer.name for every his order.
>>The repeating names should look like asterisks, dashes or something else
>>(it doesn't matter).
>>The grid should look like:

>>| Customer ONE   |   Order 1 of Customer ONE |
>>| ************          |   Order 2 of Customer ONE |
>>| Customer TWO   |   Order 1 of Customer TWO |
>>| ************          |   Order 2 of Customer TWO |
>>| ************          |   Order 3 of Customer TWO |

>>PS. If you know Microsoft FoxPro: it was simple to do this,
>>just by making "set relation..." between tables and "set skip to...".
>>What about Delphi?

>This FoxPro way of handling this is not the norm. The norm is for every
>Detail row in a 1:m relationship to also contain the data from the
>corresponding row from the Master table.

>There is no built-in way in Delphi to override this behavior, but there are
>probably quite a few ways to do it with special handling.

>One way would be to represent the Master table data with Delphi calculated
>fields (not calculated fields in the SQL statement itself). If you have
>some way of determining whether a given Detail row is the first or a
>subsequent row for the Master row group set, you could alternately set the
>Master row calculated field to either the actual data or asterisks.

One way to do that would be to have another copy of the detail TTable.
The first detail TTable (the one you view in the grid) has a lookup into
the master TTable to get the customer name.  The second one is set up as
a child of the master TTable using the MasterSource, MasterFields, and
IndexFieldName properties.  The two detail tables will then be set to
the same row only for the first detail record for a given master record.

- Show quoted text -

Quote
>Another way would be to do this in a stored procedure. Not sure about the
>exact syntax in Oracle, but in InterBase rows can be selected using a
>SELECT..FOR statement in a stored procedure. This retrieves rows on a
>sequential basis, in a loop, and allows other processing for each row
>before returning the row to the front-end application. Using a variable in
>this loop, you could count the Detail rows retrieved for a given Master
>table row. If it is the first, return actual Master table data for the row.
>If it is other than the first, return asterisks. In this case, the
>asterisks are data returned by the database back-end and so require no
>special handling by the application front-end.

>//////////////////////////////////////////////////////////////////////////
>Steve Koterski                      "The knowledge of the world is only to
>Technical Publications              be acquired in the world, and not in a
>INPRISE Corporation                 closet."
>http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)

--
Report all {*word*201} mail to your Potsmaster.

Re:Master-detail in one grid???


Quote
Steve Koterski wrote in message <3679346b.440005@newslist>...
>On Thu, 17 Dec 1998 10:54:08 +0300, "Oleg Roshchin" <o...@ca-ib.com> wrote:

>>This question is about Delphi-3 and Oracle-7.3.3

>>Advice pls how to show two tables (e.g. Customer and Orders)
>>in one DBGrid when each Customer has one ore more Orders?
>>I can make view like
>>   select c.name, o.name
>>   from customer c, order o
>>   where o.cust_id=c.id;

>>BUT the problem is: I don't want to see customer.name for every his order.
>>The repeating names should look like asterisks, dashes or something else
>>(it doesn't matter).
>>The grid should look like:

>>| Customer ONE   |   Order 1 of Customer ONE |
>>| ************          |   Order 2 of Customer ONE |
>>| Customer TWO   |   Order 1 of Customer TWO |
>>| ************          |   Order 2 of Customer TWO |
>>| ************          |   Order 3 of Customer TWO |

>>PS. If you know Microsoft FoxPro: it was simple to do this,
>>just by making "set relation..." between tables and "set skip to...".
>>What about Delphi?

>This FoxPro way of handling this is not the norm. The norm is for every
>Detail row in a 1:m relationship to also contain the data from the
>corresponding row from the Master table.

>There is no built-in way in Delphi to override this behavior,

Seems like it would be a useful option, not just for displaying data in
grids, but especially for reports. Anyway, as I said in my posting, a few
lines of code after you open the query will fix it.

Other Threads