10 Replies Latest reply on Jan 6, 2012 4:57 AM by ibrahim_bittar

    How do I Extract the first letter each of a 3 word string?

    gcastle

      How do i Extract the first letter of each word of a 3 word string?

      Do I somehow nest a Left() in a LeftWords() then Middle() - the same and Right() - the same?

      I want to put a company name in a "Company" field and when I leave the field trigger the calculation to put the first letter of the first three words of the company field in the Company ID field.

       

      Your help will be appreciated! Gary E Castle

        • 1. Re: How do I Extract the first letter each of a 3 word string?

          Hi Gary,

           

          You can try this calculation, result is text:

           

          Left ( LeftWords ( text ; 1 ) ; 1 ) & Left ( MiddleWords ( text ; 2 ; 1 ) ; 1 ) & Left ( MiddleWords ( text ; 3 ; 1 ) ; 1 )

           

          ... but UUIDs should be meaningless.  Most folks use FM's auto-enter serial number which increments. 

           

          By using MiddleWords (), we don't have to worry about issue if there are only two words in the field.  If we used RightWords() then we would have to test that there are enough fields otherwise we could duplicate the same word twice.

          • 2. Re: How do I Extract the first letter each of a 3 word string?
            davidjhayden

            I would do the following, where "phrase" = the 3 word string and you want the 3 first letters together.  You cold make this shorter and cleaner, and LaRetta's approach would work, but this'll make it easier when you decide to change things down the road.

             

            Let ([

            $a = Substitute (phrase; " "; "¶");

            $L1= GetValue ($a; 1);

            $L2= GetValue ($a; 2);

            $L3= GetValue ($a; 3)

            ];

             

            Left ($L1; 1) & Left ($L2; 1) & Left ($L3; 1)

             

            )

             

            Best,

             

            Dave

             

            -----

            Dave Hayden

            Museum Data Solutions

            • 3. Re: How do I Extract the first letter each of a 3 word string?
              comment

              How about =

               

              Left ( Filter ( Proper ( text ) ; "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ) ; 3 )
              

               

              ---

              However, as LaRetta said, IDs should be meaningless.

              • 4. Re: How do I Extract the first letter each of a 3 word string?

                Now this is really good!  It took me a minute to figure out what it did because I kept thinking it should produce the first three letters of the first word but no, it filters only the first letter of each word!  Proper is the key! 

                • 5. Re: How do I Extract the first letter each of a 3 word string?
                  gcastle

                  Thanks again! Your solution works very well. I also like the "Filter Proper"

                  solution someone else sent me. I haven't tried it yet. What you helped me

                  with is only part of the equation for Customer ID.  I know I don't have  to

                  do this extra work. I am going to rethink it. I'm interested in your opinion

                  on this subject. Is there a problem from your point of view?  I learned it

                  from Colorado Kenworth when I worked for them. They use the first two

                  letters of the first word in the Company Name. and then they add 3 digits.

                  They do this according to the following Plot.

                   

                  ABC = 1; DEF=2; GHI=3; JKL;=4 on through to YZ=9.

                   

                  You apply these starting on the third letter of the Company Name. EX

                  Colorado Kenworth = CO456  With just 2 letters and 3 digits one could

                  conceivably have 675,324 accounts in this database.

                  I'm going to use the first letter of the first two letters of the first two

                  words in order to  avoid the only problem I've ever encountered.  In a big

                  city or even here in Billings, MT  If you have dozens or hundreds or

                  thousands of "BI" customers and quickly have a juggling problem. In the MS

                  Access Database I used for the last 14 years I had almost 4000 names and I

                  very rarely had such a jugging problem.  But by choosing the first  letter

                  of the first two names in the "company name"with 3 digits the juggling

                  problem is almost eliminated.

                   

                  Pros: Fast look up time. I have the number memorized by just using this

                  combination in a very short time.

                  I also use this combination as part of an auto enter id for other forms:

                  Projects; invoices, etc.  This makes some sorting easier and faster.  By the

                  way I'm form Missouri - "The SHOW ME" state.  I my data bases my Account #

                  has as much meaning as the actual company name.

                   

                  I would like to have all this scripted.  I think it is probably possible

                  maybe Not!  Right now this is so far above my novice to FMP abilities.  So

                  I'm dropping back to plan B.  Now that my first two Letters are entered

                  automatically my plans are to learn to script my "ABC" buttons to append the

                  corresponding 3 numbers (one at a time) behind the  2 Letters in the ID

                  field on the Add Contact form. I'll bet you can give me a solution for

                  scripting these buttons. Thanks in advance!  Gary

                  From:  LaRetta <noreply@filemaker.com>

                  Reply-To:  <fmi-1374853765-6b8-2-1ex5@fmdev.filemaker.com>

                  Date:  Wed, 04 Jan 2012 22:12:59 -0800

                  To:  Gary Castle <ccww@usadig.com>

                  Subject:  Re: How do I Extract the first letter each of a 3 word string?

                   

                  <https://fmdev.filemaker.com/index.jspa>

                  created by LaRetta <https://fmdev.filemaker.com/people/LaRetta>  in

                  Scripting and Calculations - View the full discussion

                  <https://fmdev.filemaker.com/message/65993#65993>

                   

                  Hi Gary, You can try this calculation, result is text: Left ( LeftWords (

                  text ; 1 ) ; 1 ) & Left ( MiddleWords ( text ; 2 ; 1 ) ; 1 ) & Left (

                  MiddleWords ( text ; 3 ; 1 ) ; 1 ) ... but UUIDs should be meaningless.

                  Most folks use FM's auto-enter serial number which increments.

                  Reply to this message by replying to this email -or- go to the message on

                  FileMaker Technical Network

                  <https://fmdev.filemaker.com/message/65993#65993> Start a new discussion in

                  Scripting and Calculations by email

                  <mailto:discussions-community-discuss-scripting_and_calculations@fmdev.filem

                  aker.com>  or at FileMaker Technical Network

                  <https://fmdev.filemaker.com/choose-container!input.jspa?contentType=1&conta

                  inerType=14&container=2035>

                                          Manage your email preferences

                  <https://fmdev.filemaker.com/index.jspa/user-notification-preferences!input.

                  jspa> .

                   

                  FileMaker Developer Conference 2012 € Miami, Florida € July 16-18 €

                  www.filemaker.com/devcon

                  • 6. Re: How do I Extract the first letter each of a 3 word string?

                    Hi Gary,

                     

                    I do not wish to dissuade you from your task but unique table IDs should be meaningless because meanings can change.  Consider company name:

                     

                    Nissan used to be called Datsun

                    Xerox used to be called The Haloid Company

                    Time Warner used to be called AOL Time Warner

                    BP PLC used to be called British Petroleum

                    ... these are off the top of my head.

                     

                    And the problem is, when relationships are based upon this information and you change the name, you break your relationships.  Also, you say it is quick to find them but would you remember to look up Time Warner under AOL?  Using shortcuts is fine but their meaning will become less meaningful as names change, as you hit duplicates (and you will).  Also, what about company names beginning with number?  Or beginning with 'the' or 'A'?

                     

                    It simply is best to use meaningless serial numbers to hold your relationships together.  If you want a shortcut to quickly find someone, search the company name field directly where you can type parts of the name and get all the results (even using filtered portal).  I have worked on such ID assigning- solutions (for backward compatibility) and you need additional protections if served (to avoid assigning duplicate serials). 

                     

                    Every day is a good day to switch from repeating fields to regular fields ... and each day is a good day to switch from human-created IDs to meaningless IDs.

                     

                    LaRetta

                    1 of 1 people found this helpful
                    • 7. Re: How do I Extract the first letter each of a 3 word string?

                      Also if possible, can you modify your post and remove all of the text after "Thanks in advance!  Gary" where the email information begins?  It is just confusing if folks think they are links to follow.  If not, that's okay too.  :-)

                      • 8. Re: How do I Extract the first letter each of a 3 word string?
                        gcastle

                        I'm sorry but I was unable to delete the built in information.  Thanks for

                        your insight on ID's.  I actually  did put an auto enter serial field in my

                        data base when I started it several months ago. So I'm covered there. I just

                        have to decide if I still want to do my system for lookup.  In the mean time

                        I would still like to know the scripting tech to add say a button triggered

                        value into say a memo field if you have time. Gary

                         

                        From:  LaRetta <noreply@filemaker.com>

                        Reply-To:  <fmi-965117135-6b8-2-1eyx@fmdev.filemaker.com>

                        Date:  Thu, 05 Jan 2012 15:05:51 -0800

                        To:  Gary Castle <ccww@usadig.com>

                        Subject:  Re: How do I Extract the first letter each of a 3 word string?

                         

                        <https://fmdev.filemaker.com/index.jspa>

                        created by LaRetta <https://fmdev.filemaker.com/people/LaRetta>  in

                        Scripting and Calculations - View the full discussion

                        <https://fmdev.filemaker.com/message/66057#66057>

                         

                        Also if possible, can you modify your post and remove all of the text after

                        "Thanks in advance!  Gary" where the email information begins?  It is just

                        confusing if folks think they are links to follow.  If not, that's okay too.

                         

                        Reply to this message by replying to this email -or- go to the message on

                        FileMaker Technical Network

                        <https://fmdev.filemaker.com/message/66057#66057> Start a new discussion in

                        Scripting and Calculations by email

                        <mailto:discussions-community-discuss-scripting_and_calculations@fmdev.filem

                        aker.com>  or at FileMaker Technical Network

                        <https://fmdev.filemaker.com/choose-container!input.jspa?contentType=1&conta

                        inerType=14&container=2035>

                                                Manage your email preferences

                        <https://fmdev.filemaker.com/index.jspa/user-notification-preferences!input.

                        jspa> .

                         

                        FileMaker Developer Conference 2012 € Miami, Florida € July 16-18 €

                        www.filemaker.com/devcon

                        • 9. Re: How do I Extract the first letter each of a 3 word string?

                          If there is a connection to this latest request and your first one, Gary, I confess that I do not see it so please indulge me.  It is usually best to search for answers to different questions and then post a new question when you cannot find answers.  In that way, your new subject provides valuable information to other Users also searching for same kind of assistance.  But regardless where you post, we will always try to assist so let's see if we can get you answers here:

                           

                          I would still like to know the scripting tech to add say a button triggered value into say a memo field if you have time.

                           

                          You do not need a script If you want to insert today's date into a memo field, Insert > Button.  Scroll down to Fields section and double-click Insert Calculated Result and select your memo field from the list then say OK.  Now that you have specified the 'target', specify the calculated result (button right below 'specify target').   In this calculation dialog, type:

                           

                          Get ( CurrentDate ) & ¶

                           

                          This will, via button, insert the current date into the memo and add a new line for User to begin typing.  When you exit, you will be given opportunity to name your button.  If you want a script trigger then you will need to create a script out of the button instead.  If this is not what you wish, please provide more information and we will be happy to assist you.

                           

                          LaRetta

                          • 10. Re: How do I Extract the first letter each of a 3 word string?
                            ibrahim_bittar

                            Hi La Retta

                             

                            If you don't mind I'll borrow your last phrase, it's very good :).

                             

                            Saludos

                             

                            Ibrahim Bittar Torres

                            Director General

                            Eikonsys, S.A. de C.V.

                            FileMaker 10 Certified Developer

                            http://www.eikonsys.com

                            FileMaker Business Alliance