Change a record

[cotsia]

i have a form that is based on set with one to many relationship, the set consists of five tables. what i would like to do is convert metric units to imperial units. the velocity_units_id and the pressure_units_id are the fields on the parent table that i would like to change, these two fields get their values from another table (by a lookup table), for example the velocity can either be ft/sec(id:004) or m/sec(id:005). on the table i need the id to be shown but on the form i need the user to see the text so i 've created dynamic text box. i also have a settings button where i call another form and the user can choose the units that he wants to use. my default units system is the metric and when i change to imperial except the change in the fields velocity_units_id and pressure_units_id in the tbl_02_batch i want to recalculate the fields velocity and pressure in the table link_02_test_rounds. i used action script the xbasic change record action to change the records on the table but it only works for the first record and for the tbl_02_batch..

pic1.png
form1.png
form2.png

thanks
Costantina

[Tom Cone Jr]

Re: Change a record

Sorry. Not familiar with the following notation.

velocity can either be ft/sec(id:004) or m/sec(id:005)

What might that mean?

[Tom Cone Jr]

Re: Change a record

Can you explain this further?

...on the table i need the id to be shown but on the form i need the user to see the text

There may be no reason to change the actual field values in your table. Couldn't you use a calc display value in your form to display the desired results depending on the user's units of measure preference?

[cotsia]

Re: Change a record

Tom i was just giving an example of my data.. on my parent table the id number will be shown and on the form the user will see the text (m/sec).. i need to show the id number on the table because this db is going to be connected with another software that will give me the units and other informationt, and if the user wants to change the units they should be changed in the table or the software on the machine want make any changes..

[Tom Cone Jr]

Re: Change a record

Still having trouble understanding your goal.

Please show us a sample of the actual data in each field, and then show us how you would want to change it for each entry.

[cotsia]

Re: Change a record

parent table.pngtest rounds table.pngunits table.png

as you can see at the parent table i have the ids of the units that i need, the values i get from the unit table with lookup table.
if i have a metric system then the ids are going to be 004 and 009 and for the imperial system 005 and 011..
and on the test round table everything multiplies with a certain number so the values will change (if the fields are already filled) according to the system units. the multipliers are also shown in the units table

thanks for your time and excuse my english

[Tom Cone Jr]

Re: Change a record

Thanks.

I'm still not understanding. Sorry to be so slow.

Can you supply a list of values in metric, and then show us how you would want them changed if the user selects imperial? Please show before and after.

Please also tell us what specific range of records you would want to change.

[cotsia]

Re: Change a record

book1.png

the multiplier will be used only on the values at the test round table

[Stan Mathews]

Re: Change a record

i used action script the xbasic change record action to change the records on the table but it only works for the first record and for the tbl_02_batch..

Edit the button containing the xbasic change record action. Right click on the action and choose View xbasic. Copy the xbasic and include it in a response here.

The xbasic change record action is designed to affect one record. We can modify that to loop through all matching records.

[cotsia]

Re: Change a record

'Open the table that you want to update...
dim tbl as p 
tbl = table.open("tbl_02_batch",FILE_RW_SHARED)
'Find the record that has to be edited
query.filter = "batch_id = Var->vBatchId"
query.order = ""
query.options = ""

DIM itbl as p 
itbl = tbl.query_create()
DIM recs as n 
recs = itbl.records_get()   'get the number of records found by the query
if recs = 0 then 
	ui_msg_box("Error","Record to update not found. Could not find record that matches filter expression: '" + query.filter+"'.",UI_STOP_SYMBOL)
	tbl.close()
	end 
else if recs > 1 then 
	ui_msg_box("Error","More than one record was found that matches the filter expression. No records were updated.",UI_STOP_SYMBOL)
	tbl.close()
	end 
end if

if tbl.is_record_locked() = .t. then 
	ui_msg_box("Error","Cannot edit record because it is locked by another user, or in another session.",UI_STOP_SYMBOL)
	tbl.close()
	end 
end if 

tbl.change_begin()
tbl.VELOCITY_UNITS_ID = "005"
tbl.PRESSURE_UNITS_ID = "011"
tbl.change_end(.t.)
tbl.close()

this is the code if i choose the imperial system i have a condition to every action. this code is to make a change on the parent table

