f



get last record of group

 Hi all,

got a master DBf and a detail DBF, related by a common field ( e.g. 
client_nr)

I need to show the master records in a bBrowser, with additional columns 
that have to show for each master record, the fields of the corresponding 
LAST detail record

When using the traditional approach (Setrelation on the client_nr) I get the 
first detailrecord of each group.

I'm wondering if there is some trick I'm missing  (e.g. when using a 
dbserver:seek one cane use the [lLast] argument).

Maybe I could work around it with a conditional and/or unique indexorder on 
the detail DBF, but for the moment I would like to avoid that.


TIA


-- 
Paul 


0
polleke (29)
11/18/2009 2:24:36 PM
comp.clipper.visual-objects 12618 articles. 0 followers. Post Follow

18 Replies
982 Views

Similar Articles

[PageSpeed] 22

Hi Paul,

I would try to use a Descend Order (CDX) in the detail DBF. In taht
case Setrelation will Seek() always the first record of the groupe
wich in fact is the last you need to show.

TIA
Ernesto

>
> Maybe I could work around it with a conditional and/or unique indexorder on
> the detail DBF, but for the moment I would like to avoid that.
>
> TIA
>
> --
> Paul

0
emin
11/18/2009 6:04:04 PM
Paul

You'll need to have a descending or custom index to achieve that.

CYA
Steve 


0
Stephen
11/18/2009 10:29:54 PM
Hi Paul,

As you indicated Seek is your partner if you use CDX...

<oDBServer>:Seek(<uKey>, [<lSoftSeek>], [<lLast>]) ---> lFound

<lLast>	TRUE seeks the last occurrence of the specified key value.  FALSE, 
the default, seeks the first occurrence.  <lLast> only applies to CDX indexes.

You can always have a variable that contains the current client_nr.  Then 
as you move and DbServer:client_nr <> variable, do a Seek with lLast := TRUE.

Think that will be your best bet.

Regards,

Johan Nel
Pretoria, South Africa.

Paul D B wrote:
>  Hi all,
> 
> got a master DBf and a detail DBF, related by a common field ( e.g. 
> client_nr)
> 
> I need to show the master records in a bBrowser, with additional columns 
> that have to show for each master record, the fields of the corresponding 
> LAST detail record
> 
> When using the traditional approach (Setrelation on the client_nr) I get the 
> first detailrecord of each group.
> 
> I'm wondering if there is some trick I'm missing  (e.g. when using a 
> dbserver:seek one cane use the [lLast] argument).
> 
> Maybe I could work around it with a conditional and/or unique indexorder on 
> the detail DBF, but for the moment I would like to avoid that.
> 
> 
> TIA
> 
> 
0
Johan
11/19/2009 4:52:01 AM
Paul

> You'll need to have a descending or custom index to achieve that.
I should qualify that by adding 'as long as your using a relation'

If you don't use a relationship and set the index scope programmatically, 
you could use the Seek() method on DBServer.

CYA
Steve 


0
Stephen
11/19/2009 5:01:26 AM
Stephen Quinn wrote:
> Paul
>
>> You'll need to have a descending or custom index to achieve that.
> I should qualify that by adding 'as long as your using a relation'
>
> If you don't use a relationship and set the index scope
> programmatically, you could use the Seek() method on DBServer.
>
> CYA
> Steve

Hi Steve,

I don't think I can do this with an descending index.

Say the master file are the Customers.
The relation with the detail file is on the field #CustNr.
The detail file are the invoices per customer. Each invoice record has a 
date field.
I've got an index order on this detail file with key 
Str(InvCustNr,6)+DTos(Invdate). The order is Descending so the most recent 
invoice shows first.

So far so good but I wouldn't know how to set a relation that works. AFAIK 
in a relation you can only specify a field, not an expression.

-- 
Paul 


0
Paul
11/19/2009 8:06:01 AM
from the helpfile :

<cbRelationBlock> The relation code block for the server.

imo you can specify an expression like :

self:odbCustomers:setrelation(self:odbInvoices,{||str(_field->custnr,6)},"str(_field->custnr,6)")


>
> Hi Steve,
>
> I don't think I can do this with an descending index.
>
> Say the master file are the Customers.
> The relation with the detail file is on the field #CustNr.
> The detail file are the invoices per customer. Each invoice record has a date 
> field.
> I've got an index order on this detail file with key 
> Str(InvCustNr,6)+DTos(Invdate). The order is Descending so the most recent 
> invoice shows first.
>
> So far so good but I wouldn't know how to set a relation that works. AFAIK in 
> a relation you can only specify a field, not an expression.

-- 
Jean-Pierre Maertens


