Report Form Behavior problem 1

[Scott24x7]

Hi All,
A few months ago I created a report for one of my tables (it's expense report, some may remember when I worked through this). It was all working fine, and suddenly I started to get a message when trying to run it saying "expenseid_a is not a field" (paraphrasing, I don't remember the exact error now, and I have changed it, which I'll get to in a minute).

My understanding of the report form is, wherever it is called from (whatever procedure) any fields/variable that exist in that scope are visible to the report.
I was doing a query to a cursor, and that data was driving the report. But then it stopped finding one of the fields.
So I decided to switch it to a table. (This also has the advantage of being able to test it easier at the report form time).

The problem with query to table is, VFP treats it as a freetable and only allows 10 character field names. So I did the query, found all the field names, mapped them back properly to the report form, and when I run it now, I am past the "expenseid_a" issue, and the table populates correctly, BUT my detail band now just shows the same (first) record over and over for the number of records in the table. I don't recall making a change to the report other than the field mappings, and data grouping (It used to be EXPENSEID_A, now I have it set to expenseprint.expenseid2 which is the "new" field mapping of the old ExpenseID_A).

I've been tearing my hair out, and I just can't seem to figure it out. A second report runs right after it against the same data set which prints receipts, and that one works fine...
The code to launch the report is here:

SET SAFETY OFF
SELECT * FROM EXPENSE;
   LEFT JOIN EXPENSEDETAIL ON EXPENSEDETAIL.EXPENSEID = EXPENSE.EXPENSEID;
   WHERE EXPENSE.EXPENSEID = EXPENSEGRID.EXPENSEID INTO TABLE EXPENSEPRINT ORDER BY EXPENSEDETAILDATE READWRITE
SET SAFETY ON
*
SELECT EXPENSEPRINT
GO TOP
*
M.lnCorporateTotal = ThisForm.pageframebase1.pageBASE1.lblCorporateTotal.Caption
IF RIGHT(ALLTRIM(M.lnCorporateTotal),2) = "¥0"
	M.lnCorporateTotal = ""
ELSE
	M.lnCorporateTotal = RIGHT(lnCorporateTotal,LEN(lnCorporateTotal)-AT(":",lnCorporateTotal))
ENDIF
*
M.lnTransportTotal = ThisForm.pageframebase1.pAGEBASE1.lbltransportationTotal.Caption
IF RIGHT(ALLTRIM(M.lnTransportTotal),2) = "¥0"
	M.lnTransportTotal = ""
ELSE
	M.lnTransportTotal = RIGHT(lnTransportTotal,LEN(lnTransportTotal)-AT(":",lnTransportTotal))
ENDIF
*
M.lnTNETotal = ThisForm.Pageframebase1.PAGEBASE1.lblTNETotal.Caption
IF RIGHT(ALLTRIM(M.lnTNETotal),2) = "¥0"
	M.lnTNETotal = ""
ELSE
	M.lnTNETotal = RIGHT(lnTNETotal,LEN(lnTNETotal)-AT(":",lnTNETotal))
ENDIF
*
M.lnTradeToolsTotal = ThisForm.Pageframebase1.PAGEBASE1.lblTradeToolsTotal.Caption
IF RIGHT(ALLTRIM(M.lnTradeToolsTotal),2) = "¥0"
	M.lnTradeToolsTotal = ""
ELSE
	M.lnTradeToolsTotal = RIGHT(lnTradeToolsTotal,LEN(lnTradeToolsTotal)-AT(":",lnTradeToolsTotal))
ENDIF
*
M.lnOtherTotal = ThisForm.Pageframebase1.PAGEBASE1.lblOtherTotal.Caption
IF RIGHT(ALLTRIM(M.lnOtherTotal),1) = ":"
	M.lnOtherTotal = ""
ELSE
	M.lnOtherTotal =RIGHT(lnOtherTotal,LEN(lnOtherTotal)-AT(":",lnOtherTotal))
ENDIF
*
M.lnExpenseTotal = ThisForm.pageframebase1.pageBASE1.lblExpenseTotal.Caption
M.lnExpenseTotal = RIGHT(lnExpenseTotal,LEN(lnExpenseTotal)-AT(":",lnExpenseTotal))
*
SELECT EXPENSEPRINT
REPORT FORM EXPENSEREPORT.FRX TO PRINTER
*
SELECT EXPENSEPRINT
DELETE ALL FOR EMPTY(EXPENSEPRINT.EXPENSEREC)
LABEL FORM EXPENSERECEIPTS.LBX TO PRINTER
*
SELECT EXPENSEPRINT
USE

I don't think any of this is an issue, but provided in case I'm doing something stupid.
Any ideas why this same record prints over and over until the detail band has printed the same number of records as is in the table that is its source data?


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Scott24x7]

