Problem changing a field name

[Jay Talbott]

I have a crosstab operation which produces a field with way more than 32 characters. This is causing a heck of a lot of problems.
Because the field name is longer than 32 characters, I cannot use Xbasic to change the field name, and because I cannot change the field name, the report is not breaking correctly.

Believe it or not, the field name that is produced is:
production_dept_alltrim_str_recipe_no_padr_alltrim_item_30_portion_left_notes_20_

I know, it looks impossible, but here is the screenshot proving it:
long_field_name.png

I have tried to use t.fieldname_change(), but to no avail, probably because of the long file name. I have tired grabbing on the first 32 characters of the file name and using that in the t.fieldname_change() function, but that did not work either.
Any thoughts?
Thanks,
Jay

[Finian Lennon]

Re: Problem changing a field name

Hi Jay:

I haven't done this in a while. Have you tried the following:

Define the Crosstab again. In advanced, choose NOT to delete the data dictionary. Run it to make sure it's doing what you want, regardless of field name length.
If it is, right click on the CrossTab and choose "Show Xbasic". Copy the xbasic into a script and edit the values (output name, fieldnames etc) to what you want, making sure not to allow the deletion of the data dictionary. Run.

I think that should do it.

[Jay Talbott]

Re: Problem changing a field name

Thanks, Finian. I'll try it tomorrow morning and report back with the results.
Jay

[Stan Mathews]

Re: Problem changing a field name

I have tried to use t.fieldname_change(), but to no avail,

I think you mean table.fieldname_change(). It's not clear from the documentation but you must specify the full drive-path-tablename-extension when using this.

t = table.open("aaa")
flds_old = t.field_name_get()
t.close()
? flds_old
= a
b
c
d

flds_new = <<%nm%
#abcdefghijklmnopqrstuvwzyz1234567890a
#b
#c
#d
#%nm%
? flds_new
= abcdefghijklmnopqrstuvwzyz1234567890a
b
c
d


table.fieldname_change("p:\executive\mpurch\aaa.dbf",flds_old,flds_new)
t = table.open("aaa")
flds_old = t.field_name_get()
t.close()
? flds_old
= abcdefghijklmnopqrstuvwzyz1234567890a
b
c
d
flds_new = <<%ab%
#z
#y
#x
#w
#%ab%

table.fieldname_change("p:\executive\mpurch\aaa.dbf",flds_old,flds_new)
t = table.open("aaa")
flds = t.field_name_get()
t.close()
? flds
= z
y
x
w

[johnkoh]

Re: Problem changing a field name

Stan,

I did not know how to apply table.fieldname_change() until see this.
Thank you

[Stan Mathews]

Re: Problem changing a field name

You're welcome.

If you are going to use this you should also be familiar with the somewhat inappropriately named TABLE.NAME_NORMALIZE().
This method will help you avoid problems with field names that contain characters doesn't recommend. Also keep the field names at or under 32 characters.

? left(table.name_normalize("a long*field+name%with@some=garbage^characters"),32)
= "a_long_field_name_with_some_garb"

As you can see from the example above, table.fieldname_change() doesn't validate the field name list you supply.

[Al Buchholz]

Re: Problem changing a field name

Jay

Here is some code that I've used in this situation.

It changes one fieldname to what I've chosen.

Someday I'd like to expand it to do multiple fields with one table open.... Maybe next week.... Or I can find someone to do that..........

Add this UDF to your database.

FUNCTION GET_FIELDNAME AS C (ptn as C, pfn as n )

'Given a table name and field number return the field name
'
dim Key_fld_ptr as P
dim t_source as P
dim fld as P

t_source = table.open(ptn)
fld = t_source.field_get(pfn)

get_fieldname=fld.name_get()
t_source.close()
END FUNCTION

Crosstab xbasic is generated with this line...

'Add the table just created by the Crosstab operation to the Database..
file_add_to_db(xtab_db_name)

add this new code added at this point...

oldfieldname=get_fieldname(xtab_db_name,1)
table.fieldname_change(xtab_db_name,Oldfieldname,"IndexKey")

With a little mod of the last line it can be used on a summary operation also to made reasonable names..

[Jay Talbott]

Re: Problem changing a field name

Thanks Finian, Stan and Al. It is working great now.
Jay

[amorris]

Re: Problem changing a field name

You guys are doing something I never do. I never create or condone a field/index name of more than 10 letters, file names are no more than 8. Up to you though.

[Al Buchholz]

Re: Problem changing a field name

You guys are doing something I never do. I never create or condone a field/index name of more than 10 letters, file names are no more than 8. Up to you though.

Al

Nice name..

I agree, but in this case, the crosstab operation is creating the fieldname, not the developer. We're just cleaning it up..