Hi,

I am trying to add additional detail to an existing report. When an installment invoice is printed it gives no details, just a total of what is owed/paid. I would like to add the details of all sales order lines for the order. However, my script is not behaving as I thought it would. Here's the code


detail.1:
before.layout:

select tdsls041.*
from tdsls041
where tdsls041._index1 = {:tdsls045.orno, :temp.pono}
selectdo
selectempty
lattr.print = false
endselect

after.layout:

if temp.pono < 701 then
temp.pono = temp.pono + 1
layout.again()
endif


The problem is this prints nothing until I remove lattr.print from selectempty. Then it prints 700 lines changing the values as it pulls lines from tdsls041. Any explanations/fixes appreciated.

Thanks,
Ev

It looks like it does not find temp.pono=1 so it sets lattr.print to false and then never resets it - try this.

Mark


detail.1:
before.layout:
select tdsls041.*
from tdsls041
where tdsls041._index1 = {:tdsls045.orno, :temp.pono}
selectdo
lattr.print = true
selectempty
lattr.print = false
endselect

after.layout:
if temp.pono < 701 then
temp.pono = temp.pono + 1
layout.again()
endif

Before you hit the detail.1 layout, did you set the value of temp.pono to the first valid record in tdsls041 for that order number? If you just set it to something arbitrary (like 0), then you would hit selectempty and leave. As it stands, unless you number your lines by 1 (rather than by, for example, 5 or 10), you will not get all of you lines. Also, even if you numbered by one, if a line was deleted in the sequence, that would cause you to miss the lines after that.

It might work better to try something like this:


<layout hit before detail.1>:
before.layout:
temp.pono = 1

detail.1:
before.layout:
select tdsls041.*
from tdsls041
where tdsls041._index1 inrange {:tdsls045.orno, :temp.pono}
and {:tdsls045.orno}
as set with 1 rows
selectempty
lattr.print = false
endselect

after.layout:
if temp.pono <=700 then
temp.pono = tdsls041.pono + 1
layout.again()
endif


This should handle any non-consequetive position numbers.

Ed,

I looked at this last night and again this morning. Could you do something like this to keep it from cycling through 700 times?



<layout hit before detail.1>:
before.layout:
temp.pono = 1

detail.1:
before.layout:
select tdsls041.*
from tdsls041
where tdsls041._index1 inrange {:tdsls045.orno, :temp.pono}
and {:tdsls045.orno}
as set with 1 rows
selectdo
lattr.print = true
selectempty
lattr.print = false
endselect

after.layout:
select tdsls041.*
from tdsls041
where tdsls041._index1 = {:tdsls045.orno}
and tdsls041.pono > :temp.pono}
as set with 1 rows
selectdo
temp.pono = tdsls041.pono
selectempty
temp.pono = 0
endselect
if temp.pono > 0 then
layout.again()
endif


It looks like it would work, but it seems like I am missing something.

Mark

Thanks evesely. Works great.

Mark,

Your last solution should work. However, the select in the after.layout seems like extra effort. The inrange in the before.layout should get the next real record as long as we increment the temp.pono variable in the after.layout.

-Ed

I was thinking that the second code would stop the layout.again after the last position. So if each order had 700 lines then my code would have extra overhead, but if each order only had 50 positions then my code would stop the extra 650 queries the original code was executing. Without being familiar with the sessions and data I was really just guessing.

Mark

I watched the thread progress with interest. Here is something I use in one of my scripts.<layout hit before detail.1>:
before.layout:
more.records = true
temp.pono = 0

detail.1:
before.layout:
select tdsls041.*
from tdsls041
where tdsls041._index1 = {:tdsls045.orno}
and tdsls041.pono > :temp.pono
|assuming that tdsls041.pono does not take a value of 0
as set with 1 rows
selectdo
lattr.print = true
temp.pono = tdsls041.pono
selectempty
lattr.print = false
more.records = false
endselect

after.layout:
|No more firing the select 700 times!!!
|Also no more confusion as to what happens
|if pono has a value of 999!
if more.records then
layout.again()
endif

Originally posted by ~Vamsi


and tdsls041.pono > :temp.pono


This is very intriguing... I was not aware this could be used.

However, in evesely's solution the select does not continue on needless. I've tested this and found it stops immediately after finding the last valid position number.

Ev