Hi Mohammed,

you sent me the form with your problem. Please don't do that again, especially not with data in the magnitude of about a megabyte of confidential customer data! I erased that.

From what I remember about the form: You choose two fields of those tables and now want to do what? Search the first selected field in of table1 in the second selected field of table2? Normally is does not make sense to search any field of one table in any other field of another table. For example it's nonsense to search a bill number of a bill in a birthday field of a contact record. Although I had the form and data, it was not self-explanatory what this form is meant to do.

You normally have defined relations by primary and foreign keys stored in certain fields. And it only makes sense to relate these fields.


Now let us take a look at your original replace:

replace IN contact ;
for cc==LOOKUP(bill.cc,cc,bill.cc) AND NOT EOF('bill') ;
val with VAL(bill.bill_value)

This searches contact.cc in the bill.cc field of the bill table and checks, if there is a corresponding record. So it's really ....contact.cc = lookup(bill.cc,contact.cc, bill.cc). If cc is found you replace contact.val with VAL(bill.bill_value). This is just a tricky way to simulate a seek without an index. It would be much simpler with indexes.

But to stay with lookup, I'd put this replace this way:

replace in contact ;
val with val(lookup(bill.bill_value,contact.cc,bill.cc)) ;
for not eof('bill')

Now you lookup the bill_value field by searching contact.cc in the bill.cc field, which is a much more natural use of the lookup() function. And "not eof('bill')" ensures, you only replace those contact.val, where you really find contact.cc in bill.cc.

Maybe this helps you to see, where you need to make your changes.

Bye, Olaf.