code to create report with variable number of fields 2

[harrymossman]

I have a "queries for dummies" form that allows Paradox-illiterate users to build queries by entering a key word, pushing buttons, and clicking boxes to limit what fields appear in the result table. This all works well.

I can't create a report for every possible set of selections. That would be hundreds of reports.

I'd like to do something like the quick report button, only with the report style specified. (If the user clicks all of the fields, the result is too wide to display as a table. So a single or multi-record layout would be best.)

A form with the table on it would be ok too. Maybe better. It would automatically be in a single-record format.

Can someone push me in the right direction? I have used create() and run() to make a form. Not sure how to get the table-frame (and maybe other objects like a close button) on it. Or is there another approach?

Harry

 

[footpad]

Harry,

You may be interested in

http://techtricks.com/paradox/quickrpt.php3,

an article on my site that shows how to implement "quick report" functionality from within forms. (It's also got a pretty cool trick for rebinding table frames on the fly.)

I'm biased, of course, but I think you might find it helpful.

Hope you do...

-- Lance

 

[Kliot]

Lance,

Incredible site, I can't wait to spend an afternoon going over it. You are truly a great resource for the Paradox community.

Perrin

 

[footpad]

Perrin,

Thanks; I'm glad it's useful. I don't get to work on it very often, but I try to add an article every couple of weeks. (Well, that's the goal at any rate.)

I've got a few good ones under construction at the moment. Hopefully, I'll be able to get them online next week.

-- Lance

 

[harrymossman]

Thanks Lance. This is an excellent article. I expect to use the technique a lot for other forms.

However, it still doesn't seem to be the answer for my current problem. If the user chooses very many fields, the resulting table will be too large for a tabular format, even landscape. I'm not seeing any way to set the report to single- or multi-record.

 

[footpad]

harrymossman,

I'll admit that the article only outlines a basic technique. In your case, you'll want something more complex.

Here's a code sample that presumes your working directory is set to the one containing the sample Paradox tables:

method pushButton(var eventInfo Event)
var
   rpt       Report
   uiNew,
   uiRpt     UIObject
   tc        TCursor
   liHeight  LongInt
