8 Replies Latest reply on Aug 23, 2013 1:52 AM by sarah_ryanne



      Learning the ways to FMP and looking for some advice as to which/how many functions I should be well versed in??? Thanks in advance!

        • 1. Re: Functions

          Here are some commonly used ones:


          Case ( test1 ; result1 {; test2 ; result2 ; … ; defaultResult} )

          Date ( month ; day ; year )

          Filter ( textToFilter ; filterText )

          IsEmpty ( field )

          Left ( text ; numberOfCharacters )

          Length ( text )

          Let ( {[} var1 = expression1 {; var2 = expression2…]} ; calculation )

          Middle ( text ; start ; numberOfCharacters )

          Month ( date )

          Position ( text ; searchString ; start ; occurrence )

          Replace ( text ; start ; numberOfCharacters ; replacementText )

          Right ( text ; numberOfCharacters )

          Round ( number ; precision )

          Sum ( field {; field…} )

          WordCount ( text )

          Year ( date )


          An advanced one to write SELECT SQL statements:

          ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments… } )


          These don't require any parameters but are really good to know:







          It's hard to pick how many you should know. I'd recommend getting familiar with functions that allow you to manipulate numbers ( round, truncate, abs ), text (left, right, middle, position, length ), dates ( date, day, month, year, dayofweek ), time ( time, minutes, seconds ).


          FTS (FileMaker Training Series) highlights some of the best ones to know. And this is a good guide that explains them all:


          • 2. Re: Functions

            Hello, Sarah. Welcome to FileMaker.


            "What functions should I learn?" is largely akin to "Which stores should I shop in?" The first question someone would ask you, were you to ask that, would be, "What are you shopping for?"   


            FileMaker has literally hundreds of different functions. Most developers have a core set they use frequently, based on their needs, preferences, and styles. The vast majority of us have learned them over the years, as we had a need and reached out for a new tool to fill it. We learned as we went.


            That said, there are certain core categories of functions that, at least in my opinion, every developer needs to be proficient with:


            1) The various text parsing functions - Length, Left, Right, Middle, LeftWords, RightWords, MiddleWords, PatternCount, Substitute, Position. If you spend any time at all working with text strings, you'll eventually have a use for every one of them.


            2) The functions that deal with return delimited lists - LeftValues, RightValues, MiddleValues, GetValue, FilterValues, ValueCount, List. FileMaker makes heavy use of return delimited lists (any time you use a checkbox set, it's stored that way, and value lists and multi-keys are also prevalent). You'll need to know how to manipulate these lists and extract what you need from them, especially as you advance to more sophisticated techniques.


            3) Date and time functions - especially Date, Time, Get ( CurrentDate ), Get ( CurrentTime ), Get ( CurrentTimestamp ). A lot of what you'll wind up doing will involve knowing when things happen.


            4) Boolean and logical functions - If, IsEmpty, IsValid, Case. You must know how to instruct FileMaker to make decisions.


            5) Math functions - if you do any parsing of math, you'll need them. Cos, Abs, etc.


            6) Environment functions - Basically everything that starts with Get (either with or without parentheses). These are invaluable for abstracting your calculations so you can avoid hard-coding things. Makes your scripts and calculations portable.


            Those should be a good starting point. As you advance, you'll also want to look at Let and Evaluate. These are tremendously beneficial.





            • 3. Re: Functions

              Hi Mike,


              Your advice has been incredibly useful.


              As the 100's of functions did look a bit daunting your's and Martha's suggestions have given me a great starting point.


              Thank you for taking the time with this beginner!




              • 4. Re: Functions

                Hi Martha!


                Many thanks for your suggestions.... FTS is already on my Amazon wishlist!




                • 5. Re: Functions

                  One I would absolutely add to Martha's list would be the: Substitute ( text ; searchString ; replaceString ) function.


                  Another very important thing to learn at an early stage is how FileMaker deals with data... Text, Number, Date, Container... etc... Learning how/when to use functions like GetAsNumber ( ), GetAsDate ( ), GetAsText ( ) ... etc... can be "critical" in how an operation works.  A string of numbers can be evaluated Numerically or Alphanumerically both result in a different hierarchy.

                  • 6. Re: Functions

                    You've gotten good suggestions and welcome, Sarah! you may want to take the 'a function a day' approach and study just one each day until you've gone over them all. I've been using FMP since v1.0 and I still find new functions I'd never used (of course, new ones are added in each version, but the ones that have been there since 6.0 and I just found them?!) So, I periodically review all of them briefly.


                    If you need more than can be found in FMHelp, type the functions in a search engine (such as "FileMaker ExecuteSQL") and see if you can get examples and articles to expand your knowledge.


                    Some of them are pretty complex and some of them are simple, but used in conjunction with other functions are extremely powerful. I use PatternCount(), Position() and the other text functions all in the same calculation and/or script for parsing text that I get from other sources and needs to be "cleaned". The Math functions are pretty standard. The Get and Design functions are not used as often (for me), but they are wonderful.


                    Another bit of advice, get the PDF from the FMI site and you have a searchable resource of Functions.


                    link to all documentation for FM12: http://www.filemaker.com/support/product/documentation.html

                    link to functions PDF: http://www.filemaker.com/support/product/docs/12/fmp/fmp12_functions_ref.pdf


                    (grab all the PDFs while you're there, just keep them handy!!)



                    • 7. Re: Functions

                      I have a different opinion on this. I think you should look over ALL of the functions. Maybe just read their description. There are only a few that you need to be very well versed in, and those are so simple that having a full understanding comes very quickly.


                      I believe it is more important to know that a function may exist to get a particular task accomplished. You don't need to know anything about how to use that function, just that it exist. Looking up the functions actual name, use and syntax can be done as needed.


                      Every now and then, I need to change the font or color of the calculated result. I couldn't manually type the function in a calculation from memory, but I do know there are functions for changing the font, color, style and size.

                      That was a fairly simple example. A more complex example would be the financial functions. I don't think I've ever used one of them, but I know they are available.


                      I can easily find the function and, usually, figure out its use by looking at the parameters. If not, the help system is just a few clicks away.




                      (If that style of learning is not for you, the operators is a good place to start.)

                      • 8. Re: Functions

                        Thanks everyone for your suggestions...


                        They have given me a good idea of where to start and build from and at this point there is no harm in trying them all.


                        (watch this space!)


                        Thanks Again,