×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Parent Child Relation and Child Table index

Parent Child Relation and Child Table index

Parent Child Relation and Child Table index

(OP)
In Parent Child Relation Child Table index set on same Parent table index

Select Parent

Set Relation to Invoice_no into Child

Set Skip to Child

e.g. IF Sale Header Table set on Field Invoice_No then Child Table index also set to Invoice_No to create 1 to Many Relationship

Problem

Is there any way that child table index change e.g. to Field ItemName and Parent remain on Field Invoice_No and Parent Child relation also NOT Break

RE: Parent Child Relation and Child Table index

What does SET RELATION do?

The relation is a setting quite like a filter setting. Whenever you move around in Parent, then Parent.Invoice_no is seeked in whatever current index Child is ordered by. The order of Child is not saved in the relation setting, so it doesn't call the SEEK() function with the index tag the Child table was ordered by at time of the SET RELATION.

Therefore you don't have a chance to have a relation and then change the ordering of child data. What you can make use of is compound indexes by defining an index on eg BINTOC(invoice_no)+ItemName and SET RELATION TO BINTOC(Invoice_no) into Child. That with SET EXACT OFF means the SEEK makes use of the first part of the index expression only, and as that part is equal within the group of all items of the same invoice your (Child) items of the current invoice are sorted by name.

The simpler solution is SQL queries, as they can easily make use of multiple simple indexes for joining and sorting and you don't clutter you CDX with special purpose indexes.

Bye, Olaf.

RE: Parent Child Relation and Child Table index

(OP)
Thanks for your valuable reply

Please guide how will handle relation of Parent Child if Field Type Date instead of Numeric with Character Field then e.g. Instead of Invoice_no and ItemDesc field is InvDate and ItemDesc

RE: Parent Child Relation and Child Table index

Well, I advise against usage of specific indexes. But you need something turning any type into string: Transform does that. BINTOC() is very special ans specific to numeric data and for dates you can also usse DTOC, of course. Just hink about an expression that can concatenate two different data types and you always end up at string types.

Bye. Olaf.

RE: Parent Child Relation and Child Table index

And in SQL it's simply:

Having Parent display in a grid?
In AfterRowColChange do:

CODE -->

Select * From Child Where Invoice_No = Parent.Invoice_No order by ItemName Into Cursor crsItems1
Select * From Child Where InvDate = Parent.InvDate order by ItemDesc Into Cursor crsItems2 
Much simpler logic, isn't it? It only requires indexes on invoice_no, invdate, itemname, itemdesc (unless it's memo), no special index expressions.

You may use temporary IDX to create indexes for RELATION purposes, but that costs extra, remember you index the whole table, not just one invoice.

Bye, Olaf.

RE: Parent Child Relation and Child Table index

Quote (mstrcmtr)

In Parent Child Relation Child Table index set on same Parent table index
It is NOT required for the Parent and the Child to have the SAME Index active.

Yes the Child table must have an Active Index (SET ORDER TO <whatever>)
But the Parent does NOT need to have ANY Index active or it may have a totally different Index Active.

CODE -->

USE ChildDBF IN 0 ORDER Index1
USE ParentDBF IN 0  && No Index Active
SELECT ParentDBF
SET RELATION TO <expression matching ChildDBF Index1 expression> INTO ChildDBF 
or equally effective

CODE -->

USE ChildDBF IN 0 ORDER Index1
USE ParentDBF IN 0 ORDER MyOwnIndex
SELECT ParentDBF
SET RELATION TO <expression matching ChildDBF Index1 expression> INTO ChildDBF 

In both cases the Parent's Relation is established by matching the expression with the ChildDBF's Index expression.
NOTE: It is not REQUIRED that the field(s) used for the Parent's expression match the ChildDBF's Index expression fields - only the expression values much match.

You might be able to better understand this by doing a simple test.
Create 2 tables and open both into separate Browse windows
Now set an Index on the ChildDBF and no Index on the ParentDBF
Go to the ParentDBF and set up your Relation.
Manually move the record pointer through the ParentDBF and watch what happens in the ChildDBF

You can do other similar tests to better understand how things work.

Good Luck,
JRB-Bldr

RE: Parent Child Relation and Child Table index

Yes, but the question was about the child order.

Quote:

Is there any way that child table index change
The child table is the one needing to have the correct index to be SEEKed in with a parent table value to position on related data, most often foreign key.
Also wanting to order all records of a specific foreign key (eg all order items of an order) requires an index expression combining foreign key and field to sort by. Which then has the limited capability to subsort all items of a certain equal foreign key.

So, I already addressed this question the right way, no need to go into the direction of parent sorting. That of course is totally independant.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close