Master/detail table relationship problems 
Author Message
 Master/detail table relationship problems

I have a form which shows data from a detail table linked to a master table.
The link is via a master index created from one field in the master but the
detail table also has this field for linking but cannot easily have a master
index as the records in this table are not unique and Paradox tables require
the master index to be unique.  The best example of my problem I can think
of would be a form which shows an invoice line item records.  The invoice
table being the master table which stores the invoice info with the detail
table storing the line item detail.  But what if the user has two identical
line items so there are two records in the detail table with the same Part
No.  Then I do not have unique records and get a key violation error.  I
have tried introducing another field called say LineNo to give the table a
unique multi field index but in my application I need to allow the user to
insert lines at will.  There lies the problem.  I have had a lot of trouble
in trying to re-sequence the LineNo fields on a record insert.  Sorry for
the long winded explanation.  Do I need the line item field.  How do I
re-sequence them on an insert.   Does anyone have any guidance to offer.

--



Wed, 18 Jun 1902 08:00:00 GMT  
 Master/detail table relationship problems
Hello,

you should never rely on fields with customer input.
My approach to this is that every table has an ID field as the first field.
I use a separate table to supply my tables with ID numbers.
Then I never use Master/Detail relationships but always use Filtering of the
items table. Thereby you can use whatever index you want.
I have MasterTableID field in MasterTable and DetailTableID field in
DetailTable. Then I have a MyMasterTableID  in DetailTable to be able to
filter on the items with the same MasterTableID, which is your 'invoice
header'.
Then your DBGrid will show only the item records belonging to the header
record, all sorted by your index field. (Your index could be the 'line
number field')
No unique keys, no trouble :-)

I use dBase tables, which are very suitable for indexes and compound
indexes. They don't require a unique key unless you tell it to.
If Paradox don't do what you want, use dBase tables instead. They are built
in too.

:)
Kai Inge



Quote:
> I have a form which shows data from a detail table linked to a master
table.
> The link is via a master index created from one field in the master but
the
> detail table also has this field for linking but cannot easily have a
master
> index as the records in this table are not unique and Paradox tables
require
> the master index to be unique.  The best example of my problem I can think
> of would be a form which shows an invoice line item records.  The invoice
> table being the master table which stores the invoice info with the detail
> table storing the line item detail.  But what if the user has two
identical
> line items so there are two records in the detail table with the same Part
> No.  Then I do not have unique records and get a key violation error.  I
> have tried introducing another field called say LineNo to give the table a
> unique multi field index but in my application I need to allow the user to
> insert lines at will.  There lies the problem.  I have had a lot of
trouble
> in trying to re-sequence the LineNo fields on a record insert.  Sorry for
> the long winded explanation.  Do I need the line item field.  How do I
> re-sequence them on an insert.   Does anyone have any guidance to offer.

> --




Wed, 18 Jun 1902 08:00:00 GMT  
 Master/detail table relationship problems



Quote:
> have tried introducing another field called say LineNo to give the table a

A common way of doing it.

Quote:
> unique multi field index but in my application I need to allow the user to
> insert lines at will.  There lies the problem.  I have had a lot of

trouble

Why insert?
The normal way of doing it is simply to add the new lines at the bottom.

Finn Tolderlund



Wed, 18 Jun 1902 08:00:00 GMT  
 Master/detail table relationship problems
Why insert?
The user wishes to insert so they have control over the sequence in which
the records are displayed and printed.


Wed, 18 Jun 1902 08:00:00 GMT  
 Master/detail table relationship problems
Hi Paul,

Try using a key field in your detail table that has no relevance to the
master-detail link.
For example
Invoice table has the following fields, Invoice Number, Date, etc, etc
Line item table has the following fields, Line ID, Sort Order, Invoice
Number, Description, etc, etc
Line ID is the key field, which can be an autoinc or similar. Use a
secondary index on Invoice Number AND Sort Order to join the two tables and
the user never has to see the key field. Because the index contains Sort
Order, the user can choose in what order the records are displayed.

I hope this helps
Cheers,
Mark Gibson


Quote:
> Why insert?
> The user wishes to insert so they have control over the sequence in which
> the records are displayed and printed.



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Query on the subset in a master-detail table relationship

2. Different detail Index order within master-detail relationship

3. Master-Detail Relationship problem

4. Master/Detail relationship problem

5. Problem when inserting records in the detail table (master/detail relation)

6. MASTER/DETAIL relationship

7. Auto Increment Fields and Master Details Relationship

8. Q: Master-Detail relationship using DBase records

9. DBGrid master/detail relationship?

10. Error using master/detail relationship?

11. Master-detail Relationship

12. why wont this master/detail relationship work?

 

 
Powered by phpBB® Forum Software