[cotsia]

Re: Change a record

'Open the table that you want to update...
dim tbl as p 
tbl = table.open("link_02_test_rounds",FILE_RW_SHARED)
'Find the record that has to be edited
query.filter = "batch_test_set_id = Var->vBatchTestSetId"
query.order = ""
query.options = ""

DIM itbl as p 
itbl = tbl.query_create()
DIM recs as n 
recs = itbl.records_get()   'get the number of records found by the query
if recs = 0 then 
	ui_msg_box("Error","Record to update not found. Could not find record that matches filter expression: '" + query.filter+"'.",UI_STOP_SYMBOL)
	tbl.close()
	end 
else if recs > 1 then 
	ui_msg_box("Error","More than one record was found that matches the filter expression. No records were updated.",UI_STOP_SYMBOL)
	tbl.close()
	end 
end if

if tbl.is_record_locked() = .t. then 
	ui_msg_box("Error","Cannot edit record because it is locked by another user, or in another session.",UI_STOP_SYMBOL)
	tbl.close()
	end 
end if 

tbl.change_begin()
tbl.VELOCITY_1 = a5_eval_expression("=Link_02_Test_Rounds->Velocity_1*3.28084",local_variables())
tbl.VELOCITY_2 = a5_eval_expression("=Link_02_Test_Rounds->Velocity_2*3.28084",local_variables())
tbl.MAX_PRESSURE = a5_eval_expression("=Link_02_Test_Rounds->Max_Pressure*14.50377377",local_variables())
tbl.MOMENTUM = a5_eval_expression("=Link_02_Test_Rounds->Momentum*0.2248089431",local_variables())
tbl.PRESSURE_INTEGRAL = a5_eval_expression("=Link_02_Test_Rounds->Pressure_Integral*14.50377377",local_variables())
tbl.change_end(.t.)
tbl.close()

and this is the code to change the records in the tbl_02_test_rounds

[Stan Mathews]

Re: Change a record

This should process all the records found. suggest you test it thoroughly. Create another button, add an action - inline xbasic, paste this as the xbasic.

'Open the table that you want to update...
dim tbl as p
tbl = table.open("tbl_02_batch",FILE_RW_SHARED)
'Find the record that has to be edited
query.filter = "batch_id = Var->vBatchId"
query.order = ""
query.options = ""

DIM itbl as p
itbl = tbl.query_create()
DIM recs as n
recs = itbl.records_get()   'get the number of records found by the query
if recs = 0 then
	ui_msg_box("Error","Record to update not found. Could not find record that matches filter expression: '" + query.filter+"'.",UI_STOP_SYMBOL)
	tbl.close()
	end
	'else if recs > 1 then [COLOR="#FF0000"]'commented out this section since we do want to process multiple records[/COLOR]
	'	ui_msg_box("Error","More than one record was found that matches the filter expression. No records were updated.",UI_STOP_SYMBOL)
	'	tbl.close()
	'	end
end if

if tbl.is_record_locked() = .t. then
	ui_msg_box("Error","Cannot edit record because it is locked by another user, or in another session.",UI_STOP_SYMBOL)
	tbl.close()
	end
end if
[COLOR="#008000"]for qx = 1 to recs[/COLOR]
	tbl.change_begin()
	tbl.VELOCITY_UNITS_ID = "005"
	tbl.PRESSURE_UNITS_ID = "011"
	tbl.change_end(.t.)
[COLOR="#008000"]next qx[/COLOR]
tbl.close()

[cotsia]

Re: Change a record

thanks Stan for your help..
but i' ve tried it and it doesn seem to work..
don't get it i just want to change the matching records..
could the query be the error?

query.filter = "batch_id = Var->vBatchId"
query.order = ""
query.options = ""

[Stan Mathews]

Re: Change a record

Sorry. My bad. Should be

for qx = 1 to recs
tbl.change_begin()
tbl.VELOCITY_UNITS_ID = "005"
tbl.PRESSURE_UNITS_ID = "011"
tbl.change_end(.t.)
tbl.fetch_next()
next qx

[cotsia]

Re: Change a record

nope still the same i might be on record number 3 and it changes record number 1.
i' ve also try tbl.fetch_first() and still nothing