Hello all,
I´m having problems with a report. It have to get the max date and max document, and the min date and min document from the table tfgld018.
Also must be able to print the series if the document has a series.
I´m sending my code in order to see what I´m doing wrong.
Thank you in advance.

Hi,

I'm not sure I really understand what you intend to do, but according to your description, I suggest a few changes to your code..

function read.main.table()
{
init = true
select tfgld018.*, tfgld011.*
from tfgld018, tfgld011 |, tfgld017
where tfgld018._index1 inrange {:ttyp.f, :docn.f}
and {:ttyp.t, :docn.t}
and tfgld018.dcdt inrange :qdat.f and :qdat.t
and tfgld018.ttyp refers to tfgld011
and tfgld011.dsrn inrange :serie.f and :serie.t
selectdo
if init then
init = false
reset.vars()
endif
|why this check??? this means it will check from the second ttyp only... try without it. In the function reset.vars I would just put all the vars to 0 (zero)
| if (tfgld018.ttyp <> doctipo) then
if (tfgld018.dcdt >= fechamax) then
fechamax = tfgld018.dcdt
documax = tfgld018.docn
| change this to endif because for first run it must check both
| else
endif
if (tfgld018.dcdt <= fechamin) then
fechamin = tfgld018.dcdt
documax = tfgld018.docn
endif
| endif
| endif


| rprt_send() do this after the endselect
endselect
rprt_send()
}

function reset.vars()
{
fechamin = tfgld018.dcdt
documin = tfgld018.docn
fechamax = tfgld018.dcdt
documax = tfgld018.docn
doctipo = tfgld018.ttyp
}


Like this you will have a report mentioning the min/max values. I don't know exactly what you meant with printing the series, but then just fetch the series in report script.

Hope this helps you. For any further questions, don't hesitate to post them to the board..

Good luck!!
En

Why don't you use MAX() function for your case. Before select , write additonal select and use max function like that:
select max(tfgld018.docn):documax,
max(tfgld018.dcdt):datemax
from tfgld018
where |************* write your conditions ***************|
selectdo
endselect

Also you can try it with MIN() functions.

Good luck !

Thanks for your responses En and Mr. Suleyman.
En, just a little detail. If I change the rprt_send() after the endselect, it only send me the last record. I need the max and min tfgld018.docn and tfgld018.dcdt for each type of document. I mean, each change of tfgld018.ttyp


Mr. Suleyman If I use your code, as a result I have only the max document and date.

Any other idea?

Thank you in advance.

Try following code
declaration:
*********** mindocn,maxdocn,mindate,maxdate
function find.minmax.value()
{
select MIN(tfgld018.docn):mindocn,MAX(tfgld018.docn):maxdocn,
MIN(tfgld018.dcdt):mindate,MAX(tfgld018.dcdt):maxdate
from tfgld018, tfgld011
where tfgld018._index1 inrange {:ttyp.f, :docn.f}
and {:ttyp.t, :docn.t}
and tfgld018.dcdt inrange :qdat.f and :qdat.t
and tfgld018.ttyp refers to tfgld011
and tfgld011.dsrn inrange :serie.f and :serie.t
selectdo
}

function read.main.table()
{
***** temp1 | Define it type of tfgld018.docn
***** temp2 | define it type of tfgld018.dcdt
doctipo = "Z_Z" | Meanless code to compare change of ttyp
select tfgld018.*, tfgld011.*
from tfgld018, tfgld011 |, tfgld017
where tfgld018._index1 inrange {:ttyp.f, :docn.f}
and {:ttyp.t, :docn.t}
and tfgld018.dcdt inrange :qdat.f and :qdat.t
and tfgld018.ttyp refers to tfgld011
and tfgld011.dsrn inrange :serie.f and :serie.t
order by tfgld018.ttyp
selectdo
if doctipo <> tfgld0018.ttyp then
temp1 = tfgld018.docn
temp2 = tfgld018.dcdt
find.minmax.value()
tfgld018.docn = temp1
tfgld018.dcdt = temp2
|temp1,temp2 keeps pointer of tfgld018 keys TRUE.
else
doctipi = tfgld018.ttyp
endif
|You got min,max value for each ttyp
|don't forget definitions of mindocn,maxdocn,mindate,maxdate
rprt_send()
endselect

Mig28, thanks for adding this very important detail, that the min and max values should be printed PER TRANSACTION TYPE, i.e. FOR EACH TTYP. This of course changes the situation.

Suleyman, sorry but with all due respect your code is incorrect and will not deliver the desired result; have a look at it again...

Below is the code I suggest. Start from tfgld011 (transaction types) and for each selected ttyp, you make a selection on tfgld018 and print the min/max values...

function read.main.table()
{
select tfgld011.*
from tfgld011
where tfgld011._index1 inrange {:ttyp.f} and {:ttyp.t}
and tfgld011.dsrn inrange :serie.f and :serie.t
order by tfgld011._index1
selectdo

reset.vars()
select tfgld018.*
from tfgld018
where tfgld018._index1 inrange {:tfgld011.ttyp, :docn.f}
and {:tfgld011.ttyp, :docn.t}
and tfgld018.dcdt inrange :qdat.f and :qdat.t
selectdo
if (tfgld018.dcdt >= fechamax) then
fechamax = tfgld018.dcdt
documax = tfgld018.docn
endif
if (tfgld018.dcdt <= fechamin) then
fechamin = tfgld018.dcdt
documax = tfgld018.docn
endif
endselect
rprt_send()
endselect
}

function reset.vars()
{
fechamin = 0
documin = 0
fechamax = 0
documax = 0
}


Let me know if it works fine...

What is wrong enfrom??? I think it will works well .

I didn't look much into your script; to start with I saw hree problems:
1) your subquery selects tfgld018 again, which is dangerous
2) your subquery selects the entire range again of ttyps
3) based on the requirements it seems that you need min/max date, and the document related to those records. What you do, is you fetch the min/max document also, which means basically the document with the smallest number and the document with the highest number. I don't think that's the idea.

