John Chamberlain Posted February 26, 2014 Share Posted February 26, 2014 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 Link to comment Share on other sites More sharing options...
comment Posted February 26, 2014 Share Posted February 26, 2014 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. Link to comment Share on other sites More sharing options...
John Chamberlain Posted February 26, 2014 Author Share Posted February 26, 2014 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. Link to comment Share on other sites More sharing options...
comment Posted February 27, 2014 Share Posted February 27, 2014 (edited) 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 1 Link to comment Share on other sites More sharing options...
Rick Whitelaw Posted February 27, 2014 Share Posted February 27, 2014 Depending on the data contained in your fields, Title Case can end up looking strange and unnatural. Your choice of course. Link to comment Share on other sites More sharing options...
LaRetta Posted February 27, 2014 Share Posted February 27, 2014 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. Link to comment Share on other sites More sharing options...
comment Posted February 27, 2014 Share Posted February 27, 2014 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. Link to comment Share on other sites More sharing options...
LaRetta Posted February 27, 2014 Share Posted February 27, 2014 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. ;-) Link to comment Share on other sites More sharing options...
LaRetta Posted February 27, 2014 Share Posted February 27, 2014 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. Link to comment Share on other sites More sharing options...
John Chamberlain Posted February 27, 2014 Author Share Posted February 27, 2014 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" Link to comment Share on other sites More sharing options...
comment Posted February 27, 2014 Share Posted February 27, 2014 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? Link to comment Share on other sites More sharing options...
John Chamberlain Posted February 27, 2014 Author Share Posted February 27, 2014 Yes Rick, I read it, but apparenty did not understand it completely. I will try your solution today. Thanks to all Link to comment Share on other sites More sharing options...
LaRetta Posted February 27, 2014 Share Posted February 27, 2014 It was Michael (Comment) who made the great suggestions in post #4. Link to comment Share on other sites More sharing options...
comment Posted February 27, 2014 Share Posted February 27, 2014 You can call me Al. Link to comment Share on other sites More sharing options...
John Chamberlain Posted February 27, 2014 Author Share Posted February 27, 2014 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. Link to comment Share on other sites More sharing options...
comment Posted February 27, 2014 Share Posted February 27, 2014 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. Link to comment Share on other sites More sharing options...
Rick Whitelaw Posted February 27, 2014 Share Posted February 27, 2014 What LaRetta said. Not guilty! Rick. Link to comment Share on other sites More sharing options...
John Chamberlain Posted February 28, 2014 Author Share Posted February 28, 2014 Michel, Rick, LaRetta, et al Thanks so much for helping a clueless old man Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 3731 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now