update.fields does not change value via lookup

[evacc44]

Quick summary of what I am trying to do:

I have two fields in my database (Script_Num and Script_Name). They are type numeric and character, respectively.

The database has a lookup set to Script_Num that changes the value of Script_Name depending on what numeric value is stored in Script_Num. For example, if Script_Num = 1, then Script_Name is changed via lookup to be a certain character value.

That has worked fine until I wrote a script that changed Script_Num from one value to another in batch (that's the code below). It seems that the update expression does to initiate the lookup to change the value of Script_Name based off the new value of Script_Num.

So in other words, the Script_Num lookup does not run on Script_Name if I batch change the values of Script_Num via update.

Is this just not how Alpha works or am I missing something here?

this.Disable()
dim s_num as C	

s_num=topparent:FIELD1.value

tbl=table.open("pricelist")

Query.filter="C_Price>0 .and. Script_num=0"
Query.order=""
Query.options="M"
qry = tbl.query_create()

nrecs = qry.records_get()
IF (nrecs > 0) then
	update.fields = 1
	update.field1 = "Script_num"
	update.expr1 = s_num
	'update.expr1 = "'"+reason+"'"
	tbl.update()
end if
qry.drop()
tbl.close()
ui_msg_box("Warning",str(nrecs)+" records have been updated!",UI_OK)
topparent.Close()
Query.filter=""
Query.order=""
Query.options=""

this.Enable()

[Stan Mathews]

Re: update.fields does not change value via lookup

A field rule lookup is an aid to manual data entry. You are correct. It does not execute in response to a script/operation.

You can change your update to include the lookup expression.

[evacc44]

Re: update.fields does not change value via lookup

How would I change my update to include the lookup expression? Thanks!

[Stan Mathews]

Re: update.fields does not change value via lookup

Not having seen your database, something like...........

IF (nrecs > 0) then
	update.fields = 2
	update.field1 = "Script_num"
	update.expr1 = s_num
	update.field2 = "Script_name"
	update.expr2 = [COLOR="#FF0000"]your_lookup_expression[/COLOR]
	tbl.update()
end if

[evacc44]

Re: update.fields does not change value via lookup

Sorry for the late response on this. I have not been on site since asking the question.

As a follow-up, is there any way to force a lookup on an entire table?

For example:

I have two tables (address and repairs) and two forms (using the previous tables, respectively). The only field they share is company_name. Both tables have an email field, but the emails are only stored in address's email field. While the other table has the email field, it is a new field and contains no data. I want to match each repairs record (via company_name) to address and populate the email fields.

Is lookup the best way to do this? If so, how can I run a lookup on an entire table?

[Stan Mathews]

Re: update.fields does not change value via lookup

A posting operation, probably.