Parent Child Relation and Child Table index

[mstrcmtr]

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

 

[Olaf Doschke]

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.

 

[mstrcmtr]

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

 

[Olaf Doschke]

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.

 

[Olaf Doschke]

And in SQL it's simply:

Having Parent display in a grid?
In AfterRowColChange do:

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.

 

[JRB-Bldr]

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.

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

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

 

[Olaf Doschke]

Yes, but the question was about the child order.

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.