Normal Topic Question on Mass Udating (Read 744 times)
Larry
Member
*
Offline


No personal text

Posts: 8
Joined: Jan 22nd, 2006
Question on Mass Udating
Nov 13th, 2006 at 5:25pm
Print Post Print Post  
After translating Q&A to Sesame I noticed that one of the special characters such as ° for degree is now 0 with a slash thru it. also the " symbol changed to '. These are easy enough to change except I have over two thousand records with 123 fields each and these characters are random. meaning they could be in any field in the record. How do you program a mass update to search each field and replace in each record? I know the very old gwbasic very well but do not know sbasic. So is there another way to search and replace?
Thanks
Larry

  
Back to top
 
IP Logged
 
The Cow
YaBB Administrator
*****
Offline



Posts: 2530
Joined: Nov 22nd, 2002
Re: Question on Mass Udating
Reply #1 - Nov 13th, 2006 at 6:48pm
Print Post Print Post  
The following code run in a mass update, will replace all of the capital "A"s to lower case "b"s. You will need to change that so it changes the ASCII characters you need (work on a copy of your application!). This isn't the most efficient way to do it, but it it is illustrative of the technique.

Code
Select All
var element_list as string
var number_of_elements as int
var loop as int
var str_len as int
var inner as int
var current as string
var val as string
var out as string
var ch as string

// Gets a list of all of the elements on your form
element_list = @StringArrayElementList()

// Counts the number of elements in the list
number_of_elements = @CountStringArray(element_list)

// Loop through the elements in the list
for loop = 1 to number_of_elements

	  // Get the "current" element from the list
	  current = @AccessStringArray(element_list, loop)

	  // Make that element the current one to operate on
	  SetThisElement(current)

	  // Get its value
	  val = ThisElement

	  // Get the string length of its value
	  str_len = @Len(val)
	  out = ""

	  // Run through the string copying it to a new string, replacing the bad character ("A") with a good one ("b")
	  for inner = 1 to str_len
		    ch = @Mid(val, inner, 1)
		    if(@Asc(ch) = 65)
		    {
				out = out + "b"
		    }
		    else
		    {
				out = out + ch
		    }
	  next

	  // Set the current elements value to the new string
	  ThisElement = out
next
 

  

Mark Lasersohn&&Programmer&&Lantica Software, LLC
Back to top
IP Logged
 
Larry
Member
*
Offline


No personal text

Posts: 8
Joined: Jan 22nd, 2006
Re: Question on Mass Udating
Reply #2 - Nov 13th, 2006 at 9:03pm
Print Post Print Post  
Thanks for the quick response.
Maybe I'm not understanding the way it is done. I copied the code into the global code area and then ran the mass update (on a copy of the application). I get an error saying "var declarations not allowed to be global globally declared in the global area"
Am I putting the code in the wrong area? If so then what am I doing wrong?
Thanks
Larry
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Question on Mass Udating
Reply #3 - Nov 13th, 2006 at 9:14pm
Print Post Print Post  
Larry,

Put it anywhere other than GLOBAL CODE. Also, unless you actually want to replace "A" with "b" you also need to adjust for the substitution you want to make. As this code will attempt to "fix" every value in every record, we strongly recommend you make a backup first.  Smiley
  

- Hammer
The plural of anecdote is not data.
Back to top
IP Logged
 
Larry
Member
*
Offline


No personal text

Posts: 8
Joined: Jan 22nd, 2006
Re: Question on Mass Udating
Reply #4 - Nov 13th, 2006 at 10:24pm
Print Post Print Post  
Thanks for the help. Worked like a charm. I was on the right track just wrong area.
Larry
  
Back to top
 
IP Logged