rename or rename the fields of a table

Primary tabs

7 posts / 0 new
Log in or register to post comments
Last post
Tue, 05/09/2023 - 10:25
#1
ortizmario6025@...
Offline
Joined: 09/03/2021 - 11:54
rename or rename the fields of a table

greetings,
How can I change the names of the fields in a table to be able to export it in excel. Is there any script that can help me.
Thank you

Top
Wed, 05/10/2023 - 15:36
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

The easiest way is to just rename a field in your database and then go to the history and grab the IDEAScript code.

Just curious why you need to change the field names for the Excel database?

Here is an example of changing a character fields name:


Set db = Client.OpenDatabase("General Ledger-GL-Database.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "ACCOUNT_NO"
field.Description = ""
field.Type = WI_CHAR_FIELD
field.Equation = ""
field.Length = 5
task.ReplaceField "ACCOUNT_NUM_FECHA", field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
Top
Wed, 05/10/2023 - 15:58
ortizmario6025@...
Offline
Joined: 09/03/2021 - 11:54

I am interested in changing it, since the table comes with a name that the end user does not know how to interpret well, therefore I have to export it to excel with a name that is familiar in order to interpret it.

Top
Thu, 05/11/2023 - 07:04
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

That makes perfect sense, thanks for letting me know.  If you want to do multiple fields you can do something like this:


Set db = Client.OpenDatabase("General Ledger-GL-Database.IMD")
Set task = db.TableManagement

'chang field 1
Set field = db.TableDef.NewField
field.Name = "FIELD1"
field.Description = ""
field.Type = WI_CHAR_FIELD
field.Equation = ""
field.Length = 5
task.ReplaceField "FIELD11", field
task.PerformTask

'chang field 2
Set field = db.TableDef.NewField
field.Name = "FIELD2"
field.Description = ""
field.Type = WI_CHAR_FIELD
field.Equation = ""
field.Length = 5
task.ReplaceField "FIELD22", field
task.PerformTask

'chang field 1
Set field = db.TableDef.NewField
field.Name = "FIELD3"
field.Description = ""
field.Type = WI_CHAR_FIELD
field.Equation = ""
field.Length = 5
task.ReplaceField "FIELD33", field
task.PerformTask

Set task = Nothing
Set db = Nothing
Set field = Nothing

You need to run the PerformTask after each one, if you don't there is a bug and the last field is always really wide for some reason.  I don't think they have fixed it because we have the work around.

You can also do this as a loop to save on coding if you want.

Top
Thu, 05/11/2023 - 07:59
ortizmario6025@...
Offline
Joined: 09/03/2021 - 11:54

Good

Top
Fri, 05/12/2023 - 02:31
klmi
Offline
Joined: 02/13/2019 - 08:41

If multiple fields are renamed I would prefer an extra Sub or Function like that:
 

Sub RenameField(sOldField As String, sNewName As String)
Set db = Client.CurrentDatabase()
Set task = db.TableManagement
Set oOldField = db.TableDef.GetField(sOldField)
Set field = db.TableDef.NewField
field.Name = sNewName
field.Description = oOldField.Description
field.Type = oOldField.Type
If oOldField.Type =  WI_CHAR_FIELD Or oOldField.Type = WI_VIRT_CHAR Then
field.Length = oOldField.Length
field.Equation = oOldField.Equation
ElseIf oOldField.Type =  WI_NUM_FIELD Or oOldField.Type = WI_VIRT_NUM Then
field.Decimals = oOldField.Decimals
field.Equation = oOldField.Equation
ElseIf oOldField.Type =  WI_DATE_FIELD Or oOldField.Type = WI_VIRT_DATE Then
field.Length = oOldField.Length
field.Equation = oOldField.Equation
End If
task.ReplaceField oOldField.Name, field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Sub
Top
Mon, 05/22/2023 - 15:05
ortizmario6025@...
Offline
Joined: 09/03/2021 - 11:54

thanks to both

Top