2 Replies Latest reply on May 2, 2011 3:55 PM by philmodjunk

    another novice question-  search in 1 field and enter in another

    Muffalupogus

      Title

      another novice question-  search in 1 field and enter in another

      Post

      Hi,

      I am a novice with FMP still and I am trying to learn as I build my first database.  

      I have data that was downloaded and that I now need to organize so that I can get outputs I understand.  I am trying to extract data (text, one of various words) from a particular field and if one of these words is present I want to have a text entry (a single word) put into a different field.  

      I am looking at who is sponsoring something, and the institutions are listed by name.  I want to categorize them by Academic, Industry... and based on key words in the first field I want either "Academic" or "Industry" entered into a different flied.  Is the best way to do this with a calculation in the filed I want to populate into or would a script be better?

      I have tried writing my own calculation and I can get the program to find something (obviously not the right thing yet) and then try to write in the new field but I am not getting the separation between "Academic" and "Industry".

      What I have tried so far is:

      Case (

      GetField ( Sponsors )  = "*University*" ; "Academic" ; 

      GetField ( Sponsors )  = "*Hospital*" ; "Academic" ;

      GetField ( Sponsors )  = "*College*" ; "Academic" ;

      GetField ( Sponsors )  = "*School*" ; "Academic" ;

      GetField ( Sponsors )  = "*Inc.*" ; "Industry" ;

      GetField ( Sponsors )  = "*Ltd.*" ; "Industry" ;

      " ")

      And this was just a pilot to see if I was on the right track... I wasn't:

      If ( GetAsText ( GetField ( Sponsors ) ) = "*University*" ; "Academic" ; "Industry" )

      Thanks for your help!

        • 1. Re: another novice question-  search in 1 field and enter in another
          Muffalupogus

          I actually just realized that I should have set the field as text (the one I am interested in populating) and then used the "Options" menu and "Auto-enter" a calculated value.  I tried it but my calculation is still incorrect.  Any help with this would be fantastic.

          • 2. Re: another novice question-  search in 1 field and enter in another
            philmodjunk

            auto-enter is just one option. A calculation field works (If you don't need to edit the results and want to keep the original data unmodified) and if this is a one time fix to your data you can even use the following expression in a Replace Field Contents operation:

            Case (
                       PatternCount ( Sponsors  ; "University" ) ; "Academic" ; 
                       PatternCount (Sponsors ; "Hospital" ) ; "Academic" ;
                       PatternCount (Sponsors ; "College" ) ; "Academic" ;
                       PatternCount (Sponsors ; "School" ) ; "Academic" ;
                       PatternCount (Sponsors ; "Inc." ) ; "Industry" ;
                       PatternCount (Sponsors ; "Ltd." ) ; "Industry" ;
                       " ")