Updating multiple records from script

[Sooty57]

Sorry about asking a dumb question (again!!!), but I have been struggling with this for hours without success.

I have a dialog box where the end user selects a current value from a table, with another input field to enter a replacement value. When the user clicks the OK button I want all records with the value in the dropdown to be changed to the value in the input field.

For example:

Table1 has a field called "Name"
The user selects "Bill" in the dropdown and types "William" in the input box.
When the user clicks OK all instances of "Bill" in the "Name" field of all records are changed to "William"
The table has about 70k records

[MoGrace]

Re: Updating multiple records from script

If your dropdown was bound to a variable which is populated from your table, and your input field is also a variable then you have the scenario you need for an update operation. If you can create an Update operation using variables in the filter, you can run that update in your form by first passing your layout variables to global variables which the Update uses.

[Sooty57]

Re: Updating multiple records from script

If your dropdown was bound to a variable which is populated from your table, and your input field is also a variable then you have the scenario you need for an update operation. If you can create an Update operation using variables in the filter, you can run that update in your form by first passing your layout variables to global variables which the Update uses.

Thanks Mo. I understand this, but I am having trouble with the actual process of updating the records.

I have used the while .NOT. tbl.fetch_eof() to loop through the records, but cannot make the If() statement work so that only those records that match the variable are updated.

[MoGrace]

Re: Updating multiple records from script

We seem to be talking about two different things. If you are wanting to do this in an XBasic script while the current table is open, then it seems you would have to filter the records first which the OnChange event of the dropdown can achieve. Then your script can fetch thru the current records in that query without an IF() statement, to do its change_begin() routine. A look at your script would be nice. ;)

[Mike Wilson]

Re: Updating multiple records from script

try this:

dim vtable_value as C="Bill"              
dim vnew_value as C

dim vresult as C
vresult=ui_dlg_box("Update",<<%dlg%
{font=arial,10}{lf};
Table Value:|[.20vtable_value];
New Value:|{initial_focus}[.20vnew_value];
<10OK> <10Cancel>
%dlg%)

if vresult="Cancel"
	end 
end if

' dim vatriables 
	dim cycle as N=0
	dim vrecs as N
	dim vfilter as C

' filter for the table
	vfilter="[COLOR="Red"]your_field_name[/COLOR]="+quote(vtable_value)

'count records in table matching filter for wait dialog
	vrecs = TABLECOUNT("[COLOR="red"]YOURTABLENAME[/COLOR]",vfilter)
	cycle = 1
	DIM GLOBAL pDlg1 as {waitdialog}
		pDlg1.Set_Title("Please Wait")
		pDlg1.Create(1,"Percent")
		pDlg1.Set_Message("")
		pDlg1.Set_Bottom_Message("")
		pDlg1.Set_Percent(Var->cycle,Var->vrecs)

	'open table filtered and loop through changing records
	dim t as P
	t=table.open("[COLOR="red"]yourtablename[/COLOR]")
	xi=t.order("",vfilter)
	t.fetch_first()
	while .not. t.fetch_eof()
		t.change_begin()
			t.[COLOR="Red"]your_field_name[/COLOR]="+=wut(lt(alltrim(vnew_name)))
		t.change_end(.t.)
		
		cycle=cycle+1
		pDlg1.Set_Percent(Var->cycle,Var->vrecs)
	t.fetch_next()
	end while
	t.close()
	pDlg1.close()

[Sooty57]

Re: Updating multiple records from script

The table is not open in a browse or form. The routine will run from a button on toolbar.

The information is very sensitive, so I will create a dummy database to upload.

Just discovered Mike's reply as I was posting....... will give that a go first.

[Mike Wilson]

Re: Updating multiple records from script

One small change for clarity:

.........

t.change_begin()
     t.[COLOR="Red"]your_field_name[/COLOR]="+=wut(lt(alltrim(vnew_name)))
t.change_end(.t.)

.......

[Sooty57]

Re: Updating multiple records from script

One small change for clarity:

.........

     t.[COLOR="Red"]your_field_name[/COLOR]="+=wut(lt(alltrim(vnew_name)))

.......

This line gives script error (expected value) when testing syntax

[MoGrace]

Re: Updating multiple records from script

The dimmed variable was vnew_value. But I don't understand the purpose in combining WUT() with LT() and ALLTRIM() since that is what this function is supposed to do. I changed it to UT(vnew_value) and that worked.

t.Lastname=ut(vnew_value) 'which was the name of the field I made.

Mike - that was a nice example for a wait dialog - thanks!

[Sooty57]

Re: Updating multiple records from script

Modified as per Mo's suggestion and all worked fine.

Just a quickie though..... Why is the new name in the field all uppercase?


OOps - scrub that question ........ UT()

[Mike Wilson]

Re: Updating multiple records from script

The mistake was a cut and paste error. I like to use wut(lt(value)) so that ONLY the first letter is capped. If you type in aBCde, the result of WUT() will be ABCde. So I like to make all lower case first and then cap the first letter only for uniformity.

Glas it worked out.

[MoGrace]

Re: Updating multiple records from script

Interesting that the bubble help in the code editor for the WUT() function does not say that! At least not in v8. It does however say that in the CHM file.

[preston2]

Re: Updating multiple records from script

What changes would I need to make to have this work on a numeric value field. I tried changing the code but kept getting mismatch errors. Alpha 5 V 10.5

I need to change one field in a table from every instance of .30 and change it to 30. The table has 1700 records in it so I really do not want to do this manually if possible. I already looked into the operations search and replace but that only works for character fields.

Thanks.

[Ted Giles]

Re: Updating multiple records from script

Preston.
Backup the Table
Update Records Operation - Numeric - assign a specified value
Update Records selected by a Filter Expression. In your case it would be .30.

Make sure that the filed is big enoough to take 30.00 so it needs to be N5 at least

[preston2]

Re: Updating multiple records from script

Preston.
Backup the Table
Update Records Operation - Numeric - assign a specified value
Update Records selected by a Filter Expression. In your case it would be .30.

Make sure that the filed is big enoough to take 30.00 so it needs to be N5 at least

Thanks a bunch.

I looked right past that before and did not even see it.

I think that will give me what I need to add a update button in my app so the end users can change the value in all the records if needed. The field is a 5/2 and is used for a base percentage markup on items in a product catalog.

I tell you, with the help of the generous people here, the wiki and the books, I am starting to understand Alpha 5 a lot better.

Again, thank you.