Changing Font Case During Expor

[John Chamberlain]

I have a file that contains data entered by various persons. Some of the data is in full caps, some in title case, and some in all lowercase. I want to export the data to an excel spreadsheet, and I need all of the fields to be in Title case. I can change the case in the FMP file, but when I export it the formatting does not go with the data. Is there any way to do this export with the formatting intact?

 

TIA

[comment]

I can change the case in the FMP file,

 

Do you want to change the case in the FMP file? You're not being quite clear about that. If you do, and you want the change to apply to the exported data as well, you must modify the text itself using the Proper() function. Note that this action is irreversible.

[John Chamberlain]

Ideally I would like all the fields in the FMP file to be full caps or to be initial caps (title case) no matter which way the operator has entered the data. Either of these would then export yhr firlfd in a uniform manner. The result I am after is to have the data in the Excel file appear in the same font style.

[comment]

Ideally I would like all the fields in the FMP file to be full caps or to be initial caps (title case) no matter which way the operator has entered the data.

 

Okay, so this is not really about exporting. Once you fix the data in the Filemaker file itself, the export will follow. This needs to be done in two - or actually three - steps:

 

0. Backup your data;

1. Fix your existing data;

2. Make sure any future data is automatically fixed.

 

For step 1, show all records in the table, click into the field, and select Records > Replace Field Contents… > Replace with calculated result: 

Proper ( YourFieldName )

[ --- added due to popular demand:

or, if you prefer:

Upper ( YourFieldName )

--- ]

Repeat this for each field you want to process in this way.

 

 

For step 2, set each such field to auto-enter calculated value (replacing existing value) =

Proper ( Self )

Edited February 27, 2014 by comment

[Rick Whitelaw]

Depending on the data contained in your fields, Title Case can end up looking strange and unnatural. Your choice of course.

[LaRetta]

I would like all the fields in the FMP file to be full caps or to be initial caps (title case) no matter which way the operator has entered the data. 

 

All fields in the file?  Fields have different needs ... some fields might be titles to books which would warrant Comment's step 2 where an auto-enter (Replace) is perfect.  But if the field contains sentences - not so great an idea of course; and if the field contains people or places - probably not so great either.  

 

Either of these would then export yhr firlfd in a uniform manner. 

 

Is it a single field (I bolded blue?) or multiple fields and what is their purpose? To expand on Rick's comment about the "unnatural", you can have NBC changed to Nbc and worse still ... LaRetta changed to Laretta (LOL) so just a bit of caution ... if the field holds proper name, you may wish to skip step 2 on that field.  Proper() is exact in its interpretation but NAMES of people, things, events, acronyms and the world (in its various spellings and languages) does not always capitalise according to Proper().  

 

Which is worse? Asking a User to data-enter AT&T properly?  Or letting them enter at&t and it changing to At&T and improperly staying that way, not even allowing the User to correct it if they wanted to?  There is only one thing worse and that is ALL CAPS.  I agree the initial 'normalisation' of step 1 is in order ... eliminate the all caps by whatever means necessary, have someone you trust check the data for mis-capitalizations and never bring them back.  They are difficult (thus stressful) for a User to read, they imply 'old school' and government, and they are considered rude and shouting unless used singly only to emphasise.  BTW, I refuse to read text that is all cap and I toss mail with an all cap address as well knowing it is junk (or government which is same thing).    Once you change the data to all caps, you cannot change it back without possibility of breaking the capitalisation.

 

ALL CAPS should not be used to cover-up data-entry person too lazy to use the shift key.  Users should be held accountable for accurate data-entry on proper names.  I've harped on this before because people and companies really care that their names are correctly spelled.  Blow it here and I don't care how good your product, the customer will resent you for it. And nothing looks more dorky than a 'personal' letter sent which is Dear Mr. CABBOTT.  It is obvious it came from a database and that the sender could care less about the customer.

 

So if the  yhr firlfd field or fields are Titles, Product Names, Stock Codes etc then step 2 will fit perfectly - I just wanted to caution if not.

[comment]

So if the  yhr firlfd field or fields

 

I believe yhr firlfd is a typo of the field.

 

BTW, though I agree with all you (and Rick) said, ultimately John (the OP) is presumed to be a big boy and know what he's doing.

[LaRetta]

LOL, I knew it was typo but didn't know if it was 'yrField' or what.  But when I mimic and type it out, I can clearly see it ... yhr ... indeed spot on ... the.

 

Excellent.

 

;-)

[LaRetta]

LOL, I hadn't seen your add-on, Michael.  You know how I can fuss ... I have this fear that if I know something and do not explain any highly-potential caveats that it then might hurt someone simply because I was too lazy to type my additional thoughts.  

 

So yes most times folks know exactly what I am saying but not always so I try to fill in the gaps for those reading this thread six months from now who has similar (but not exact) problem and are new to FM and who applies it to a SurName where it might hurt them.  And I do it just in case John didn't think of it for all fields.  It has nothing to do with being a big boy or age or even expertise ... I get reminded every day in this business ... and simply none of us can think of everything.  It is the Master Mind Principle at work, also known in my hippie days as head-tripping.

 

So, for every five (WAG) people reading this who knows it already and wishes I'd hush, there is someone who does not know it.   I realise that I 'what if' quite a bit but most bases should be covered when we think of them, focusing on pre-problem more than disaster-recovery.  

 

Knowledge is power ... I miss Soren.

[John Chamberlain]

OK, please let me clarify.

 

The data gets entered by volunteers who may, or may not, use the shift key or the caps lock. There is no way to discipline volunteers at this time.

 

The data being exported is for a report to another entity, and should be uniform in appearance - they don't care if it is all caps or title case, but it should not be a mixed bag.

 

What I want, if it is possible, is a way to define the fields such that, no matter how the data is entered, will export them in one case only.

 

I could use LaRetta's suggestion to replace all data in each field, but wouldn't that only fix the data at that point? And wouldn't future entries would still be subject to the data entry persons whim?

 

PS Yes, the typo should have been "the field"

[comment]

wouldn't that only fix the data at that point? And wouldn't future entries would still be subject to the data entry persons whim?

 

Have you read my post #4 above?

[John Chamberlain]

Yes Rick, I read it, but apparenty did not understand it completely. I will try your solution today.

 

Thanks to all

[LaRetta]

It was Michael (Comment) who made the great suggestions in post #4.  

[comment]

You can call me Al.

[John Chamberlain]

Hi Michael (don't know where I got Rick from)

 

Tried your solution, and it works just fine - EXCEPT it only works for the current data. When new records are entered by someone using intial caps or all lower case that is how the data will export.

 

What I want is probably not even possible, i.e. fields that, no matter how they are entered, will always export in all upper case.

[comment]

It is possible. Just follow step 2 meticulously - especially the part that says "replacing existing value". By default, the auto-enter has the option "Do not replace existing value" checked. You need to deselect it.

[Rick Whitelaw]

What LaRetta said. Not guilty!

Rick.

[John Chamberlain]

Michel, Rick, LaRetta, et al

 

Thanks so much for helping a clueless old man