0
Jean
11/19/2009 8:18:28 AM
Jean-Pierre Maertens wrote:
> from the helpfile :
>
> <cbRelationBlock> The relation code block for the server.
>
> imo you can specify an expression like :
>
> self:odbCustomers:setrelation(self:odbInvoices,{||str(_field->custnr,6)},"str(_field->custnr,6)")
>
>
>>
>> Hi Steve,
>>
>> I don't think I can do this with an descending index.
>>
>> Say the master file are the Customers.
>> The relation with the detail file is on the field #CustNr.
>> The detail file are the invoices per customer. Each invoice record
>> has a date field.
>> I've got an index order on this detail file with key
>> Str(InvCustNr,6)+DTos(Invdate). The order is Descending so the most
>> recent invoice shows first.
>>
>> So far so good but I wouldn't know how to set a relation that works.
>> AFAIK in a relation you can only specify a field, not an expression.

Hi JP, long time no hear!

Well that works! But I tried something similar earlier but then I only 
passed in the codeblock.
 self:odbCustomers:setrelation(self:odbInvoices,{||str(_field->custnr,6)})
and then  it does NOT work.

I thought the codeblock was enough and that the string parameter was 
optional.
The Help says: cRelationBlock> When the relation is specified as a code 
block, a string version of the code block *can* be provided as well; it is 
returned by the Relation() method.


Apparantly is is not optional but obligatory.

Anyway, many thanks again

-- 
Paul 


0
Paul
11/19/2009 9:06:06 AM
Paul

A relation is set on a common field in both DBFs
The field doesn't have to be in the index expression (of the chld)
The child HAS to have an index - in your situ a descending index.


> Apparantly is is not optional but obligatory.
Providing the string version (as well as/ or instead of the cb only) is 
always 'safer/better' I've found (VO2.5)

CYA
Steve 


0
Stephen
11/19/2009 9:45:24 AM
Stephen Quinn wrote:
> Paul
>
> A relation is set on a common field in both DBFs
> The field doesn't have to be in the index expression (of the chld)

of the master, you mean... ?


-- 
Paul 


0
Paul
11/19/2009 9:55:48 AM
}aul

>> The field doesn't have to be in the index expression (of the chld)
> of the master, you mean... ?
No - the child

Matter of fact it doesn't have to be in the expression of any index for the 
relationship to work.
The only requirementa are that it's in both DBFs and the child has an index

CYA
Steve 


0
Stephen
11/19/2009 2:19:12 PM
Stephen Quinn wrote:
> }aul
>
>>> The field doesn't have to be in the index expression (of the chld)
>> of the master, you mean... ?
> No - the child
>
> Matter of fact it doesn't have to be in the expression of any index
> for the relationship to work.
> The only requirementa are that it's in both DBFs and the child has an
> index
> CYA
> Steve

Are you sure Steve? I thought that the underlying mecanism was a seek. So 
the eacht time the master moves a record - a seek is performed in the child 
.. I don't see how you can seek on a common field when it is not incuded in 
the index expression of the child.

See VO help for Setrelation::
As always, the child work area should have a controlling index that matches 
the expression."


-- 
Paul 


0
Paul
11/19/2009 3:07:41 PM
Paul,

SetRelation is not going to work for what you intend to do, except if you 
have a Str(InvCustNr, 6) + Descend(DToS(InvDate)) on the Invoice table.

Then from the master:

oCust:SetSelectiveRelation(oInv,;
                            {|| Str(_FIELD->CustNr, 6, 0)},;
                            "Str(CustNr, 6, 0)")

HTH,

Johan Nel
Pretoria, South Africa.

Paul D B wrote:
> Stephen Quinn wrote:
>> }aul
>>
>>>> The field doesn't have to be in the index expression (of the chld)
>>> of the master, you mean... ?
>> No - the child
>>
>> Matter of fact it doesn't have to be in the expression of any index
>> for the relationship to work.
>> The only requirementa are that it's in both DBFs and the child has an
>> index
>> CYA
>> Steve
> 
> Are you sure Steve? I thought that the underlying mecanism was a seek. So 
> the eacht time the master moves a record - a seek is performed in the child 
> . I don't see how you can seek on a common field when it is not incuded in 
> the index expression of the child.
> 
> See VO help for Setrelation::
> As always, the child work area should have a controlling index that matches 
> the expression."
> 
> 
0
Johan
11/19/2009 5:32:03 PM
You misunderstand.

The index is in the child because this is where the seek occurs. You are 
confirming this.

But the fields and the name does not have to be identical and the parent 
does not have to be indexed or even in the same order as the child. The 
only requirements are that the data type of the linked parent field and 
value has to match the index expression in the child. The child seek is 
triggered by a record movement in the parent.

Don't forget you can relate on more than one field.

Geoff



