Text Format Calculation

[alphanitrate]

I need to change the Titles in my database from ALLCAPS to Proper Case.

I already have in place the following:

Trim ( Upper ( Cluster Title ) )

I know I can change this calc to

Trim ( Proper ( Cluster Title ) )

Here are my two questions:

1. This Calc requires that this field be entered and submitted before this calculation takes effect.

I have 30K records, so going one by one isn't feasible.

My current plan is to create a separate database with this calc in place so that when the content comes in it will be formatted as Proper case and then Import this back in.

Is there some way I can "touch" the field in each record so this takes effect?

2. Is it possible to have it not do minor words? (of, the, and, for, pc , oz. etc) but not do situations where they are part of other words (Hand should be ok but thermometer might not be.

In 8.5

[alphanitrate]

OK so here's an update - by changing the formula to Trim (Proper (Group Title) ) it automatically applies the formula to all the records.

Step 1. solved!

Step 2 addendum: Have now been informed that we also need to keep certain words uppercase (LED, ORB etc)

I'm thinking I have to do this the hard way through excel.

[bruceR]

OK so here's an update - by changing the formula to Trim (Proper (Group Title) ) it automatically applies the formula to all the records.

Step 1. solved!

Step 2 addendum: Have now been informed that we also need to keep certain words uppercase (LED, ORB etc)

I'm thinking I have to do this the hard way through excel.

When does Excel ever do anything that can't be done in FileMaker? Certainly not this time.

[Raybaudi]

Hi

try something along this line ( may be that you'll need to filter punctuactions )

Trim (

Substitute ( Proper ( Cluster Title ) & " "

; [ "Led " ; "LED " ]

; [ "Orb " ; "ORB " ]

; [ "The " ; "the " ]

; [ "Of " ; "of " ]

; [ "And " ; "and " ]

; [ "For " ; "for " ]

; [ "Pc " ; "pc " ]

; [ "Oz. " ; "oz. " ]

)

)

[alphanitrate]

Danielle,

That is perfect thank you very much. This will save many tedious hours. Now All I need to do is complete a full list of all the changes needed to be made. THANK YOU!!!

[alphanitrate]

just in case you were curious what it ended up looking like:

missing Trim (

Substitute ( Proper ( Cluster Title ) & " "

; [ "Led " ; "LED " ]

; [ "Orb " ; "ORB " ]

; [ "The " ; "the " ]

; [ "Of " ; "of " ]

; [ "And " ; "and " ]

; [ "For " ; "for " ]

; [ "Pc " ; "pc " ]

; [ "Oz. " ; "oz. " ]

; [ "With " ; "with" ]

; [ "Hdmi " ; "HDMI" ]

; [ "Vhs-C " ; "VHS-C " ]

; [ "Vhs " ; "VHS " ]

; [ "Atv " ; "ATV " ]

; [ "Aa " ; "AA " ]

; [ "Aaa " ; "AAA " ]

; [ "Wd-40 " ; "WD-40 " ]

; [ "Psi " ; "PSI " ]

; [ "psi " ; "PSI " ]

; [ "In " ; "in " ]

; [ "X " ; "x " ]

; [ "To " ; "to " ]

; [ "Mm " ; "mm " ]

; [ "Ml " ; "ml " ]

; [ "Lb " ; "lb " ]

; [ "Pk " ; "pk " ]

; [ "Ft " ; "ft " ]

; [ "Hp " ; "HP " ]

; [ "hp " ; "HP " ]

; [ "Jic " ; "JIC " ]

; [ "Jicm " ; "JICM " ]

; [ "Jicf " ; "JICF " ]

; [ "Sae " ; "SAE " ]

; [ "Bm " ; "BM " ]

; [ "Cp " ; "CP " ]

; [ "Bf " ; "BF " ]

; [ "i " ; "I " ]

; [ "ii " ; "II " ]

; [ "iii " ; "III " ]

; [ "iv " ; "IV " ]

; [ "B&s " ; "B&S " ]

; [ "Tv " ; "TV " ]

; [ "Npt " ; "NPT " ]

; [ "Nptf " ; "NPTF " ]

; [ "Nptm " ; "NPTM " ]

; [ "Fnpt " ; "FNPT " ]

; [ "a " ; "A " ]

; [ "w " ; "W " ]

; [ "v " ; "V " ]

; [ "Vac " ; "VAC " ]

; [ "Vdc " ; "VDC " ]

; [ "Atv " ; "ATV " ]

; [ "Utv " ; "UTV " ]

; [ "Rv " ; "RV " ]

; [ "Hss " ; "HSS " ]

; [ "Unc " ; "UNC " ]

; [ "Nf " ; "NF " ]

; [ "Nc " ; "NC " ]

; [ "Asa " ; "ASA " ]

; [ "Btu " ; "BTU " ]

; [ "Gpm " ; "GPM " ]

; [ "Gph " ; "GPH " ]

; [ "Cfm " ; "CFM " ]

; [ "Dot " ; "DOT " ]

; [ "Dot-c " ; "DOT-C " ]

; [ "Cv " ; "CV " ]

; [ "Wd-40 " ; "WD-40 " ]

; [ "Hvlp " ; "HVLP " ]

; [ "Pvc " ; "PVC " ]

; [ "Din " ; "DIN " ]

; [ "Gm " ; "GM " ]

; [ "Mig " ; "MIG " ]

; [ "Tig " ; "TIG " ]

; [ "Rca " ; "RCA " ]

; [ "Co2 " ; "CO2 " ]

; [ "Ohv " ; "OHV " ]

; [ "Tgh " ; "TGH " ]

; [ "Obdii " ; "OBDII " ]

; [ "Atm " ; "ATM " ]

; [ "Atc " ; "ATC " ]

; [ "Agc " ; "AGC " ]

; [ "Vsr " ; "VSR " ]

; [ "Abs " ; "ABS " ]

; [ "Thx " ; "THX " ]

; [ "Xl " ; "XL " ]

; [ "xl " ; "XL " ]

; [ "Xxl " ; "XXL " ]

; [ "xxl " ; "XXL " ]

; [ "Xxxl " ; "XXXL " ]

; [ "xxxl " ; "XXXL " ]

)

)

Works like a dream. Only had an issue with the single letter appearing at the end of words.

Thanks again!