Selecting a record for a report

[audiopro]

I am updating an app written a few years ago and need a bit of advice regarding selecting a record for a report. The record is an invoice and the original function displayed a browse window with all the invoices listed in invoice number order and set to the bottom of the table. The operator selected the required invoice record and hit ESC. This called a report form which was the formatted invoice and the full invoice was displayed in preview mode. If all was well, the operator selected 'print' and a pretty invoice was ejected from his printer.

This has worked for years without any problem at all, until now that is. It is not so much a problem that has been introduced as a new line of business. Some of his new products are exempt from VAT so a second type of invoice is now required. The problem I have is how to test which type of invoice is required.

There are 3 main tables involved here.
Customers:-
customers names and addresses etc.

Invoices:-
The actual invoice details

Services:-
Services offered - some are vat exempt some are not.
There is a field (VT) which contains 'V' if vatable and 'N' or nothing if not.
Do I set up a query to get this information or is there a better method. All I need to know is whether the whole invoice contains any non vat items.
Could you please give me some pointers in the right direction. I am looking for the strategy, not the actual code.
Thanks

Keith

http://www.studiosoft.co.uk

 

[white605]

Not sure if this is the right approach or not but it could be used to branch
select * from table where vat#[V] into cursor cursorname
if reccount("cursorname")>=1
There are value added items
endif

This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[audiopro]

Thanks, I will give that a try tomorrow.
I tried sending the results of the query to an array but I hit problems if the invoice did not contain any services.


Keith

http://www.studiosoft.co.uk

 

[audiopro]

This is what I have ended up with and it works ok. It seems a long winded way of doing it though.

Any suggestions on how it could be tidied up?

SELECT CUSTOMER
SET ORDER TO CNUM
SELECT PICCY
SET ORDER TO P_REF
SELECT FRAMES
SET ORDER TO INVNO
GO BOTTOM

BROW FIELDS FRAMES.INVNO, FRAMES.CNUM, FRAMES.INTOT, FRAMES.PICTOT, frames.freetot, CUSTOMER.C_SNAME, PICCY.OWNER, PICCY.P_REF ;
FOR (FRAMES.INTOT>0.00 OR FRAMES.PICTOT>0.00 OR FRAMES.FREETOT >0)


INO=FRAMES.INVNO
SOR=0
PICREF=PICCY.P_REF
IF PICREF > 0

   SELECT OWNER FROM Z:/DATA/PICCY.DBF WHERE PICCY.P_REF=PICREF INTO CURSOR PICOWNER

   IF RECCOUNT("PICOWNER") = 1
      IF PICOWNER.OWNER='S'
          SOR=1
      ENDIF
   ENDIF
ENDIF


IF SOR=1
   REPORT FORM Z:\REPORTS\SORINVOICE FOR FRAMES.INVNO=INO PREVIEW
   REPORT FORM Z:\REPORTS\SORINVOICE2 FOR FRAMES.INVNO=INO PREVIEW

   ELSE
      REPORT FORM Z:\REPORTS\INVOICE FOR FRAMES.INVNO=INO PREVIEW
      REPORT FORM Z:\REPORTS\INVOICE2 FOR FRAMES.INVNO=INO PREVIEW
ENDIF

Keith

http://www.studiosoft.co.uk

 

[audiopro]

I spoke too soon, the process only works once then it falls over.
I have relationships set up in the data environment, is the select I am doing above affecting that relationship in some way. Should I get rid of the relationships in the data environment and hard code them?

This is yet another thing in VFP which I have found comes back to haunt me time and again. I am sure it is my lack of knowledge which is the main problem but I have found the manual to be of no help whatsoever in solving such issues.


Keith

http://www.studiosoft.co.uk

 

[white605]

Where are you using the field VT?
Is it PICOWNER.OWNER?

What happens the second time you run the program, Error?, Incorrect data in report?

You can either put the tables in the data environment and filter the report output
or
Select all the information from related tables into a cursor with only the records you want and report on the cursor that is created each time you run the report/s

This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[audiopro]

Sorry if I have confused the issue but I have had to add some more status options to each invoice. The status was in the VT column but is now in the OWNER column of the cursor. The issue remains the same as there is only 1 VAT exempt option which is now signified with 'S'.
The second time the program was run, the relationship between FRAMES and PICCY was lost and the PICCY.OWNER, PICCY.P_REF columns in the browse window were blank. The report just listed all the records without an owner status which was wrong.
I removed the

SELECT CUSTOMER
SET ORDER TO CNUM
SELECT PICCY
SET ORDER TO P_REF

from the code and the relationships remain intact.
Would setting the order in this way, even though they are the same as the data environment relationships, affect the relationships?


Keith

http://www.studiosoft.co.uk