7 Replies Latest reply on Dec 15, 2011 10:36 AM by sporobolus

    CF or Let, that is the question?


      While learning about building a Let () statement, it occured to me there is a cross over with Custom Functions!


      Given the following rules, which is best suited to the task (Let or CF)?


      Rules are as follows:


      1st 3 letters of code are 1st letters of each word in Co. name

      last 2 letters of Code are 1st 2 letters of City

      exclude suffixes (i.e. Inc, Co, Ltd...)

      consider hyphen as text separator


      if Co. name is 1 word (i.e. Hertz),

      use 1st 3 letters of Co. name (i.e. HER)

      if Co. name is 2 words (i.e. 4 Sale),

      use 1st letter of each word in Co. name (i.e. 4S)

      if Co. name is 3 words (i.e. Key-Point Transport),

      use 1st letter of each word in Co. name (i.e. KPT)

      if Co. name is >3 words (i.e. Jolly Green Giant Cannery),

      use only 1st letter of 1st 3 words in Co. name (i.e. JGG)


      duplicate Co. name (i.e. Hertz),

      use same (i.e HER) if City is different, as this will still produce a unique Code

      duplicate Co. name (i.e. Hermans),

      use next available consonant (i.e HEM)



      if City name is 1 word (i.e. Brampton),

      use 1st 2 letters of City name (i.e. BR)

      if City name is 2 words (i.e. Los Angeles),

      use 1st letter of each word in City name (i.e. LA)

      if City name is >2 words (i.e. Los Palmas Orianna),

      use only 1st letter of 1st 2 words in City name (i.e. LP)


      duplicate City name (i.e. Brantford),

      use next available consonant (i.e BN) if Co. Name is same

      - Hertz, Brampton = HERBR

      - Hertz, Brantford = HERBN

      - Hertz, Bramalea = HERBM

      - Hertz, Brandon = HERBD


      In the event a city runs out of consonants,

      add a 6th character (1-9 or reverse alpha z/y/x/w/v/u...)


      Ultimately, I hope to build the sample in both, a Let () statement and as a Custom Function!



        • 1. Re: CF or Let, that is the question?

          A simple rule for CF is that they should operate in any context. 



          • 2. Re: CF or Let, that is the question?

            Hi Malcolm


            Thank you.


            In this case, do you mean the rules would work for Accounts & Contacts (with city fields), but not work for Calendar, Notes, & Projects?


            Do the rules presented, negate using a CF, or am I missing the point?



            • 3. Re: CF or Let, that is the question?

              I am saying that you should write your CF so that it can be used anywhere for any suitable purpose. However, this is only a rule of thumb. You can break the rule. I have done so myself.


              If you do break the rule you should name your CF in such a way that it reminds you of it's purpose. Write extensive notes explaining it's use within the CF itself and include thorough testing to ensure that the CF is benign when it is called from the wrong context.


              The simplest thing to do is to pass all the necessary materials to the CF. Do not rely on the CF being called from the right place.



              • 4. Re: CF or Let, that is the question?

                Hi robear,


                The reasons you might choose to create a custom function are typically that 1) you have code or logic that you will use in multiple places in a file, and you don't want to have to keep repeating it, 2) that by putting complex or obscure code into a custom function you can simply it or make it easier to read as part of other calculation expressions, or 3) you want to use recursive capabilities to work through a series of iterations until one or more conditions are satisfied.


                In most other cases, regular calculation code - including Let( ) statements where appropriate - will suffice.


                Similarly, Let( ) statements are primarily useful when you have part of a calculation expression that will be used more than once to arrive at the answer (you can declare that part as a named value and then reference it so that it doesn't have to be calculated multiple times. Other than that, Let( ) statements are sometimes used for increased readability of calc syntax, or to modify local ($) or global ($$) variables.


                The basic logic of the abbreviation generation system you've outlined doesn't require either a Let( ) statement or a custom function, as it can be achieved with an expression such as:


                If(WordCount(Co. name) < 2;

                Upper(Left(Co. name; 3));

                Left(Co. name; 1) &

                Left(MiddleWords(Co. name; 2; 1); 1) &

                Left(MiddleWords(Co. name; 3; 1); 1)



                ...however the requirement to establish uniqueness and deal with conflicts by trying a number of alternatives until one that doesn't produce a conflict is found is one that might benefit from the use of recursion, so a custom function would certainly be one of the options you might consider - as would an OnModify triggered script.


                Malcolm wrote:

                A simple rule for CF is that they should operate in any context.


                It depends what you mean by "rule". It's desirable, perhaps, but not mandatory. And, FWIW, it's also extremely easy to achieve.


                Context independence in a custom function is generally established in a straightforward way by defining all the context-dependent inputs as parameters for the function. So, for example, if robear wants to turn Co. name values into unique abbreviated strings, he may require a custom function that accepts the name, a list of existing abbreviations and a list of associated cities as parameters. The syntax would therefore be something like:


                CompanyAbbreviation ( CompanyName; AbbreviationsList; CitiesList )


                In this way, just about any custom function can be constructed so as to operate in any context.





                R J Cologon, Ph.D.

                FileMaker Certified Developer

                Author, FileMaker Pro 10 Bible

                NightWing Enterprises, Melbourne, Australia



                1 of 1 people found this helpful
                • 5. Re: CF or Let, that is the question?

                  From a user perspective... if the result is to be used as an account name... I am glad you are including the digits from the company names. As a child, my father worked for 3M company and my company is 11th Hour Group so the number is significant... emotionally if nothing else.


                  One technique I have used for this kind of thing in the past was to turn the several fields I want included in the calculation to be put into a string... no spaces or punctuation... then selecting the values in prime number sequence... 1, 2, 3, 5, 7, 11, 13, 17, 19, 23, 31, 37 etc. In the series I generated there were no duplicates.


                  - Lyndsay


                  PS... a manual calculation using Let[]

                  1 of 1 people found this helpful
                  • 6. Re: CF or Let, that is the question?

                    You may want to consider using a script for generating new keys, since your rules call for repeatedly checking for existing values. It would be natural to write a script that generates a "1st draft" key, then checks for any existing matches, and revises the key until it isn't a duplicate. This could also be accomplished with a calculation (via custom function or otherwise) with the help of the List () function to get all existing key values, but looping (for the iterative revision) is usually easier to write and debug in a script than in a custom function.

                    • 7. Re: CF or Let, that is the question?

                      on 2011-12-14 23:05 robear wrote

                      While learning about building using Let (), it occured to me there is a cross over with Custom Functions!


                      Given the following rules, which is best suited to the task (Let or CF)?


                      my simple answer is that "CF or Let?" is not the question; first of all, they

                      are not exclusive; it is common to construct a CF which uses a Let() or to call

                      a CF from within a Let(); both custom functions and Let() are conveniences;

                      with a few exceptions, anything you can do with them can be done with a simple

                      expression too, so usually they are tools to help a developer manage

                      complexity, and you'll need experience to know what works for you;


                      CFs encapsulate expressions: are you re-using an expression, or similar

                      expressions which can be abstracted? then a CF may make your development more

                      efficient; abstracting a problem is work too, but with experience you can weigh

                      the comparative effort


                      Let() is just a tool for structuring expressions; it helps break down problems

                      in ways that can speed development (and maintenance in the future), but with

                      experience you may not need to break problems down as much; Let() can also do

                      certain tricks with script variables, and it can perform a short sequence of

                      operations, which can be helpful in situations where scripts can't be used


                      so it's not either/or — both tools can be useful together or separately


                      in your specific example you have already broken down the problem similarly to

                      how one would assign intermediate variables in a Let(), so the value of Let()

                      is clear; i can't tell whether you'll need to use the same expression in

                      multiple places, or to abstract it further, so i have no opinion on whether it

                      makes sense to use a CF