I have a customer running a very old version of SBT ProSeries. This particular version uses all VFP Free tables. The issue I'm having is trying to create a view that contains each inventory item from the icitem table and information from the inventory suppliers table (icisupl) and the vendor name. The inventory suppliers table contains one record for each item number and vendor number combination. So there could be multiple records for each item in the suppliers table. For this reason they want only the vendor number of the vendor they purchased from last. In addition to this they also want the vendor company name that is not stored in the inventory supplier file and must be retrieved from the vendor table (apvend). I was able to retrieve the vendor number of the last supplier by using a subquery. This was only after I hard coded the value for the where clause. So what it returned was the same value for every inventory item. What I'm struggling with is how to use this subquery but use a variable for the where clause and not a hard coded value. Then the next hurdle is how to join this returned vendor number to the appropriate record in the apvend (vendors) table so I can retrieve the vendors company name. I'm assuming a subquery is the best approach I'm just not sure how to go about it. If this is not the best approach please let me know.

icitem - Inventory
icisupl - Inventory Suppliers
apvend - Vendors


TIA

Jeff Hibbs
The Blencowe Group