why All records are showing in the report? 1

[Mandy_crw]

hi everyone... i design a report using wizard to show records from two tables of the selected idnum in the combobox... but why is everything in my database is being shown in the form? Please help.... thanks....

PROCEDURE CmdPrint.click()

LOCAL lclname as Character

SELECT tsulat

CritId = this.Parent.text1.value

SEEK CritId

IF FOUND()

SET DECIMALS TO 2

dyus = 0
paytuit = 0
buks = 0
mctr = 0
devisor = 0

TRANSSF = 0
TRANSBA = 0
TRANSBO = 0
TRANSUN = 0
TRANSOT = 0

SELECT trans

SCAN FOR CritId = trans.idnum

iden = trans.transcode

DO case

CASE iden = "SF"

TRANSSF = TRANSSF + TRANS.transamt

CASE IDEN = "BA"

TRANSBA = TRANSBA + TRANS.transamt

CASE IDEN = "BO"

TRANSBO = TRANSBO + TRANS.transamt

CASE IDEN = "UN"
TRANSUN = TRANSUN + TRANS.transamt

CASE iden = "OT"
TRANSOT = TRANSOT + TRANS.transamt

ENDCASE

ENDSCAN

SELECT tsulat

*Computations ----------------------------------------------

REPLACE...
replace....


SELECT idnum, ALLTRIM(sname) + ", " + ALLTRIM(fname) FROM tsulat WHERE idnum = CritId ORDER BY sname, fname INTO CURSOR StudRep

select StudRep

REPORT FORM MyReport TO PRINTER PROMPT PREVIEW
ENDIF

ENDPROC

 

[GriffMG]

I suspect your report has 'remembered' the data environment from when you created it in the wizard.

Edit it and look at the data environment

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Chris Miller]

All records are the normal scope. If you want to make use ojust one or a few records you have to do two things:
1. Don't have a report data environment, and more important no private datasession
2. Select the workarea to print from and use a scope clause, which can be NEXT 1 for 1 record or a FOR clause with condition.

You certainly don't set a NEXT 1 or FOR id=x or any other limiting clause and what Griff said could also be the reason, your report has aa dataenvironment and gets its data from there.

Chriss

 

[Mandy_crw]

Thanks Griff and Chris...but i dont understand what you meant with Data environment, private datasession and Next and for... May you give an example to base from? thanks...

 

[Chris Miller]

Okay, Mandy.

It's a bit complicated, but let#s start by saying to print ALL records is normal for a report.

If you just want to print current record of a workarea, you can start the REPORT with:

