Converting integer field to character field

[D.Cook]

We are going through the process of converting an ID field to allow character values in it. So basically converting it from an integer field to a character field.

Firstly, has anyone been through this before and have any tips or advice about potential less obvious problems?

And secondly, Is there any way to right-align a character field? This field used to be displayed in a browse as right-aligned (eg with format ">>>>>>>>>9"). But now it will be displayed left aligned (eg with format "x(10)"). If there's one thing I know about users (and there probably is only one thing), it's that they don't like change!

 

[RealHeavyDude]

You can not change the data type of a field. The only way to change it "seamlessly" is to delete the field and add it again with another data type - so there is no conversion in the database. Of course, deleting and adding the field might not be a suitable solution as you will lose the data. Therefore there are several possibilities, but I usually dump the table with a customer logic and load it again after the change has taken place. Don't know how big your table is and whether that is suitable for you. Of course you could just add the field with a different name - copy the value from the original field, delete the original field and then rename the field.

Regarding the right-alignment: I am not aware of an "easy" solution that would not involve a complex logic in your presentation layer - maybe somebody else knows.

Heavy Regards, RealHeavyDude.

 

[LarryD]

As to the right alignment, the code would be (and you could set it into a function or whatever):

FILL(" ",10 - LENGTH(TRIM(myfield))) + TRIM(myfield)

A few options:

- add another field in your db that is the right adjusted value, use the code above in either a trigger or some other code to set it every time you change the actual db field and then display the new field instead of the db field. Ugly, but it would work.

- For dynamic browses, I have no idea.

However, depending on whether the browse is from a temp-table or a db table, you could:

- temp table: have a field to display the right adjusted value in the tt, and use the code above to set this value from the db table or other data source.

- db table: def a temp char variable, use that in the browse. Then add a FIND trigger (on FIND of dbtable) or I think perhaps a row-display trigger (someone probably has a better solution than this) to set and/or display the above fill statement/function in the temp char variable.

Note that if it's an updateable browse, you may have to do some coding to redisplay the value after the update.

Others may have better/more elegant solutions

 

[D.Cook]

Thanks for the input guys.
Yep we will be doing a dump and load, there will be other database changes so it's easier that way.

Thankfully we're generally pretty good at using 'like' in variable definitions, although sadly there's still hundreds of code changes anyway..

Larry thanks for the code snippet, yep generally the browse is using db data but not updateable, so a row-display trigger sounds like a great idea. Have just tried it out with code like this:

on row-display of browse b-{&file}
   {&file}.pe_id:screen-value in browse b-{&file} = fill(" ", {&field_width} - length(trim({&file}.myfield))) + trim({&file}.pe_id).

It's looking good, thanks!

 

[tamhas]

Using LIKE is not actually a good practice because it creates DB dependencies in code that might otherwise not have them.

I would consider the right align issue with some care. While left align will be a switch for the users, there is a switch happening anyway since you are now allowing non-numeric data in the field. Editing a right aligned character field is non-intuitive and is going to require some fancy coding on your part.

Note that one way to make the switch is to add a character field, run code to copy the values, then delete the integer field and redo the indices. That is likely to be faster than a dump and load and means that your changes are always under ABL transaction control.

 

[D.Cook]

Hmm I think I see your point about DB dependencies. Perhaps instead of LIKE, using a global preprocessor directive might be a better option (in theory). Eg

DEFINE VAR myvar AS {&myvar_type}.
/*or maybe just*/
{defmyvar.i}

But our application is dependent on the DB in just about any way I can think of, so I don't see this as an issue. In fact, I don't know how an application could be independent of the database.

Regarding display format, this is purely for browses (which will not be editable), all other fill-ins will just be default format. So I don't see it as an issue either.

I have now realised that instead of an ON ROW-DISPLAY trigger, LarryD's snippet can just go in the static browse definition, which is a bit cleaner. I'm not sure if it changes the way the AVM processes it or if performance is any different, but cleaner code is still better

fill(" ", {&field_width} - length(trim({&file}.myfield))) + trim({&file}.myfield)
     @ {&file}.myfield