Oh, I forgot to mention...

When I run this in the IDE (I just set up the m.<variables> and then press ! in the toolbar), it works PERFECTLY. When I run it in the .EXE, I just get the same line repeated over and over, as described above!


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[mjcmkrsr]

Hi,
Looks like there is a field in your REPORT/LABEL form that is not in your table EXPENSEPRINT. You might want to check whether the fields in your table match the fields in your REPORT/LABEL forms (detail band, grouping, header and footer ...)
hth
MarK

 

[Scott24x7]

Mark,
If that were true, I would get an error stating that the "Variable <variable name> is not found." (As I have gotten when I was converting the field names from the SELECT statement that created dynamic names that are unique. So if I were missing a variable in the detail band (and those fields are all specified as EXPENSEPRINT.<fidlename> in their expressions, then I would get such an error.
Any other ideas?


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Koen Piller]

Scott,
Please run your report again and report exactly what the error message is, as your "expenseid_a is not a field" is not an existing message.
Koen

 

[Scott24x7]

Koen,
That error is no longer relevant, as I no longer create the cursor, instead I write directly to a table, which was why I had to re-map the field names. (The cursor allows greater than 10 character field names, and handels duplicates with the LEFT JOIN with <fieldname>_A, <fieldname>_B. When you write to table instead, VFP treats it as a free table, so only 10 character field names are allowed, so fields that collide in naming like EXPENSEID as EXPENSEID and EXPENSEID2. So it takes the first instance up to 10 characters, and after that adds 2, 3, 4 etc. when there are more collisions.

There are no longer any fields that are not found, and all are mapped this way. I gave some history as to why I went away from the CURSOR option, so that it might prevent someone suggesting I instead output TO CURSOR instead... been down that route, and had that problem.

The problem I have now is, for some reason it completely doesn't respect my detail grouping. (It gives me the number of records in the output free table named EXPENSEPRINT, but the first row only repeated over and over).

This is the problem I'm now trying to overcome. I do not understand why I'm just getting the data from one record repeated for all the outputs, instead of the individual data for each record. I feel like I must be missing something obvious, but I don't know what. The thing that really stumps me is, as I mentioned if I'm testing the report against a previously output data set, the report works, the moment I try it as a .EXE I get this result with one record repeated for each table entry.
Really driving me crazy, I've wasted 3 days trying to get it to work with both CURSOR or table as output, and it continues not to work...

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Olaf Doschke]

You have to SELECT the table you want to print before REPORT FORM, your report is likeöy driven by another workarea and therefore not scanning (skipping in) the records of the table you print.

When your report hasn't got a data environment opening table(s) and having a .SelectedAlias property defining an active workarea, a report is driven by whatever is the active workarea, no matter what expressions you use in report fields and if they all point to the workerea you want to be the active one.

A grid can be a reason for this. Whenever a grid has focus when you execute your REPORT FORM the recordsource also becomes the report driving workarea, that's a known behaviour and the wrokaround is to set fosuc to an unbound control, SELECT the wanted workarea explicitly and then REPORT FORM.

Mike Lewis also has written an article about that grid/&report behavior:

VFP reports not showing the same data as the preview

By Dan Macleod Has this happened to you? You preview a report in Visual FoxPro. Everything looks fine. You then print the report by clicki...

hexcentral.blogspot.com

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Scott24x7]

Hi Olaf,
I was aware of that, and if you notice in the code I pasted in the original post, the lines as follows:

SELECT EXPENSEPRINT
REPORT FORM EXPENSEREPORT.FRX TO PRINTER
*
SELECT EXPENSEPRINT
DELETE ALL FOR EMPTY(EXPENSEPRINT.EXPENSEREC)
LABEL FORM EXPENSERECEIPTS.LBX TO PRINTER
*
SELECT EXPENSEPRINT
USE

I've done exactly that after building the memvars that are associated with it.
Have I done something wrong there?
In reality, I select it twice. I select it right after the query with:
SELECT EXPENSEPRINT
GO TOP

Then I build the memvars.
Then just to be sure I select it again:
SELECT EXPENSEPRINT
REPORT FORM EXPENSEREPORT.FRX TO PRINTER

This is why I'm so baffled.
I thought maybe I have done something stupid in the report form itself?


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Olaf Doschke]

It's not enough to SELECT the correct workarea, you have to set focus to something else but the grid, that is the essence of the problem.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Scott24x7]

Olaf,
There is no Grid selected during this. The form has a "Print" button on the form, that object's click event is the last thing to occur. The code at the top executes the click event. It clearly "sees" that table, because the second "REPORT FORM" works just fine. It selects the table, deletes all records that have an empty "receipt" and then prints all the records with their receipt number and the receipt image. Both REPORT FORMS use the same table. The second just doesn't have a data grouping, because it just prints all the records that the table has. (It's technically a LABEL format, it prints 4 receipts per page, but the VFP interface for lack of a better word, is essentially the same for REPORT as it is for LABEL.

So I'm still stumped... you think it is something to do with having focus on the report button? Or some grid on the form still? How is that possible?


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Olaf Doschke]

Well, then forget about the grid behaviour.

You're sayiing you group data in the FRX, you'll likely have additional bands, look into the "on entry" expressions of bands whether you SELECT EXPENSE there or even simpler the target alias expression you set in the Band tab of the band properties dialog.

This will cause the change of the report driving cursor or workarea besides report data environment could change the active workarea. It seems clear already this is not EXPENSEPRINT anymore, you stay on the same record and print it over again, if the count matches the reccount of EXPENSE, you know it has become the driving workarea.

By the way, when you query INTO TABLE it makes no difference whether you add options like READWRITE or NOFILTER to the query, you create a DBF file. I recommend not to, but you know your older thread and in the end it's your decision. When you create a table with data to print, it could be easier to let the report have a private datasession and use the data to print within it, then you can limit that to the data you only want to print and at least you'll get an error if anything within the FRX is trying to SELECT another workarea not used in the private data session.

Bye, Olaf.


Olaf Doschke Software Engineering

https://www.doschke.name

 

[Scott24x7]

Hi Olaf,
So I followed that link to the issue about "Print Preview", just out of desperation, and while it didn't specifically fix the issue, it did get me to thinking. It seems overall that the issue was the EXPENSEREPORT.FRX was somehow corrupted. I copied an old version from another machine in, gave it a different name (EXPENSEREPORT4.FRX), and changed the code back to using the CURSOR method, and magically, it all worked again!
The only thing I can figure is there is an issue with the FRX itself. But seems all is now working... drove me crazy, wish I'd have thought to do this 3 days ago!


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Scott24x7]

After the above, I again ran into the issue where it was telling me that EXPENSEID_A was not found!
BUT...
A strange fix seems to have resolved it. When I went to run the reports as previews (I have separate "Print" button and "Preview" button on the same form), I hadn't run the "PREVIEW" for a long time, and when I did I got a very garbled looking report in the preview. Then I recalled something similar back a few months ago, and had some vague recall of setting something to 80 or 90. So as you may have guessed that was SET REPORTBEHAVIOR 90 (It defaults to 80, but I'm using VFP9).

Setting that in the preview, and previewing first, and THEN printing it worked!
So I put the SET REPORTBEHAVIOR 90 in the print button code, and it also worked!
I noticed as well that the dialog box that appears during this time is different (it's a progress meter instead of a plain text box that says now printing page 1...X).
So I'm not sure exactly what's taking place during the rendering, but with that REPORTBEHAVIOR set to 90, it seems to have resolved the issues, and I get a nicer progress bar anyway.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[TamarGranor]

I just want to point out that the real problem here is that you're doing SELECT * rather than listing the fields you want and renaming those you need to. SELECT * is a bad idea for a bunch of reasons. In this case, the biggest problem is that it's including same-named fields from both tables.

If you write the query with an actual field list, I think most of your problems will go away and you can use a cursor as you should.

Tamar

 

[Scott24x7]

Hi Tamar,
I can see that that would allow me to rename fields that are same-named, but it's actually working now. And strangely the "thing" that makes it work is the SET REPORTBEHAVIOR 90 which really surprises me.
Not sure exactly what is going on in that call, but it's certainly "more advanced" than the backward compatible behavior, and that's even evidenced in the type of dialog that appears while the report is being generated. But thanks, I'll keep that in mind. The reality is 95% of both tables fields are still used, so it's a cleaner view in the code.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate.

 

[Olaf Doschke]

Reportbehavior 90 would only have influence over some features only working in that mode. But being ignorant of missing field names is not one of them. Since your report has some field expression needing EXPENSEID_A you depend on a SQL query doubling the name by source tables having the same field you join (perhaps exactly on that field). If your source tables are indeed cursors varying in their structure and sometimes having those fields.

The culprit is having a report field depending on such a generated field name. As I prevent those things I actually don't know whether the SQL engine of VFP always reproducible names the same fields with _a suffix or if that varies, then your report sometimes works, sometimes not. It might also depend on the SET ENGINEBEHAVIOR setting, for example, whether the first field with the same name keeps its name and the second gets a suffix or whatever. Have a reproducible and stable outset, then you also don't have such on/off working/not working situation.

It's a very general good advice of Tamar I second, that you only use * in queries you know well won't suffice from such behavior, i.e. queries on only one table. I once also have used a name convention for tables to all have individual names including a table short (3-4 letters) that varies and is unique per database, which means no double field names, no matter how tables are joined. I didn't stay with such a name convention as it gives cryptic names and that's worse for maintainability or readability of code than the problem it prevents.

But to summarize, if the field_a problem sneaks in and your report is based on such a field in one of the report field expressions, then you're depending on a name suffix preventing double field names in a query result, which is not under your full control and depends on the outset of source tables and maybe even sql engine behavior. I know you said reportbehavior fixes it, I am sure that's not fixing missing fields or something like automatically looking if field_a does not exist, whether field without the _a suffix exists, there is no such automatic fixing, so my guess is you did a lot of things and something fixed your report but if the correct execution depends on reportbehavior 90, then not because of the field name suffix.

You may have started creating the report on my recommendation of creating a cursor, used SQL that didn't care for a field list and created such field names with a suffix. When you then design your report with such field names as their expression you invite such trouble. I always prefer to have full control and that means not being lazy about such things.

You can easily get a full field list of something when you're lazy about typing- ÖLet me demonstrate that with northwind data, where such a field name convention is doubling field names, eg customers.customerid and orders.customerid result in the two customerid fields named customerid_a and customerid_b. In such a simple single join I don't fear the customerid fields will get other suffixes or swap _a and _b randomly, but in more complex joins or when you prepare data differently under different situations you could sometimes have a field uniquely in the tables involved and no suffix.

Anyway, here's the code to get a field list of a table:

open database (_samples+'\northwind\northwind.dbc')
use customers
set fields global
_cliptext = set('fields,1)

Now paste the fieldlist where you need it, because of setting fields to global the field list contains names in the format tablename.fieldname and if you do that for all tables involved you get every field name fully qualified and can then easily define unique names as you want them, eg customers.customerid as customer_custoimerid and orders.customerid as order_customerid, or remove some fields, i.e. remove orders.customerid and only keep customers.customerid unaliased as is, as those fields will have the same value by definition of joining tables on them. You should remain the creator of your data structures and not be sloppy about it and be happy when things work fine and run around like a berzerk with conspiracy theories about undocumented secret behaviors and rare conditions leading to your problems.

Since you say you have preview and print button, the way you describe having the problem and not having it sometimes I'd look into whether both buttons really prepare the data exactly the same or whether you once copy&pasted code querying data into table or cursor and then maintained them differently. Code preparing data for a report should be in one place like anything in OOP make use of the reuse paradigm and don't ever copy&paste code.

I know, these are all assumptions, assumptions, assumptions. But I know you and how you wind yourself out of things, which turn out to be coming from not sticking to the rules. It always boils down to needing to be very pedantic about things. The computer and programming languages always execute what you tell them, not what you intended. If there's an error it's rarely a bug, more often a design flaw of language inventors not acting on the principle of least surprise but most of the time simply some error from your side or misunderstanding.

There are only a few things on the topic of reports you never figure out without being told:
1. A report just blinks up like an EXE without READ EVENTS if the workarea you report has 0 records
2. A report acts on the wrong driving workarea, when a grid has focus while you run a report
3. private data session vs. default datasession determines whether you can even control the report from outside,i.e. a FOR clause only works in a default datasession report. Both things can be fine, but you know me and most experts despite the OOP encapsulation principle like to use default datasession and not fully self-contained reports based on a private dataenvironment. One of the reasons is it has no parameters, even though you can use even local variables just currently in scope and access them in report fields when you REPORT FORM, but not in the report dataenvironment code section. So that's a strange way the runtime provides variable access. Indeed you could always access all variables, the runtime manages them in a name table and has global access even to local variables. It would just defy the scope of variables to allways allow all variable access, and it would complicate the cases of same named local or private variables. Confused yet? That's why some even don't use that feature and only work with private or public variables within reports. Well, not me, but I've seen frameworks automating both views and reports with private arrays. Might also be a thing changed at some VFP version.
4. Yes, and some features depend on reportbehavior, but field name suffixes or ignoring them is not one of those features. It's a bit of a pitty VFP9 defaults to reportbehavior 80, but when VFP9 was new it was causing the least trouble as all report initially were legacy. You can change such defaults in options and make a permanent change, but EXEcutable defaults will not be your options defaults. That's also a very general reason to have a class for all environmental settings, not just some, all.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name