REPORT FORM MyReport [highlight #FCE94F]NEXT 1[/highlight] TO PRINTER PROMPT PREVIEW

NEXT 1 is one example of a Scope. That's what the IntelliSense tip shows at this point:

The square brackets mean it's an optional command clause, you can also leave it. But then the default scope is ALL.
There are others like REST (all records starting at the current one) and NEXT 5 would print 5 records. So NEXT N means N records starting with the current record and NEXT 1, therefore, is the current record.



What Griff and I said about the data environment means only will be understandable once you know what a data environment is. Forms and Reports always have one. And it's either in a current or private datasession. So that's another term you might not yet understand. I suggest you read the help about the two terms datasession and data environment. Because it leads too far if I'd explain all this.

In short it's where data is opened and if that's private it means only the report would see it. As you SELECT a workarea directly before REPORT FORM, you likely want to print from this, which can only work, if the report doesn't have a private dataenvironment, which would create a new datasession and switch to it. And within that session again the default scope of which records to print will be ALL.

I don't expect you to understand this second part, but since your report printed, it's likely adding NEXT 1 will be what you want and that's enough for now.

Chriss

PS: the Intellisense shows how complex REPORT FORM is, it also helps to read the help topic to get what all these command clauses are good for.

 

[TamarGranor]

Have you checked whether the cursor you create, StudRep, contains only the records you want?

Tamar

 

[Mandy_crw]

Thank you Chris and Tamar.... I tried using NEXT 1 with GO RECORD and It showed the first record in my MAIN table it did not show everything (the same idnum where record pointer is in my Main table) occurrences from my TRANSACTION table. It only showed one transaction.... Am I understanding it correctly Chris? Please teach me... Thanks...

​

​

​

GO RECORD reknamber

REPORT FORM MyReport NEXT 1 TO PRINTER PROMPT PREVIEW

But when i use this it showed all transactions but it also showed everybody in my Main table....
REPORT FORM MyReport FOR tsulat.idnum=CritId WHILE Trans.idnum=CritId TO PRINTER PROMPT PREVIEW

Thank you....

 

[Mandy_crw]

Thanks Tamar, I have removed the Cursor as the source of the Report....

PROCEDURE CmdPrint.click()

LOCAL lclname as Character

SELECT tsulat

CritId = this.Parent.text1.value

SEEK CritId

IF FOUND()

SET DECIMALS TO 2

dyus = 0
paytuit = 0
buks = 0
mctr = 0
devisor = 0

TRANSSF = 0
TRANSBA = 0
TRANSBO = 0
TRANSUN = 0
TRANSOT = 0

SELECT trans

SCAN FOR CritId = trans.idnum

iden = trans.transcode

DO case

CASE iden = "SF"

TRANSSF = TRANSSF + TRANS.transamt

CASE IDEN = "BA"

TRANSBA = TRANSBA + TRANS.transamt

CASE IDEN = "BO"

TRANSBO = TRANSBO + TRANS.transamt

CASE IDEN = "UN"
TRANSUN = TRANSUN + TRANS.transamt

CASE iden = "OT"
TRANSOT = TRANSOT + TRANS.transamt

ENDCASE

ENDSCAN

SELECT tsulat

*Computations ----------------------------------------------

REPLACE...
replace....

GO RECORD reknamber

REPORT FORM MyReport NEXT 1 TO PRINTER PROMPT PREVIEW (This one shows only the first record of the main Table...)

REPORT FORM MyReport FOR tsulat.idnum=CritId WHILE Trans.idnum=CritId TO PRINTER PROMPT PREVIEW

REPORT FORM MyReport TO PRINTER PROMPT PREVIEW (This one works showing all transactions but it does show everything in my Main Table..)

ENDIF

ENDPROC

 

[TamarGranor]

If that's working for you, fine.

On the whole, my practice is to put all the data I want to report on into a cursor and run the report against the cursor. Occasionally, what I'm doing is so complex that I have to SET RELATION between multiple cursors, but in probably 98% of cases, I can get all the data into a cursor and report against that, which is the easiest way.

Tamar

 

[Chris Miller]

If you have relations and when there are multiple details in the related table, NEXT 1 will only print the first main and detail record, that's true.

I guess the FOR tsulat.idnum=CritId works?

If the GO RECORD reknamber positions on the correct top main record, the scope clause that will print that and all further with tsulat.idnum=CritId will be

REPORT FORM MyReport REST WHILE tsulat.idnum=CritId TO PRINTER PROMPT PREVIEW

FOR and WHILE together make no sense.

You can also make a compromise of yout relations and Tamars way of a new cursor report. Create a cursor of the main records you want to print, for example:

Select * From tsulat WHERE idnum=CritId Into Cursor reportmaindata

Then you can make your relations from this cursor to the detail table or tables using it as the main report cursor without a scope clause, ie ALL records of that cursor.

Chriss

 

[Mandy_crw]

Thanks Tamar and Chris.... ive tried this code, it showed what was selected, (CritId) but in the details it only shows one and its repeated even if in the cursor there were 3 transactions... Please help....

Select * From tsulat WHERE idnum=CritId ORDER BY idnum INTO CURSOR REPORTMAINDATA
INDEX ON IDNUM TO REPORTMAINDATA

SELECT * FROM trans WHERE idnum=CritId ORDER BY idnum INTO CURSOR REPORTDETAILS
INDEX ON IDNUM TO REPORTDETAILS

SET RELATION TO idnum INTO reportMAINDATA


REPORT FORM MyReport FOR reportmaindata.idnum=CritId WHILE reportdetails.idnum=CritId TO PRINTER PROMPT PREVIEW

 

[Chris Miller]

The relation has to go FROM main to details, the 1:n relation starts at the 1 nd ends in the n.

And then also try

Select REPORTMAINDATA
SET SKIP TO REPORTDETAILS

Chriss

 

[Mandy_crw]

Ok Chris... i’ll try this... thanks... may i just ask what is “set skip to ReportDetails” does? Thanks again...

 

[Chris Miller]

Do you now SKIP?

The SKIP command skips in one workarea, normally.

SET SKIP TO means skipping in the main workarea (the one that drives a report, for example) skips there, unless there are still more detail records in a 1:n related detail table. So the actual record pointer skip will be done where it's needed automatically.

As the report does not switch to other workareas for skipping to the next record, set skip to does that.

Chriss

 

[TamarGranor]

I would use a single query rather than two:

[pre]Select * ;
From tsulat ;
JOIN trans ;
ON tsulat.idnum = trans.idnum ;
WHERE tsulat.idnum=CritId
ORDER BY tsulat.idnum ;
INTO CURSOR REPORTMAINDATA
[/pre]

If you care what order the children are in for this record, add that field of trans to the ORDER BY field.

Also, I wouldn't actually include all fields from both tables, only those needed for the report.

Tamar

 

[Chris Miller]

The point of querying was only to limit the printed data, which failed by FOR clause, for some reason. Single cursor driven reports are usually easier, I agrre. But once you already have a report that relies on SET RELATION, it's easier to have the data separate, too.

I just thought of querying the main record and still use the relation to the detail DBF. You might not even need any query to a cursor, just

USE maindata
...preparations, LOCATE/SEEK main start record
SELECT maindata
SET RELATION from maindata to detaildata
SET SKIP TO detaildata
REPORT FORM yourreport.frx REST WHILE maindata.idnum = CritId PREVIEW

Chriss

 

[Mandy_crw]

Thanks Tamar and Chris... I've tried all of those, but i still didnt have the intended output... Im still figuring it out till this time... Thanks so much...

 

[Chris Miller]

From what I see you got main and detail data wrong.

In terms of an order the single main order record is the master/parent and order items are details. And while printing the orderitems is the main content of an order or bill, the data definition of the report will need
a) the order as the main report driving table, though only 1 record of it is printed.
b) a relation from order to orderdetails, not from orderdetails to order
c) a set skip to orderdetails
d) not a NEXT 1 but a REST WHILE scope in order. Because of how SET SKIP TO works. To get all order items of the order you skip in order and teh SET SKIP mechanism redirects the skips into orderitems unteil there is no orderitem for the same order id, which means the final skip is executed in order, where the new order id would end the scope of a REST WHILE orderid=specific orderid,

So even though the usual relation is from orderdetails to order via the orderdetails.orderid matching the order.id, so each child record points to its parent, the report relation has to go from the 1 to the n side of the 1:n relation.

Chriss

 

[Mandy_crw]

Tamar and Chris and everyone... It worked!!!! It worked when I have designed a new form manually, I did not use the wizard in the creation.... and like magic it worked!!!! I don't know what happen.... but thank you so much Tamar and Chris...

SELECT * WHERE idnum=CritId FROM trans ORDER BY idnum INTO CURSOR REPORTDETAILS
INDEX ON IDNUM TO REPORTDETAILS

Select * WHERE idnum=CritId From tsulat ORDER BY idnum INTO CURSOR REPORTMAINDATA
INDEX ON IDNUM TO REPORTMAINDATA

SET RELATION TO idnum INTO reportDETAILS

SET SKIP TO REPORTDETAILS

REPORT FORM Report1 FOR ReportMainData.idnum=CritId WHILE ReportDetails.IDNUM=CritId TO PRINTER PROMPT PREVIEW