Master/Detail relationship problem 
Author Message
 Master/Detail relationship problem

Maybe I am just being stupid, but I am having trouble with the
following scenario - can anyone help?

TTable A contains a list of customers
TTable B contains a list of activity info
A & B are linked via a Master / Detail field called CustNo

When I create a form to display the data I accurately get the activity
information for just <this> customer.

Now, I would like the activity detail to be indexed (sorted) by date
as well.  These are DB4 databases and I am using indicies.  The
primary index on the detail database is sorted on the CustNo field so
that the master database can link to it.  If I add the Date field to
the expression index then I get no records at best or a Grid Out Of
Range error at worst.  

Help!




Tue, 17 Nov 1998 03:00:00 GMT  
 Master/Detail relationship problem

Quote:

> Maybe I am just being stupid, but I am having trouble with the
> following scenario - can anyone help?

> TTable A contains a list of customers
> TTable B contains a list of activity info
> A & B are linked via a Master / Detail field called CustNo

> When I create a form to display the data I accurately get the activity
> information for just <this> customer.

> Now, I would like the activity detail to be indexed (sorted) by date
> as well.  These are DB4 databases and I am using indicies.  The
> primary index on the detail database is sorted on the CustNo field so
> that the master database can link to it.  If I add the Date field to
> the expression index then I get no records at best or a Grid Out Of
> Range error at worst.

> Help!



I don't know about DB4 databases, but using Oracle
or so, I would create an index containing both
CustNo and Date

e.g.
Create index MyDetailIndex
on MyDetailTable (CustNo, Date);
commit;

Then I would use MyIndex instead.

Hope this solves your problem.

Jeppe.

PS. The syntax might be wrong. DS.



Fri, 20 Nov 1998 03:00:00 GMT  
 Master/Detail relationship problem

Quote:

>   > Maybe I am just being stupid, but I am having trouble with the
>   > following scenario - can anyone help?

>   > TTable A contains a list of customers
>   > TTable B contains a list of activity info
>   > A & B are linked via a Master / Detail field called CustNo

>   > When I create a form to display the data I accurately get the activity
>   > information for just <this> customer.

>   > Now, I would like the activity detail to be indexed (sorted) by date
>   > as well.  These are DB4 databases and I am using indicies.  The
>   > primary index on the detail database is sorted on the CustNo field so
>   > that the master database can link to it.  If I add the Date field to
>   > the expression index then I get no records at best or a Grid Out Of
>   > Range error at worst.

>   > Help!


>   I don't know about DB4 databases, but using Oracle
>   or so, I would create an index containing both
>   CustNo and Date

>   e.g.
>   Create index MyDetailIndex
>   on MyDetailTable (CustNo, Date);
>   commit;

    ^^^^^^
Anyway, IMHO 'commit' statement is not needed here. The 'create index' and
other data manipulation statements is autocommited and cant be rollbacked.

Quote:
>   Then I would use MyIndex instead.

I cant agree with this. Indexes can be used ONLY for SPEED UP
when You use any client/server (not local) DBMS. Any client/server application
must to work (may be slower) WITHOUT ANY INDEXES !. Indexes is usable only
for increase of application peformance. Note that SQL syntax don't include
index-depended clauses. Using indexes is a task of query optimizer.

You problem is that IndexFieldNames property of TTable component
have two functions: in contains sort order and linked to master field names.
So You cant make detail table sorted. Try to use TQuery with param 'CustNo'
and with 'ORDER BY clause as detail datasource instead.

Hope it helps.

---
Igor Podolsky       ! While the sun hangs in the sky and the desert has sand

   carrier.kiev.ua) !                                             (Queen'91)



Sat, 21 Nov 1998 03:00:00 GMT  
 Master/Detail relationship problem


writes:

Quote:

>Maybe I am just being stupid, but I am having trouble with the
>following scenario - can anyone help?

>TTable A contains a list of customers
>TTable B contains a list of activity info
>A & B are linked via a Master / Detail field called CustNo

>When I create a form to display the data I accurately get the activity
>information for just <this> customer.

>Now, I would like the activity detail to be indexed (sorted) by date
>as well.  These are DB4 databases and I am using indicies.  The
>primary index on the detail database is sorted on the CustNo field so
>that the master database can link to it.  If I add the Date field to
>the expression index then I get no records at best or a Grid Out Of
>Range error at worst.  

>Help!



You can have a composite index in the child file yet when you link you
only use the first field as the link field.  My assumption would be
that since the active index for the child file will be the composite
index the result of the child file will sort by default to the whole
index.

Ben
Arrow



Tue, 24 Nov 1998 03:00:00 GMT  
 Master/Detail relationship problem


Quote:


>>   > Maybe I am just being stupid, but I am having trouble with the
>>   > following scenario - can anyone help?

>>   > TTable A contains a list of customers
>>   > TTable B contains a list of activity info
>>   > A & B are linked via a Master / Detail field called CustNo

>>   > When I create a form to display the data I accurately get the
activity
>>   > information for just <this> customer.

>>   > Now, I would like the activity detail to be indexed (sorted) by
date
>>   > as well.  These are DB4 databases and I am using indicies.  The
>>   > primary index on the detail database is sorted on the CustNo
field so
>>   > that the master database can link to it.  If I add the Date
field to
>>   > the expression index then I get no records at best or a Grid Out
Of
>>   > Range error at worst.

>>   > Help!


>>   I don't know about DB4 databases, but using Oracle
>>   or so, I would create an index containing both
>>   CustNo and Date

>>   e.g.
>>   Create index MyDetailIndex
>>   on MyDetailTable (CustNo, Date);
>>   commit;
>    ^^^^^^
>Anyway, IMHO 'commit' statement is not needed here. The 'create index'
and
>other data manipulation statements is autocommited and cant be
rollbacked.

>>   Then I would use MyIndex instead.

>I cant agree with this. Indexes can be used ONLY for SPEED UP
>when You use any client/server (not local) DBMS. Any client/server
application
>must to work (may be slower) WITHOUT ANY INDEXES !. Indexes is usable
only
>for increase of application peformance. Note that SQL syntax don't
include
>index-depended clauses. Using indexes is a task of query optimizer.

>You problem is that IndexFieldNames property of TTable component
>have two functions: in contains sort order and linked to master field
names.
>So You cant make detail table sorted. Try to use TQuery with param
'CustNo'
>and with 'ORDER BY clause as detail datasource instead.

>Hope it helps.

>---
>Igor Podolsky           ! While the sun hangs in the sky and the desert
has sand

land...  
>   carrier.kiev.ua) !                                                

(Queen'91)

This is not so.  The link must not be the entire (composite) index.
You can use the first field of a composite index as the link field.
That is a must in Pdox files where a primary index must be unique.  Say
you have an order header file with a primary index of Order number.
You have an order lines file with a primary composite index of the
Order number and Order line.  You may link the line file to the header
file using the order number.  Since the primary index is active for the
line file the line file will display in the order of the line numbers.
You can use your imagination from here including setting up secondary
composite indexes  for different dispalys.

Ben
Arrow



Fri, 27 Nov 1998 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Master-Detail Relationship problem

2. Different detail Index order within master-detail relationship

3. Master/detail table relationship problems

4. MASTER/DETAIL relationship

5. Auto Increment Fields and Master Details Relationship

6. Q: Master-Detail relationship using DBase records

7. DBGrid master/detail relationship?

8. Error using master/detail relationship?

9. Master-detail Relationship

10. why wont this master/detail relationship work?

11. Delphi 2.0 -> 3.0 Master Detailed Relationships

12. Master-Detail Relationship

 

 
Powered by phpBB® Forum Software