field number - change to format f00n

[coombsmj]

Does anyone know how to change the field number from the format fn (f1, f2,...,f10, f11,...) to f00n (f001,...f099...)?

Help is appreciated.

-MJ

[Thom]

Are you trying to change the field names? For that you'd need some kind of macro utility like Applescript (Mac) or Auto-It (Windows). How many fields are you trying to change?

[The Bridge]

Try this with a calculated text field:

New_Field="f" & Right("000" & Substitute(Old_Field, "f", ""), 3)

This will work for values which have "f" and 3 digits or fewer. If you expect to have more digits following the "f", then increase the number of 0's in the calculation and the number at the far right of the calculation appropriately.

[coombsmj]

The problem is that the fields are in an already-in-use database. Is there any hope?

[The Bridge]

There is always hope -- but read this entire reply before proceeding.

If you want to replace the values across your entire database, do this:

1. BACK UP YOUR DATABASE

2. Show All Records

3. Select the field you want to replace the values in

4. Select Replace... from the Records menu

5. Choose Replace with Calculated Result

6. Enter the calculation I gave you above. Be sure to change the field name to the name of the field you are replacing.

7. Click Replace

***WARNING***

THIS CANNOT BE UNDONE -- BE SURE TO MAKE A BACKUP FIRST!

I really recommend trying this with a calculated text field first, as I described earlier, just so you can be sure what sort of a result you will end up with after the replace. I cannot emphasize this enough. If the values you are replacing are keys in a relationship, the relationship will be broken. If the values you are replacing are used in other calculated fields, the calculations may also break.

[coombsmj]

quote:

---

Originally posted by Thom:

Are you trying to change the field names? For that you'd need some kind of macro utility like Applescript (Mac) or Auto-It (Windows). How many fields are you trying to change?

---

Yes, I'm trying to change the field names of 99 records (I'm assuming that I don't need to change fields over 99, as they already have a three-digit number). Is this freeware or shareware (I'm working on a Windows-2000 platform)?

-MJ

[The Bridge]

quote:

---

Originally posted by MJC:

Yes, I'm trying to change the field names of 99 records...

---

My apologies for what may have been a very misleading answer... didn't think carefully enough about what you wanted to achieve.

[coombsmj]

Thom-

I'd greatly appreciate help w/the script.

Thanks,

MJ

[Thom]

AutoIt is freeware: http://www.hiddensoft.com/AutoIt/

You write a script in its own language, and it sends keystrokes and mouse-clicks to the computer. Keystrokes tend to be more reliable. I'd be happy to help you with the script, if you need it.

[Thom]

Assuming you have AutoIt installed, the .aut extension should be registered. Save the following code with a filename ending in .aut, like fm-rename-fields.aut . Have your FileMaker file open and in front. Switch to the folder with the .aut file, and double-click to run it. It will switch to FileMaker, open Define Fields, and begin renaming the fields from f1->f001, etc., up through f99->f099. As always, make a backup first.

code:

---

; FileMaker rename fields (fm-rename-fields.aut)

; rename fields from `f1', `f2', ..., `f99' to `f001', `f002', ..., `f099'

; go fast

SetKeyDelay, 1

SetWinDelay, 1

; get FileMaker's attention

ATTENTION:

WinActivate, FileMaker Pro - [

IfWinNotActive, FileMaker Pro - [,, Goto, ATTENTION

; open Define Fields

Send, +^d

; Get first field name

Send, +{TAB}{SPACE}{TAB}^c

RenameField:

; exit after f99

StringLen, strLength, clipboard

IfGreater, strLength, 3, Goto, END

; insert zeroes

StringLeft, firstLetter,clipboard,1

StringTrimLeft, digits, clipboard, 1

SetEnv, digits, 00%digits%

StringRight, digits, digits, 3

SetEnv, clipboard, %firstLetter%%digits%

; paste new name and save

Send, ^v!a

; get next field name

Send, +{TAB}{DOWN}{TAB}

CopyName:

Send, ^c

IfEqual, clipboard, AutoIt Clipboard overflow, Goto, CopyName

Goto, RenameField

END:

Exit

---

[ November 28, 2001: Message edited by: Thom ]