"Paul D B" <polleke@NOMORESPAMhnt.be> wrote in message 
news:4b055f3e$0$2853$ba620e4c@news.skynet.be:

> Stephen Quinn wrote:
>
> > }aul
> >
>
> >>> The field doesn't have to be in the index expression (of the chld)
>
> >> of the master, you mean... ?
>
> > No - the child
> >
> > Matter of fact it doesn't have to be in the expression of any index
> > for the relationship to work.
> > The only requirementa are that it's in both DBFs and the child has an
> > index
> > CYA
> > Steve
>
>
> Are you sure Steve? I thought that the underlying mecanism was a seek. So
> the eacht time the master moves a record - a seek is performed in the child
> . I don't see how you can seek on a common field when it is not incuded in
> the index expression of the child.
>
> See VO help for Setrelation::
> As always, the child work area should have a controlling index that matches
> the expression."
>
>
> --
> Paul

0
Geoff
11/19/2009 9:29:39 PM
Paul

Yeah - I'm wrong again, no idea what I'm typing these days<g>
    - glad I've given programming away, seems like it's time for me to 
retire from the ng.

Sorry for the misinformation.

CYA
Steve 


0
Stephen
11/19/2009 10:35:33 PM
No I don't and why are you repeating what I said?
Read my reply at the bottom of the thread.

Paul

Geoff Schaller wrote:
> You misunderstand.
>
> The index is in the child because this is where the seek occurs. You
> are confirming this.
>
> But the fields and the name does not have to be identical and the
> parent does not have to be indexed or even in the same order as the
> child. The only requirements are that the data type of the linked
> parent field and value has to match the index expression in the
> child. The child seek is triggered by a record movement in the parent.
>
> Don't forget you can relate on more than one field.
>
> Geoff
>
>
>
> "Paul D B" <polleke@NOMORESPAMhnt.be> wrote in message
> news:4b055f3e$0$2853$ba620e4c@news.skynet.be:
>
>> Stephen Quinn wrote:
>>
>>> }aul
>>>
>>
>>>>> The field doesn't have to be in the index expression (of the chld)
>>
>>>> of the master, you mean... ?
>>
>>> No - the child
>>>
>>> Matter of fact it doesn't have to be in the expression of any index
>>> for the relationship to work.
>>> The only requirementa are that it's in both DBFs and the child has
>>> an index
>>> CYA
>>> Steve
>>
>>
>> Are you sure Steve? I thought that the underlying mecanism was a
>> seek. So the eacht time the master moves a record - a seek is
>> performed in the child . I don't see how you can seek on a common
>> field when it is not incuded in the index expression of the child.
>>
>> See VO help for Setrelation::
>> As always, the child work area should have a controlling index that
>> matches the expression."
>>
>>
>> --
>> Paul



-- 
Paul 


0
Paul
11/23/2009 1:37:23 PM
Stephen Quinn wrote:
> Paul
>
> Yeah - I'm wrong again, no idea what I'm typing these days<g>
>    - glad I've given programming away, seems like it's time for me to
> retire from the ng.
>
> Sorry for the misinformation.
>
> CYA
> Steve

Oh no don't retire from this NG Steve; you have very often given me/us very 
useful information.

-- 
Paul 


0
Paul
11/23/2009 1:38:17 PM
On Mon, 23 Nov 2009 14:38:17 +0100, "Paul D B"
<polleke@NOMORESPAMhnt.be> wrote:

>
>Oh no don't retire from this NG Steve; you have very often given me/us very 
>useful information.

Agreed!

Dick van Kooten
0
D
11/23/2009 4:24:15 PM
Nah Steve,

It's just all you Aussies are letting the rugby (beaten by Scotland - Who? 
[Richard - hear that?]) and the temporay ( I'm sure :), loss of the Ashes, 
get to you. I myself, humble pom, have lived with failure all my life and 
contnue to revel in it! As you should know, it is in our blood! Hardly a day 
goes by without a major goof adorning my life.
You are surely not prepared to lower yourself to my level. No! Soldier on 
Sunshine. Us Poms need You :o)

Dave Francis

"Paul D B" <polleke@NOMORESPAMhnt.be> wrote in message 
news:4b0a9049$0$2860$ba620e4c@news.skynet.be...
> Stephen Quinn wrote:
>> Paul
>>
>> Yeah - I'm wrong again, no idea what I'm typing these days<g>
>>    - glad I've given programming away, seems like it's time for me to
>> retire from the ng.
>>
>> Sorry for the misinformation.
>>
>> CYA
>> Steve
>
> Oh no don't retire from this NG Steve; you have very often given me/us 
> very useful information.
>
> -- 
> Paul
> 


0
Dave
11/23/2009 8:09:06 PM
Reply: