I have limited knowledge about FoxPro 9; but have done hours of trial and error. I need help with this as I have struggled now for many hours.Application: Form will be used for invoice at point of purchase. My form is working except for my preview and print command buttons. It is adding the record to my database and my report is working but the preview and the print does all records including the last record (that currently is on my screen/form). I only want to see and print the last recordIn old Fox Pro my command would be:REPORT FORMAT "C:\INVOICE.FRX" REST ENVIROMENT PREVIEW (rest meaning the last invoice)Thanks for your help.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Need command line for preview and print Visual Foxpro 9
- Thread starter foamlady
- Start date
- Thread starter
- #21
JRB
I followed each step and up came the VFP debugger. I typed in the command window and my database (showinvoice) opened with the curser on the record that I had entered on the form (invoiceentry) and all the information was displayed there. I then added the NEXT 1 clause to my report line in my program, went back in and once again it previewed the first record not the last. Does that help. In researching I read that if you used the form designer and not the wizard that it would put your information directly into your database. My form and report do what they are suppose to so I'm pretty sure all my links are okay. I do have a field in my database recordnum that generates from the computer and it is indexed.
Once again thanks for your patience and helping step by step.
I followed each step and up came the VFP debugger. I typed in the command window and my database (showinvoice) opened with the curser on the record that I had entered on the form (invoiceentry) and all the information was displayed there. I then added the NEXT 1 clause to my report line in my program, went back in and once again it previewed the first record not the last. Does that help. In researching I read that if you used the form designer and not the wizard that it would put your information directly into your database. My form and report do what they are suppose to so I'm pretty sure all my links are okay. I do have a field in my database recordnum that generates from the computer and it is indexed.
Once again thanks for your patience and helping step by step.
Dan Freeman
Programmer
It sounds like your report has a private dataenvironment that's opening its own copy of the table, which will by default start on the first record.
Remove the dataenvironment.
You do not need to muck with variables to print the current record. You just need to make sure the report starts on the current record.
Remove the dataenvironment.
You do not need to muck with variables to print the current record. You just need to make sure the report starts on the current record.
I typed in the command window and my data table(showinvoice) opened with the curser on the record that I had entered on the form (invoiceentry) and all the information was displayed there. I then added the NEXT 1 clause to my report line in my program, went back in and once again it previewed the first record not the last.
Yes that helps a lot.
It tells us that your Form data has been written to your data table and that the record pointer is on that record (as would be expected).
With that fully understood, it may be as Dan says above - the issue may be in the Report Form itself. If it has a Private Data Session of your data table (showinvoice) then it will not work as 'normal'.
In your Command Window type in MODI FORM <name of your report form>
In the VFP top-level menu go into Report and make sure that Private Data Session is not checked.
Then go into Report Properties and make sure that everything in there is empty or not-checked:
* Other tab page has nothing in Edit User Data.
* Data Environment tab page has nothing set into it.
* (basically set up like a 'virgin' report form)
And then do a Save.
Now to test....
Leave your Report Form open on your VFP IDE
From your Command Window open your data table and move the record pointer to a specific record.
In your Command Window again type:
REPORT FORM <report name> NEXT 1 PREVIEW NOCONSOLE
And see what happens.
Let us know.
Good Luck,
JRB-Bldr
Yes that helps a lot.
It tells us that your Form data has been written to your data table and that the record pointer is on that record (as would be expected).
With that fully understood, it may be as Dan says above - the issue may be in the Report Form itself. If it has a Private Data Session of your data table (showinvoice) then it will not work as 'normal'.
In your Command Window type in MODI FORM <name of your report form>
In the VFP top-level menu go into Report and make sure that Private Data Session is not checked.
Then go into Report Properties and make sure that everything in there is empty or not-checked:
* Other tab page has nothing in Edit User Data.
* Data Environment tab page has nothing set into it.
* (basically set up like a 'virgin' report form)
And then do a Save.
Now to test....
Leave your Report Form open on your VFP IDE
From your Command Window open your data table and move the record pointer to a specific record.
In your Command Window again type:
REPORT FORM <report name> NEXT 1 PREVIEW NOCONSOLE
And see what happens.
Let us know.
Good Luck,
JRB-Bldr
- Thread starter
- #24
JRB
The Private Data Session in the report was checked. I "unchecked" it,
In the report properties I had
DataEnvironment tab
has copy from another report file is checked
(tried and can not uncheck it)
Variables tab
I have two variables set up to do calculations in the report
I did a save on the report change (uncheck private)
Now for the test.
I opened the database from the database designer window.
I put my pointer in the middle and typed report NEXT 1, etc in command window.
Once again the report came back with record number 1 not where I was sitting.
What am I missing?
The Private Data Session in the report was checked. I "unchecked" it,
In the report properties I had
DataEnvironment tab
has copy from another report file is checked
(tried and can not uncheck it)
Variables tab
I have two variables set up to do calculations in the report
I did a save on the report change (uncheck private)
Now for the test.
I opened the database from the database designer window.
I put my pointer in the middle and typed report NEXT 1, etc in command window.
Once again the report came back with record number 1 not where I was sitting.
What am I missing?
Olaf Doschke
Programmer
When modifying the report, right click on the grey area besides the report page. Then you'll see one or more tables in a grphical representation. That means the report does open the table again. Remove the table there, that should solve the problem.
Bye, Olaf.
Bye, Olaf.
Olaf Doschke
Programmer
...right click on the grey area besides the report page.
more precise: Do that and choose the dataenvironment item of the context menu appearing. Then you'll see...
more precise: Do that and choose the dataenvironment item of the context menu appearing. Then you'll see...
- Thread starter
- #27
Olaf Doschke
Programmer
Well, if those tables are open by the form already they don't need to be (re)opened by the report. At the least the one main table always starting from the top is hindering you to let the scope make an effect.
It might be an idea to turn private datasession on again in the report and filter the main table to a single record number.
If you right click on such a table object within the reports dataenvironment you can choose properties. In the properties of the table object you can set a Filter property. Set that property <to RECNO()=lnRecno
Before doing the REPORT FORM Command do the inverse and set lnRecno=RECNO() and the report will be limited to that record only.
This means the variable lnRecno will be set to the current record number of the table open in your form, the rpoert will inversly check the recno() of the newly opened table and only allow that one record matching lnRecno.
Bye, Olaf.
It might be an idea to turn private datasession on again in the report and filter the main table to a single record number.
If you right click on such a table object within the reports dataenvironment you can choose properties. In the properties of the table object you can set a Filter property. Set that property <to RECNO()=lnRecno
Before doing the REPORT FORM Command do the inverse and set lnRecno=RECNO() and the report will be limited to that record only.
This means the variable lnRecno will be set to the current record number of the table open in your form, the rpoert will inversly check the recno() of the newly opened table and only allow that one record matching lnRecno.
Bye, Olaf.
Koen Piller
Programmer
Hi FoamLady,
I dont want to intervere with the excellent replies given already by Olaf and others, but an other solution would be to create a cursor curReport with a SQL select ** into cursor curReport with the data you require for your report. Than (in development) you browse/view your cursor before even thinking about the report, if you are satisfied with the data/results in that cursor. In final application you could skip the browse/viewing of the cursor. In your report you call only the fields of your cursor. This way you leave your data environment as it was and your report will only print the records in your cursor.
In case you need further assistance for this approach please report back.
Regards,
Jockey(2)
I dont want to intervere with the excellent replies given already by Olaf and others, but an other solution would be to create a cursor curReport with a SQL select ** into cursor curReport with the data you require for your report. Than (in development) you browse/view your cursor before even thinking about the report, if you are satisfied with the data/results in that cursor. In final application you could skip the browse/viewing of the cursor. In your report you call only the fields of your cursor. This way you leave your data environment as it was and your report will only print the records in your cursor.
In case you need further assistance for this approach please report back.
Regards,
Jockey(2)
Olaf Doschke
Programmer
Jockey,
good idea.
I don't know, though, if the report data environemnt specifies an InitialselectedAlias, if that overrides the currently selected cursor. It seems it is the case, as the current table is overriden despite of the report not being set to a private datasession.
Bye, Olaf.
good idea.
I don't know, though, if the report data environemnt specifies an InitialselectedAlias, if that overrides the currently selected cursor. It seems it is the case, as the current table is overriden despite of the report not being set to a private datasession.
Bye, Olaf.
Dan Freeman
Programmer
foamlady said:
I feared this. I suspect we've all been giving you well-meaning advice that just isn't a very good idea at all.
What are those tables doing in the dataenvironment? Did you put them there? Did someone ELSE put them there? Do you know what they do? Is the report used in other places? What are you breaking elsewhere by messing with this report?
I'd hate to see your monthly billing process get destroyed because of advice you received here. Every one of us here LIKES collecting money and would hate to inadvertently break that process.
- Thread starter
- #32
Dan,
No, nothing I do will screw up our billing situation. This application is for on the spot purchases (of seat foam)-- cash or credit card; at aviation shows. I bring the credit card information home and run the cards from here.
I opened these tables to link each of the line entries of item number to the item table that contains item number, description, cost. My form contains drop down combo lists that I pull from. The next line of the invoice opens a copy of the item table and links that way. And so on through seven lines.
Do I make sense? I have not deleted anything from Data Environment -Report Designer yet. My report reads fine it's just that one problem not being able to preview and print the current record.
Really thanks to all
No, nothing I do will screw up our billing situation. This application is for on the spot purchases (of seat foam)-- cash or credit card; at aviation shows. I bring the credit card information home and run the cards from here.
I opened these tables to link each of the line entries of item number to the item table that contains item number, description, cost. My form contains drop down combo lists that I pull from. The next line of the invoice opens a copy of the item table and links that way. And so on through seven lines.
Do I make sense? I have not deleted anything from Data Environment -Report Designer yet. My report reads fine it's just that one problem not being able to preview and print the current record.
Really thanks to all
Dan Freeman
Programmer
Ah! After this entire lengthy thread I think it's finally becoming clear what you're doing! (It's actually what I originally thought and then questioned.)
A couple of thoughts....
Instead of using related tables in the report's DE, you could nuke the DE entirely and replace the printed fields from those related tables with expressions built with Lookup(). (Do not be scared off by the unfortunate documentation wording.)
*OR* (not *AND*)
Go back to a private DE and implement the filter condition per Olaf's earlier instructions.
*OR* (not *AND*)
Replace JUST the main table in your DE with a parameterized local view, using the unique key of the invoices table as the parameter and all of the relations into lookup tables as they are now. Set the parameter (variable) before calling the report and Bob's your uncle. You don't need a scope on the report at all when the underlying view only has one record in it.
There are always multiple ways to do any task in VFP. But you can't take one from each column like a Chinese menu. You have to do all of one thing, and no more than that one thing.
A couple of thoughts....
Instead of using related tables in the report's DE, you could nuke the DE entirely and replace the printed fields from those related tables with expressions built with Lookup(). (Do not be scared off by the unfortunate documentation wording.)
*OR* (not *AND*)
Go back to a private DE and implement the filter condition per Olaf's earlier instructions.
*OR* (not *AND*)
Replace JUST the main table in your DE with a parameterized local view, using the unique key of the invoices table as the parameter and all of the relations into lookup tables as they are now. Set the parameter (variable) before calling the report and Bob's your uncle. You don't need a scope on the report at all when the underlying view only has one record in it.
There are always multiple ways to do any task in VFP. But you can't take one from each column like a Chinese menu. You have to do all of one thing, and no more than that one thing.
- Thread starter
- #34
Dan, and many others,
I will do Dan's suggestion but I won't be able to until late this evening.
I did try a "copy" of Olaf suggestion where I deleted all data environment and that gave me errors on my report because it couldn't find all my data in the linked databases.
I will let you know what happens; Dan, I may need some help writing it but let me give it a try first.
I will do Dan's suggestion but I won't be able to until late this evening.
I did try a "copy" of Olaf suggestion where I deleted all data environment and that gave me errors on my report because it couldn't find all my data in the linked databases.
I will let you know what happens; Dan, I may need some help writing it but let me give it a try first.
Olaf Doschke
Programmer
Well,
I'm sure you'll find a way now with dan's suggestions. In ragard of deleting all data environment, I said: "*If* those tables are open by the form already they don't need to be (re)opened by the report."
As you get errors, obviously your form does not have all the tables open.
I also said "At the least the one main table always starting from the top is hindering you to let the scope make an effect."
That is you could first have tried just removing that main table, of which you only want to print the current record, as that is already opened by the form for sure.
But this isn't working out, if there are relations set from the table you would remove.
I expected only the one table you report from to be in the data environment, not many tables, as that is what a foxpro novice would perhaps do first. It's not bad to have many tables in the data environment, it's even a good sign your tables are normalized and you have relations between them.
Of my solutions I'd now opt for the other one dan point's to, but it's having a disadvantage regard of maintainability. You need to know there is a filter set in a table of the reports DE to make it work and the report then would not work if no variable lnRecno would be set before calling it. On the other side you need some way to input the one record.
Dans siggestion number 1 or 3 are both good ways, with number 3 being more similar to my idea, with the advantage, that a view is better recognized as something accepting parameterisation. That means, you rather expect from a view than from a table in the dataenvironment to be somewhat parameterized, taking input from outside to filter the data.
Most important, you may try many suggestions to learn from them, but only one at a time, always restarting from a copy of your initial state, ideally. And then opt for the one you feel most comfortable with.
Bye, Olaf.
I'm sure you'll find a way now with dan's suggestions. In ragard of deleting all data environment, I said: "*If* those tables are open by the form already they don't need to be (re)opened by the report."
As you get errors, obviously your form does not have all the tables open.
I also said "At the least the one main table always starting from the top is hindering you to let the scope make an effect."
That is you could first have tried just removing that main table, of which you only want to print the current record, as that is already opened by the form for sure.
But this isn't working out, if there are relations set from the table you would remove.
I expected only the one table you report from to be in the data environment, not many tables, as that is what a foxpro novice would perhaps do first. It's not bad to have many tables in the data environment, it's even a good sign your tables are normalized and you have relations between them.
Of my solutions I'd now opt for the other one dan point's to, but it's having a disadvantage regard of maintainability. You need to know there is a filter set in a table of the reports DE to make it work and the report then would not work if no variable lnRecno would be set before calling it. On the other side you need some way to input the one record.
Dans siggestion number 1 or 3 are both good ways, with number 3 being more similar to my idea, with the advantage, that a view is better recognized as something accepting parameterisation. That means, you rather expect from a view than from a table in the dataenvironment to be somewhat parameterized, taking input from outside to filter the data.
Most important, you may try many suggestions to learn from them, but only one at a time, always restarting from a copy of your initial state, ideally. And then opt for the one you feel most comfortable with.
Bye, Olaf.
Koen Piller
Programmer
Olaf
your: I don't know, though, if the report data environemnt specifies an InitialselectedAlias, if that overrides the currently selected cursor
I dont think so, as long as you specify with curReport.field in the report objects it will and should work.
Have made numerous reports in VFP7 and 9 this way. Advantage: easy to maintain as you can construct the cursor with 'English' fields so i/o cryptic myCursor.amtduetw you can write myCursor.AmountDueThisWeek which is exactly as the report field indication. And in this case, as I understand FoamLady is making a report for-her-own use, one can browse the cursor or even if you like populate a form / grid with the data before printing.
Feel tempted to produce a working example with table structures given by FoamLady.
Regards,
Jockey(2)
your: I don't know, though, if the report data environemnt specifies an InitialselectedAlias, if that overrides the currently selected cursor
I dont think so, as long as you specify with curReport.field in the report objects it will and should work.
Have made numerous reports in VFP7 and 9 this way. Advantage: easy to maintain as you can construct the cursor with 'English' fields so i/o cryptic myCursor.amtduetw you can write myCursor.AmountDueThisWeek which is exactly as the report field indication. And in this case, as I understand FoamLady is making a report for-her-own use, one can browse the cursor or even if you like populate a form / grid with the data before printing.
Feel tempted to produce a working example with table structures given by FoamLady.
Regards,
Jockey(2)
Dan Freeman
Programmer
Hi Olaf,
Yeah, most of us would use a filter only as a last resort.
To be honest, with all these "related" (she said "linked" and I'm assuming relations in the DE) tables, most experienced VFP developers would run a query with proper join conditions to suck all values for the report into a single-record cursor, and then we'd remove aliases from the printable expressions (instead of table.fieldname we'd just use fieldname) and let the report run against the current alias.
The same approach could be done with a parameterized local view, which is nothing more than a query unless you juice it up for updates.
I think we've gone the long way around to get here, but I think the end is in sight.
Yeah, most of us would use a filter only as a last resort.
To be honest, with all these "related" (she said "linked" and I'm assuming relations in the DE) tables, most experienced VFP developers would run a query with proper join conditions to suck all values for the report into a single-record cursor, and then we'd remove aliases from the printable expressions (instead of table.fieldname we'd just use fieldname) and let the report run against the current alias.
The same approach could be done with a parameterized local view, which is nothing more than a query unless you juice it up for updates.
I think we've gone the long way around to get here, but I think the end is in sight.
- Thread starter
- #38
To all,
You have all been kind enough to give me many opinions and options -- I don't know which way to turn and most I don't know how to implement.
I am willing to start over but although I thought I understood report designer and I did relate tables real good 36 replies to my questions tells me differently.
Do I start over and if so use the report designer; data enviroment;
OR
tell me step by step the best way to fix it.
Olaf, yes I'm a NOVICE but I'm trying
Dan, I like Chinese but after reading everything I could find I started to graze.
You have all been kind enough to give me many opinions and options -- I don't know which way to turn and most I don't know how to implement.
I am willing to start over but although I thought I understood report designer and I did relate tables real good 36 replies to my questions tells me differently.
Do I start over and if so use the report designer; data enviroment;
OR
tell me step by step the best way to fix it.
Olaf, yes I'm a NOVICE but I'm trying
Dan, I like Chinese but after reading everything I could find I started to graze.
Olaf Doschke
Programmer
Well, the first thing you need to understand is, that a report with a private datasession does not see the data open in the form calling the report, they are seperated. So the report doesn't know your're on a certain record.
And from the other point o view: Only with a private datasession you can open and relate the tables in the report data environment as you did, which is not wrong and indeed a quite simple approach to relate the data, and to have it available for designing the report controls. But in the first does not filter it, so you always report all data.
The scope clause of the REPORT FORM command only can work for reports without a private datasession. This can still have a data environmen with tables in it, but not the table steering the report, that must be the current table already open before the report is started, for the scope clause to take effect.
So the main decision is for private datasession or not. Having the datasession filled is better done with a private datasession, using the current datasession instead, the form or whatever code calling the report is mainly responsible for providing all the tables the report needs. The report still can open tables itself, but if it also opens it's main steering table the current datasession and the code calling the report will have no effect on it.
The problem is, you can't have the best of both worlds:
On the one side: A report being easily adressible by a scope like NEXT 1 will not have the data in it to comfortly design it. You can still set the report controls to yourtable.yourfield, even if the report itself does not open it and will therefor not be working on it's own, independantly of code opening the table before calling the report.
On the other side: A report running on it's own, opening it's tables is working from scratch and not connected to the data you have at hand in the form or other code calling it. The influence from outside then only can be made by variables set to values filtering the data, and the tables or code within the dataenvironment or report control properties then need to be ontrolled by those variables, if you want less than all data.
Anther way inside the report in a private datasession is using a table solely created for the report, which you ZAP and only fill with the data you want reported.
So in your case, you would create a secondary invoice table as reportinvoice.dbf with the same fields an indexes. Replace the invoice table in the report with the reportinvoive table and then, before starting the report ZAP the reportinvoice and put the one record in it you want to report on, via insert into reportinvoice Select * from invoice where id=<<the current id>>
Bye, Olaf.
And from the other point o view: Only with a private datasession you can open and relate the tables in the report data environment as you did, which is not wrong and indeed a quite simple approach to relate the data, and to have it available for designing the report controls. But in the first does not filter it, so you always report all data.
The scope clause of the REPORT FORM command only can work for reports without a private datasession. This can still have a data environmen with tables in it, but not the table steering the report, that must be the current table already open before the report is started, for the scope clause to take effect.
So the main decision is for private datasession or not. Having the datasession filled is better done with a private datasession, using the current datasession instead, the form or whatever code calling the report is mainly responsible for providing all the tables the report needs. The report still can open tables itself, but if it also opens it's main steering table the current datasession and the code calling the report will have no effect on it.
The problem is, you can't have the best of both worlds:
On the one side: A report being easily adressible by a scope like NEXT 1 will not have the data in it to comfortly design it. You can still set the report controls to yourtable.yourfield, even if the report itself does not open it and will therefor not be working on it's own, independantly of code opening the table before calling the report.
On the other side: A report running on it's own, opening it's tables is working from scratch and not connected to the data you have at hand in the form or other code calling it. The influence from outside then only can be made by variables set to values filtering the data, and the tables or code within the dataenvironment or report control properties then need to be ontrolled by those variables, if you want less than all data.
Anther way inside the report in a private datasession is using a table solely created for the report, which you ZAP and only fill with the data you want reported.
So in your case, you would create a secondary invoice table as reportinvoice.dbf with the same fields an indexes. Replace the invoice table in the report with the reportinvoive table and then, before starting the report ZAP the reportinvoice and put the one record in it you want to report on, via insert into reportinvoice Select * from invoice where id=<<the current id>>
Bye, Olaf.
Olaf has done a good job of explaining things.
Here's a question that I don't remember having been asked.
Was this Report Form used before in some manner within the application?
I can only guess that it was because of all of the data tables that were set up in the Private Data Session.
If that is/were so, then you probably need to leave it alone so that it will keep working for its original purpose and make changes to a copy of it - naming this NEW Report Form something else for this NEW need.
I am going to make some guesses here, so please correct me if I am wrong.
I am going to guess that those other tables shown in the Report Form's Private Data Session are Related to your InvoiceData table and that they are providing the Report Form with non-invoice data about the customer, possibly such as Name, Address, City, etc. And there may be yet another table involved which may be providing data on Total Balance Due, etc. Maybe other info from other tables as well.
But all that info is from data tables that are identified in the Private Data Session in your original Report Form.
Once those tables are known and how they are related (maybe by CustomerID or something), it is a relatively simple matter of putting new code into your User Form/Screen to:
1. Temporarily set up the same necessary table relations
2. Do a REPORT FORM NEXT 1 to your NEW Report Form containing No Private Data Session.
3. Release the data relationships to continue on in your Form.
* OR *
1. Creating a single temporary Report Query Cursor using a SQL Query to accumulate the necessary data from multiple data tables for the invoice customer.
2. Do a REPORT FORM to your NEW Report Form containing No Private Data Session
3. Eliminate the temporary Report Query Cursor.
If you want to understand more on setting up the data table relationships you can look at the video named:
Q&A: Using Related Tables In A Report
in the Garfield videos I mentioned above.
Good Luck,
JRB-Bldr
Here's a question that I don't remember having been asked.
Was this Report Form used before in some manner within the application?
I can only guess that it was because of all of the data tables that were set up in the Private Data Session.
If that is/were so, then you probably need to leave it alone so that it will keep working for its original purpose and make changes to a copy of it - naming this NEW Report Form something else for this NEW need.
I am going to make some guesses here, so please correct me if I am wrong.
I am going to guess that those other tables shown in the Report Form's Private Data Session are Related to your InvoiceData table and that they are providing the Report Form with non-invoice data about the customer, possibly such as Name, Address, City, etc. And there may be yet another table involved which may be providing data on Total Balance Due, etc. Maybe other info from other tables as well.
But all that info is from data tables that are identified in the Private Data Session in your original Report Form.
Once those tables are known and how they are related (maybe by CustomerID or something), it is a relatively simple matter of putting new code into your User Form/Screen to:
1. Temporarily set up the same necessary table relations
2. Do a REPORT FORM NEXT 1 to your NEW Report Form containing No Private Data Session.
3. Release the data relationships to continue on in your Form.
* OR *
1. Creating a single temporary Report Query Cursor using a SQL Query to accumulate the necessary data from multiple data tables for the invoice customer.
2. Do a REPORT FORM to your NEW Report Form containing No Private Data Session
3. Eliminate the temporary Report Query Cursor.
If you want to understand more on setting up the data table relationships you can look at the video named:
Q&A: Using Related Tables In A Report
in the Garfield videos I mentioned above.
Good Luck,
JRB-Bldr
- Status