Change Field Type

[DickJames]

We have inadvertantly installed a table with a field having type numeric when in fact we would like to use it as type logical. In the control panel, it is easy to go in and either delete the field & replace it with another, or simply change the field type.

Is there a way to do this programmatically using Xbasic?

-- Dick James

[MikeC]

Re: Change Field Type

table.restructure()

http://support.alphasoftware.com/alp...TRUCTURE().htm

[Stan Mathews]

Re: Change Field Type

One way

TABLE_RESTRUCTURE()

Another

A5_DEL_FIELDS_FM_TABLE() followed by A5_ADD_FIELDS_TO_TABLE()

[DickJames]

Re: Change Field Type

Thank you gents. Instructions for table_restructure() are rather brief. Must all fields in a table be named, or only the ones being changed?

[MikeC]

Re: Change Field Type

Backup first of course anytime you are doing procedures that could destroy all data or mess up anything really.

You have to list in a string ALL fields as it basically removes all and then places back the fields---whatever is in the string will be what you end up with.

[Stan Mathews]

Re: Change Field Type

You can obtain a crlf() delimited list of the current field definitions like

c = table.open("yourtablename",FILE_RW_EXCLUSIVE)
lst = table.fields_get()
nm_lst = c.Field_Info_Dump("n,t,W,D")

[DickJames]

Re: Change Field Type

So if you are removing and then placing back all the fields, is the data in the unchanged fields unchanged? (I'm assuming the data in the fields that are changed will be lost.)

-- Dick James

[Stan Mathews]

Re: Change Field Type

Nothing is lost unless you change something that can't be converted.

Changing Field Types and Sizes

When restructuring a table that has data in it, you must be careful to avoid data loss. Data might be lost by changing a field�s definition. For example, you might lose data if you change a field's width to a smaller value. Characters that do not fit into the new width are truncated. Data might also be lost if you change a field�s type, as summarized below:

Character to:
Result:

Numeric
Converts valid numbers appearing before any other characters in the field (e.g. 100xyz is converted to 100). Retains decimal places if you specify decimal places for the numeric field. If you do not specify decimal places, values are rounded to the nearest whole number. Converts other data (including numbers with currency signs) to 0. Converting numbers with thousands separators isn't supported.

Date
Converts valid dates entered in the format MM/DD/YY, MM/DD/YYYY, MM-DD-YY, or MM-DD-YYYY appearing before any other characters in the field.

Logical
Converts the characters T, t, F, f, Y, y, N, or n appearing first in the field to a True or False value. All other characters return a False value.

Memo
Transfers all characters stored in the Character field to the Memo field.




Numeric to:
Result:

Character
Retains all values.

Date
Converts to NULL (blank)

Logical
Converts values equaling zero to F (false). Converts values greater or less than zero to T (true).

Memo
Retains all values.




Logical to:
Result:

Character
Retains all values.

Numeric
Converts to NULL.

Date
Converts to NULL.

Memo
Retains all values.




Date to:
Result:

Character
Returns values in the format YYYY/MM/DD. For example, 09/20/2001 converts to 2001/09/20.

Numeric
Converts to NULL.

Logical
Converts to NULL.

Memo
Retains all values.




Memo to:
Result:

Character
Retains characters that fit in the field. For example, a field that's 40 characters wide accepts the first 40 characters of memo text.

Numeric
Converts valid numbers appearing before any other characters in the field. For example, 100xyz is converted to 100. Retains decimal place digits if you specify decimal places for the numeric field.

Converts other data (including numbers with currency signs) to 0. Converting numbers with thousands separators isn't supported.

Date
Converts valid dates in the format MM/DD//YY, MM/DD/YYYY, MM-DD-YY, or MM-DD-YYYY appearing before any other characters.

RTF Memo
Transfers all characters stored in the Memo field to the RTF Memo field.

Logical
Converts the characters T, t, F, f, Y, y, N, or n located in the first position in the field to a True or False value. All other characters return a False value.

All other values return an uppercase F.




RTF Memo to:
Result:

Character
Retains characters that fit in the field. However, text formatting and font is not retained.

Numeric
Converts to NULL (blank).

Memo
Transfers all characters stored in the Memo field to the RTF Memo field. However, text formatting and font is not retained.




Image File Reference to:
Result:

Character
Displays text filename and path to image. For example:

=filename_decode("[exe_folder]\Samples\Alphasports\hotbar.bmp")

Memo
Displays text filename and path to image. For example:

=filename_decode("[exe_folder]\Samples\Alphasports\hotbar.bmp")

[MikeC]

Re: Change Field Type

Hi Stan,
Any info/knowledge on exponentnumeric(from an Excel file) as far as when converted to numeric??

[DickJames]

Re: Change Field Type

Stan --

That is really helpful. I wish the Alpha help files contained that sort of explanation. Thanks for your help.

-- Dick James

[Stan Mathews]

Re: Change Field Type

Stan --

That is really helpful. I wish the Alpha help files contained that sort of explanation. Thanks for your help.

-- Dick James

That is copied and pasted directly from the help files.

[Stan Mathews]

Re: Change Field Type

Hi Stan,
Any info/knowledge on exponentnumeric(from an Excel file) as far as when converted to numeric??

I don't even know what an xponentnumeric field might be.

[DickJames]

Re: Change Field Type

Stan --

That is copied and pasted directly from the help files.

My bad. I was looking at the TABLE_RESTRUCTURE() command, and I missed the link to Restructuring Tables that was shown at the bottom. Thanks again.

-- Dick James