Silly help for new VFP user

[nightshade73]

Im new to learning VFP. I have two tables. invoice and invoiceDetail. The invoice number for invoice is invoice.in_inv_num. The invoice number for invoiceDetail is id_inv_num. (I know, no primary but that is how it was originally set up.) I'm trying to make sure that there are invoices in BOTH tables. So invoice.in_inv_num = invoiceDetail.id_inv_num. What is occasionally happening is the detail is not being created and I need to make a list of all invoices that are missing the detail record. I know how to browse both tables but I don't know what to write to compare the two. Any ideas? Thank you so much for reading this.

 

[JRB-Bldr]

First - welcome to Visual Foxpro and this forum.

As with many things in VFP, there are a number of ways to approach most all of the 'challenges'.

One was would be to do a SQL Query comparison

* --- Find all in Invoice Which are NOT IN InvoiceDetail ---
SELECT id_inv_num;
   FROM invoice;
   WHERE in_inv_num NOT IN (SELECT in_inv_num from invoiceDetail);
   INTO CURSOR Missing
* --- Check resultant Cursor ---
* --- If No Records, then All OK ---
* --- If Records, Those are Missing ---
SELECT Missing
BROWSE && or whatever

NOTE - you can reverse the tables to check in the other direction.

There is also a way via Table Relations assuming that an Index exists on id_inv_num in both tables

USE Invoice in 0 
USE InvoiceDetail in 0 ORDER id_inv_num

SELECT Invoice
SET RELATION TO id_inv_num INTO InvoiceDetail
SCAN FOR EMPTY(InvoiceDetail.id_inv_num)
   * <do whatever>
ENDSCAN

Again NOTE - you can reverse the tables to check in the other direction.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Yes, welcome here.

There are many ways to skin a cat, so I'll add some variants.

Again the SQL way I'd rather do a left join and filter for no join:

SELECT invoice.in_inv_num FROM invoice iv;
LEFT JOIN invoiceDetail ivd ON iv.in_inv_num = ivd.id_inv_num;
WHERE ivd.id_inv_num IS NULL

This works through the nature of the left OUTER join to even add invoice rows on the left result side, if there is no invoiceDetail and instead of invoiceDetail columns add NULLs on the right result side. Therefore that WHERE is finding all non matches, exactly what you need.

Also the xBase approach could be done by checking EOF, when no row is found in invoiceDetails it means the relation will put the pointer in invoiceDetails to EOF (end of file):

USE Invoice in 0 
USE InvoiceDetail in 0 ORDER id_inv_num

SELECT Invoice
SET RELATION TO id_inv_num INTO InvoiceDetail
BROWSE FOR EOF("InvoiceDetail")

All the code of me and JRB-Bldr would need or profit of indexes on the data. The secondary examples both assume certain index naming conventions not necessarily true, namely index tag name is identical to field name. The situation could be, the indexes exist, but are differently named or the indexes don't exist at all.

If you open tables in the table designer via MODIFY STRUCTURE (for example) you can see what indexes exist and eventually add some.

I'd go for the SQL variants, as SQL is a more general solution you may later also apply to MSSQL, MySQL and other databases. But what version of Foxpro is at hand for you?

Bye, Olaf.

 

[Olaf Doschke]

Before the USE commands work (they open the dbf files), you probably need to CD into the directory of the tables, if you haven't changed default directory there already.
If tables are already open you can also click on the InvoiceDetails in the datasession window and then SET ORDER TO the index name.

Bye, Olaf.

 

[nightshade73]

Thank you both! I will give these a try. Im using FoxPro 9.0 sp1

Thanks!
Michelle

 

[Olaf Doschke]

Hi Michelle,

there is SP2 for VFP9 and it's recommended to update. If your company is like a customer of me they have rolled out the SP1 runtimes on every workstation so every VFP EXE will work. Then a change to SP2 needs preparation and a concerted effort of all inhouse developers and the workstation administration. Otherwise moving to SP2 is recommended.

You can download that at

https://www.microsoft.com/en-us/download/details.aspx?id=13959,

better then also go to the latest Hotfix, which is available at

https://vfpx.codeplex.com/wikipage?...x 3 Download (latest one)&referringTitle=Home

As seen there, you have to install SP2 first:

Prerequisites

You must have Visual FoxPro 9.0 Service Pack 2 installed to apply this hotfix.

Our code samples will work on VFP9 or VFP9 SP1, too, so no need to worry.

And finally that problem you have is not done without at least some SQL knowledege, besides that there is no single command for this so you first would need to know the concept of relations to get the solution idea at all, so that's no silly question, nothing you could have easily found in the help file or web, too, without knwing you search for a relation of two DBFs or join of two tables.

Bye, Olaf.