3 Replies Latest reply on Aug 17, 2012 4:10 PM by erolst

    Automating "character" function import

    garyavischious

      I am not sure if this question is labeled correctly, but I am trying to import data from an Excel document into FileMaker. I want to have a 3 character/digit code for a color code. Some color codes are 3 letters, or a combination of alpha and numberic numbers, but many documents that we get have a color code for Black listed as "2" (as the person putting together the document does not know how to enter the '002 in Excel (and even if they did FileMaker only sees the "2", even in a text formatted field) and not the "002" that it needs to be. Or "44", not the "044" that it needs to be.

       

      Is there a way to automate the validation/entry? The Char (number) function only counts actual alpha characters and does not specify numeric characters.

       

      As an example, I want to:

      entered Color Code: "KX7" leave as KX7

      entered Color Code: "44" change to 044

      entered Color Code: "2" change to 002

       

      Any suggestions?

        • 1. Re: Automating "character" function import
          erolst

          You can use the simple formula Right ( "00" & myColorCode ; 3 ), which returns the original for a 3-character string, and otherwise pads with zeros to three places. Either make it an auto-enter calculation, or post-process your data by using Replace Field Contents.

          1 of 1 people found this helpful
          • 2. Re: Automating "character" function import
            AlanStirling

            Hi Eriost

             

            There could be a problem with your formula if the 'myColorCode' field is empty.

             

            I would suggest using "000" (Three zeros instead of two) so as to cover this situation.

             

            Perhaps I could also mention that in Excel, entering an Apostophe (') as the first character in a cell will force the cell to be formatted as text, which will not remove preceding zeros and does not show the apostrophe.

             

            Best wishes - Alan Stirling, London UK.

            1 of 1 people found this helpful
            • 3. Re: Automating "character" function import
              erolst

              Hi Alan,

              AlanStirling wrote:

               

              Hi Eriost

               

              There could be a problem with your formula if the 'myColorCode' field is empty.

               

              I would suggest using "000" (Three zeros instead of two) so as to cover this situation.

               

              Perhaps I could also mention that in Excel, entering an Apostophe (') as the first character in a cell will force the cell to be formatted as text, which will not remove preceding zeros and does not show the apostrophe.

               

              Best wishes - Alan Stirling, London UK.

               

              the way I understand the OP, this cell is not supposed to be empty; under this assumption, an empty value would indicate a problem with the original data, Either way, three zeros are as inaccurate as two. A better approach would be Case ( not isEmpty ( myColorCode ) ; Right ( "00" & myColorCode ; 3 ) )†, then search for records where myColorCode is empty

               

              Anyway, I have inherent trust in the OP's ability to adjust the general principle to his particular needs and the data at hand.

               

              btw, it's "erolst" - or Oliver, if you like.

               

              Regards from Tegernsee

               

              † Let(), Self(), (maybe) If () – thanks, I know