Lookup() expression in update operation

[Bill Parker]

The lookup is not getting the correct value in the filter expression, so the returned value is always blank.

a_tbl=table.open("import2")
update.fields = 1
update.field1 = "PERSON_ID"
update.expr1 = "lookup(\"audit_temp\",\"ID=quote(import2->Offense_Id)\",\"person_id\")"
a_tbl.update()

In the above code I have prefixed the filter value with table name just to make clear what the source is supposed to be.

One solution is to use lookupc vs. lookup.
"lookupc(\"F\",Offense_Id,\"person_id\",\"audit_temp\",\"Cfi\")" works

But originally the filter was more complex and there was no matching index. So I am still curious if I messed up in the lookup() expression.

Bill.

[Stan Mathews]

Re: Lookup() expression in update operation

Can you try it as

update.expr1 = "lookup(\"audit_temp\",\"ID=\"+quote(import2->Offense_Id),\"person_id\")"

[Bill Parker]

Re: Lookup() expression in update operation

Hi Stan,

That won't work because I want each record in the update to lookup their own offense_id. So the lookup needs to say - take the current offense_id and look it up in audit_temp, then return the person_id. Then do same thing for next record which will have a different offense_id.

Bill.

[Stan Mathews]

Re: Lookup() expression in update operation

Works for me. Isn't it essentially the same thing as not quoting the key value in the lookupc()?

[Bill Parker]

Re: Lookup() expression in update operation

But that is a single value that would apply to every record in the update. Since the update is on import2.dbf, import2->Offense_Id needs to change for each record that is updated.

[Stan Mathews]

Re: Lookup() expression in update operation

Works for me.

Try this in AlphaSports.

Add a "TTL" field to the product table, numeric, 12,2.

Run this (if the script used one static product_id then you would get the same number in each record in product, it it uses the product_id of the current record you would get a different number)

a_tbl = table.open("product")
update.fields = 1
update.field1 = "TTL"
update.expr1 = "lookup(\"product sales summary\",\"product_id = \"+quote(product_id),\"invoice_items__extension\")"
a_tbl.update()
a_tbl.close()

The update.expr1 gets evaluated for each record, not once at the start of the update.

[Bill Parker]

Re: Lookup() expression in update operation

Stan, you have the patience of Job. I failed to catch the diference in your first suggestion (not that it is that hard to see). My brain said there was an extra ", i.e. \"ID=\"', which is how I got onto the static value thing.

'update.expr4 = "lookup(\"audit_temp\",\"ID=quote(import2->Offense_Id)\",\"person_id\")"
vs.
update.expr4 = "lookup(\"audit_temp\",\"ID=\"+quote(import2->Offense_Id),\"person_id\")"

Thanks for sticking with me.

Bill.

[Stan Mathews]

Re: Lookup() expression in update operation

That's ok. I was beginning to think I must be misunderstanding the difference between my suggestion and what you actually wanted.