Lookup Problem

[jmiskey]

I am starting a report from a company list database (R:PPLUSPPCOMGR.DBF) and am trying to lookup into a database within each company directory (lookup into PPERCOM.DBF for each company, i.e. R:PPLUSPPDB0005PPERCOM.DBF).____The PPERCOM database has indexes, but none that are stored in (or can be calculated from) the PPCOMGR file. However, I only need to access the first record I locate in PPERCOM for each company. And the each company code (i.e. PPDB0005) is stored in the PPCOMGR.DBF file.____First I tried setting up a Relation, where I can tell it to look at the first record only, but I am not sure how to tell it which company to look at so that it will cycle through all company (records) found in PPCOMGR. ____Alternatively, I tried using the CLOOKUP formula where I know how to build a dynamic formula to build the path to the link for the formula, but am not sure how to tell it to return just the first record instead of using an Index link.____I would prefer not to start from the PPERCOM.DBF and build a control file to run each company.____Can anyone help? I am using Version 10+.

[kfleming]

You do need an index. But what you can do is built a one size fits all index. Pick any data table and create an index called RECORD.IDX whose expression is RECNO(). This creates an index on the physical record number in the table and should we hope always have a first record whose value is one.__Then for your CLOOKUP you use 1 as the first argument, pick your return field and lookup table and then use RECORD.IDX as the related table index.____I think this should work for you.____Kathleen__R&R Support

[jmiskey]

How do I create this RECORD.IDX index? I tried in a calculated field, but that did not work.

[kfleming]

You would need Visual Foxpro or another Xbase program to do this.____Send me an email and I will send back a copy of this index file to you.____Kathleen__R&R Support__livesupport@livewarepub.com

[jmiskey]

Thank you! I received the file, and it works great!