Case Convert Operation

[Ted Giles]

I am attempting to save time with an existing Form by running a Script to convert the fields to Upper Case on the Table.
The table is SQL and a mixture of Char, Numeric and Memo.
The Form is a tabbed layout and the script should run OnSave.

Building an Operation to create the Script code works on a simple Active Link Table properly.

Building an Operation on a large table shows that the Fields are selected and moved into the correct box, but the code has a lot of bits missing when examined.

I tried the long hand method to try and convert 2 fields and it killed AA completely.
So, question. How to update all fields in an SQL Active Link Table to Upper Case - On Save?

[Stan Mathews]

Re: Case Convert Operation

I would think it would be simpler to loop through the fields on form and convert the values to uppercase in the cansave event.

See how this works as the cansave event code.

lst = topparent.Child_Enum("r;f=field")
for each chld in lst
	typ = eval("typeof("+chld+".value)")
	if typ = "C" .or. typ = "M"
		eval(chld+".value") = upper(eval(chld+".value"))
	end if
next

And there are form level field rules that might do just as well.

[Al Buchholz]

Re: Case Convert Operation

You could use xbasic to(or manually) build an SQL update statement to update all the rows and fields and run that directly through an PHP admin or SQL control.

Then use Stan's idea for new rows or use field options to upshift.

[Ted Giles]

Re: Case Convert Operation

Thank you gentlemen.
Stan, I will try the code as soon as I get back into the application.
My concern is that 160 or so fields being tested and updated through SQL might slow things down.
The form level field rules will always work fine. It was a case of saving a bit of time, but it's probably better to do it at the field level on the Form as I would do normally.

Al, we are looking at using SQL (Upper), depending on the data import. At the field level, we get rid of a load of back end work and it will be consistent.

Just a bit shocked that the Operation let me down.

[Al Buchholz]

Re: Case Convert Operation

Building an Operation on a large table shows that the Fields are selected and moved into the correct box, but the code has a lot of bits missing when examined.

I'm not sure what this means...

[Ted Giles]

Re: Case Convert Operation

I will post a pic tomorrow.
But, select Operation, Change Case, Pick the Table, Select the fields, Move them over, Run.
SQL works once.

[Al Buchholz]

Re: Case Convert Operation

I will post a pic tomorrow.
But, select Operation, Change Case, Pick the Table, Select the fields, Move them over, Run.
SQL works once.

Are you using the operation genie?

[Ted Giles]

Re: Case Convert Operation

Yep!
And tried editing the Xb

[Stan Mathews]

Re: Case Convert Operation

This should process the entire table, not sure whether FILE_RW_EXCLUSIVE or .BATCH_BEGIN()/.BATCH_END() are appropriate with active link tables.

tbl = table.open("a")
flds = tbl.Field_Info_Dump("n,t")
cflds = ""
for each fldfnd in flds
	if word(fldfnd.value,2,",") = "C" .or.  word(fldfnd.value,2,",") = "M"
		cflds = cflds+word(fldfnd.value,1,",")+crlf()
	end if
next
to_update = line_count(cflds)
tbl.fetch_first()
while .not. tbl.fetch_eof()
	tbl.change_begin()
	for qx = 1 to to_update
		eval("tbl."+word(cflds,qx,crlf())) = upper(eval("tbl."+word(cflds,qx,crlf())))
	next
	tbl.change_end()
	tbl.fetch_next()
end while
tbl.close()

Or as an operation, self writing.

a_tbl = table.open("a")
flds = a_tbl.Field_Info_Dump("n,t")
cflds = ""
for each fldfnd in flds
	if word(fldfnd.value,2,",") = "C" .or.  word(fldfnd.value,2,",") = "M"
		cflds = cflds+word(fldfnd.value,1,",")+crlf()
	end if
next
to_update = line_count(cflds)
query.options = "I"
query.description = "Temporary Query"
i_indx = a_tbl.query_create("N")
update.fields = to_update
for qx = 1 to to_update
eval("update.field"+alltrim(str(qx))) = word(cflds,qx,crlf())
eval("update.expr"+alltrim(str(qx))) = "upper("+word(cflds,qx,crlf())+")"
next qx
a_tbl.update()
a_tbl.close()

[Ted Giles]

Re: Case Convert Operation

Looks good Stan. I will test tomorrow. Getting late here.
Many thanks.