As I said I didn't go through it any more after I saw the above mentioned, so can't point you out more errors...

I would let the report generator do the work you. Here is the way to do this:

Let us assume that you have generated your new session as usual and selected the fields you require on the report layout. The PROGRAM SCRIPT would be the basic select from tfgld018 - merely constrained by the from and to fields you are interested in, but no other computation. Do not add any other logic here.

Then, in the report layout editor, first make the field Document Type as a sort field.
Then, in the Layouts, create a after.field layout for Document Type and copy all the fields you need: I believe these would be as follows:

Transaction Type
Document Date 1 (tfgld018.dcdt) (for minimum)
Document Date 2 (tfgld018.dcdt) (repeat for maximum)
Document Number 1 (tfgld018.docn) (for minimum)
Document Number 2 (tfgld018.docn) (repeat the field for maximum)

Then, in the Maintain Report Field details for Document Date 1, change Aggregate Funtion to "minimum" and in the field details for Document Date 2, change Aggregate Funtion to "maximum". Repeat for Document Number 1 and Document Number 2.

Compile and run!

All of this should not take you more than 10 minute. In fact, it took me more time to think about and write this! It pays to be very lazy when it comes to programming in BAAN 4GL!

I don't really understand why you would waste 10 mins of your precious time to think of an alternative solution; my suggested code took me approx. 2.5 mins incl. thinking and typing....

Anyway, regarding to your suggestion: is your suggestion to do it without a detail layout? Then I don't think the min and max aggregate functions will deliver much; don't they present the min/max PRINTED values via detail layouts....???

Yes, indeed. I forgot, I should have mentioned - you do not need the detail layout (unless you want it).

Enfrom my code is correct only he would calculate it for each type of ttyp. I only changed place of rprt_send(). Anyway it works well. There is no need to compare something. Baanboard is open for all suggestions !

Good LUck !!!

no comment suleyman... just try out your code...

I undeleted shah_bs threads just to keep the flow of the thread. Once posted it usually is better to leave the post than delete it. And yes mr_suleyman the board is open to all suggestions and we do appreciate any input you provide. I also think your code would work, but maybe needs a little tweaking. As en mentioned the subroutine would find all types again - so maybe just passing parameters will help find what the original poster needed. I am curious as to what the final solution is?

Hello Shah_bs,
Actually using the layout was my first approach for the problem. The detail is, when the report sends, always put 0(zero) in the fields minimum for date and docn. The other records are ok. But this first record is wrong. So, that´s why I´m try with code to get this.

Thank you anyway.

I will try the code from en and mr_suleyman and I will inform to the board any change.

Regards.

Hello Mr_Suleyman and En, thank you very much for all.

I tried with the code sugested by en and I find that the output allways put zero in docn and dcdt for minumun values. And also It puts every record from the select.

Also test the code from Mr_Suleyman and I´m still getting ony the max docn and doct from tfgld018 not fetch by document. You are saying that you change de possition of rprt_send(). Where do you put it?

Thank you all.

¿any other idea?
Regards.

Init fechamin value to something real big(like today+10000). That way the first document sets the first max and min value. Otherwise nothing will be less than 0.

Hi

Try this

select max(tfgld018.docn):maxdocn,
max(tfgld018.dcdt):maxdcdt,
min(tfgld018.docn):mindocn
min(tfgld018.dcdt):mindcdt
from tfgld018
where .....
group by tfgld018.docn,
tfgld018.dcdt

Hi,

Sorry for late response; just noticed this thread is still unresolved. C'mon, it ain't that dificult to accomplish your task..!!

Of course you are right about the minimum values being 0, my mistake in the rush.. The check is tfgld018.dcdt <= fechamin after fechamin is set to 0, so of course the condition will never be reached.. Either set fechamin to fmax (high value), or use an init variable to set all min and max values to first records' values and continue from there.

About your second remark that it "puts all the records from the select": I don't understand what you mean. If I understood your requirements well, and if you put the rprt_send() in the place I suggested, you shall get a min/max value for each ttyp in the selected range... Is this not happening..?

Hi En,
I change the value of fechamin to other value and now I´m getting in the first the values that I need. But after this recrod, it comes all the other records with values of reset.vars() function. And yes, I put the rprt_send() in the place were you suggest.

I think I´m missing something...

Thank for your help.

Hi Rita,
I will try you code and I will notify you about the result.
Thank you!

Do you have a report script? Hope you don't use things like layout.again() or so? Is there anything else programmes in your program script? Maybe you want to post your entire program script and screenshots of your form and report, plus screenshot of the actual output; like this it will be easier to understand what goes wrong.

Hi mig28mx,

I have used it in one of my scripts, It should work, but hope its helps you acheiving all that you want out of the code.

Rita, I don't think this is what mig28mx looks for; your query will give smallest/highest document number, whereas I think mig28mx looks for the document number corresponding to the transaction on the earliest and latest date. See the difference...?

Hello En, Rita.
Thank you very much for all your help. I´m glad to inform you that the main problem has been solved.

Actually I´m using the code proposed by Rita, and works like charm!
Obviously, the help provided by En help me a lot also.

Thank you for all your support and to the board for made this space.
Regards.

I have not looked in detail at the table definitions. But if you do not want the intervening the field(s) from the sql, then then use the sql with
order by xxxx desc
as set with 1 rows.

That will provide the max. value, given xxxx

Using order by together with "with set with 1 rows" will do what you want,