endVar

   tc.open( ":work:customer" )
   liHeight = tc.nFields() * 330
   rpt.create()
   rpt.dmAddTable( ":work:customer" )
   uiRpt.attach( rpt.#Record_Band )
   uiNew.create( RecordTool, 720, 0, 9360, liHeight, uiRpt )
   uiNew.attach( uiRpt.#Multirecord7 )
   uiNew.XSeparation = 0
   uiNew.YSeparation = 0
   uiNew.TableName = "customer"
   rpt.run()
   rpt.DesignModified = FALSE

endMethod

I'll admit that the result is crude, but a bit of experimentation should yield more professional results.

I should note that Runtime applications may not be able to use this code.

Hope this helps...

-- Lance

 

[harrymossman]

Thanks! It works great!

 

[harrymossman]

The report works well except for a problem caused by the way our database was designed eons ago. Our logging number has two parts. The second part is Number (bad choice) with the field type of number. So when I do reports, I always have to get rid of the decimals. (Yes, I know that I really should change the database. Don't even go there.)

The ideal would be to have the whole log number appear in the report in the format 03-X-1234. I have managed to add a field in this format to the table. But I can't figure out how to remove the Year (also a bad choice) and Number fields.

Any suggestions would be appreciated. I'm still stumbling around with complicated techniques like tcursors.

Harry

method pushButton(var eventInfo Event)

var

   rptTblName,
   sourceTblName,
   structTblName	string

   sourceTbl,
   rptTbl,
   structTbl		table

   rptDummies		report

   uiNew,
   uiRpt     		UIObject

   tcSource,
   tcStruct,
   tcRpt			tCursor

   liHeight  		longInt

   dynNewStruc      dynArray[] Anytype

endVar

sourceTblName = ":priv:qLog.db"

sourceTbl.attach(sourceTblName)

rptTblName = ":priv:qLog_rpt.db"

rptTbl.attach(rptTblName)

structTblName = ":priv:field_struct.db"

structTbl.attach(structTblName)

;Delete old tables
if isTable(rptTblName) then
	if not rptTblName.delete() then
   	msgInfo("error","can't delete old rptTbl")
	endIf
endIf

if isTable(structTblName) then
	if not structTbl.delete() then
   	msgInfo("error","can't delete old structTbl")
   endIf
endIf

;Making a working table - rptTbl
tcSource.open(sourceTbl)
tcSource.copy(rptTbl)
tcSource.close()

;Restructure rptTbl with a new field LogNum
rptTbl.attach(rptTblName)
rptTbl.enumFieldStruct(structTblName)
tcStruct.open(structTblName)
tcStruct.edit()
tcStruct.home()
tcStruct.insertBeforeRecord()
tcStruct."Field Name" = "LogNum" ;field name
tcStruct."Type" = "ALPHA" ;field type
tcStruct."Size" = 20
tcStruct.endEdit()
tcStruct.close()
dynNewStruc["FIELDSTRUCT"] = ":priv:field_struct.db"
if not rptTbl.restructure(dynNewStruc) then
msgstop("Error", "unable to restructure")
return
endIf

;Combine two other fields to fill LogNum
tcRpt.open(rptTblName)
tcRpt.edit()
scan tcRpt :
tcRpt."LogNum" = tcRpt."Year" + "-" + format("w4.0,al", tcRpt."Number" )
tcRpt.unlockRecord()
endScan
tcRpt.endEdit()
tcRpt.close()


tcStruct.open(structTblName)
tcStruct.edit()
if tcStruct.locate("Field Name", "Year") then
	if not tcStruct.deleteRecord() then
	msgInfo("error","Year")
	endIf
endIf

if tcStruct.locate("Field Name", "Number") then
	if not tcStruct.deleteRecord() then
	msgInfo("error","Number")
   endIf
endIf

tcStruct.endEdit()
tcStruct.close()
dynNewStruc["FIELDSTRUCT"] = ":priv:field_struct.db"

;Ok to this point

tcStruct.open(structTblName)
liHeight=tcStruct.nFields()*330
rptDummies.create()
rptDummies.dmAddTable(":priv:qLog_rpt.db")
uiRpt.attach(rptDummies.#Record_Band)
uiNew.create(RecordTool,720,0,9360,liHeight,uiRpt)
uiNew.attach(uiRpt.#Multirecord7)
uiNew.xSeparation=0
uiNew.ySeparation=0
uiNew.frame.style=noFrame
uiNew.nCols = 2
uiNew.TableName="qLog_rpt"
rptDummies.run()
rptDummies.DesignModified=FALSE
tcStruct.close()

endMethod

 

[harrymossman]

Also, how can I hide the report until it's finished. Right now, it comes up in design mode and sits there for 10 seconds or so. And I can't even seem to get a wait cursor to show while it's sitting there.

Harry

 

[harrymossman]

I fixed the formating problem another way. Still can't figure out how to hide the design form. Otherwise, the quick report works fine. Here's my code:

method pushButton(var eventInfo Event)

var

   rptTblName,
   structTblName	string

   rptTbl,
   structTbl		table

   rptDummies		report

   uiNew,
   uiRpt,
   pageTitle  		UIObject

   tcStruct,
   tcRpt				tCursor

   liHeight,
   records  		longInt

endVar

setMouseShape(mouseWait)

rptTblName = ":priv:qLog.db"

structTblName = ":priv:field_struct.db"

rptTbl.attach(rptTblName)

structTbl.attach(structTblName)

rptTbl.enumFieldStruct(structTblName)

tcStruct.open(structTblName)

records = tcStruct.nRecords()

if comments.value = "True" then

		liHeight = 10500

   else


switch

case records <4: 	liHeight = 3000

case records <6:	liHeight = 4000

case records <10:	liHeight = 6000

otherwise:			liHeight = 10500

endSwitch

endIf


rptDummies.create()

rptDummies.dmAddTable(&quot;:priv:qLog.db&quot;)

uiRpt.attach(rptDummies.#Record_Band)

uiNew.create(RecordTool,720,0,9360,liHeight,uiRpt)

uiNew.attach(uiRpt.#Multirecord7)

uiNew.xSeparation=50
uiNew.ySeparation=50
uiNew.frame.style=noFrame
uiNew.nCols = 2

uiNew.TableName=&quot;qLog&quot;

pageTitle.create(textTool, 900,0,10000,360,uiRpt)
pageTitle.name = &quot;newTitleText&quot;
pageTitle.text = &quot;Log Report for &quot; + getNetUserName() +
&quot;, &quot; + string(today()) + &quot;  &quot; + &quot;Key Word: &quot; + format(&quot;cu&quot;,keyWd.value) + &quot;\n&quot;

pageTitle.Visible = True

rptDummies.run()

setMouseShape(mouseWait) ;I actually have this several places, none of which seem to work.

rptDummies.DesignModified=FALSE
tcStruct.close()

endMethod

 

[Kliot]

harrymossman,

How about using setPosition(x,y,w,h)after the create() to move the report off of the screen. Set x and Y to a high number moving the report off the visible screen. It's not great but I've done it in the past to modify a report using objpal without the user seeing the changes being made.

Perrin