8 Replies Latest reply on Aug 6, 2012 9:48 AM by philmodjunk

    Built-in Function/script capability from within a calculation field?



      Built-in Function/script capability from within a calculation field?


      My "Person" table consists of records imported from an external web service, and one of the things I'm trying to avoid is setting up finite array fields unknowning what the maximum limit may be. 

      So as part of the message I'm trying to construct on the fly, my "Person" record may have more than one address associated with it so it may appear as follows:


      10 Main Street¶20 Madison Avenue¶1500 Lincoln Avenue


      Springfield¶Pleasantville¶Cos Cob

      and so forth.  Anyways I want to construct my message so it can appear as follows:

      Address 1: 

      10 Main Street

      Adress 2:

      20 Madison Avenue

      and so forth.  Since my fields has a delimiter of "¶" or the newline character, I know I can use the Position function and similar to help manipulate this and will probably need to perform a script loop for until I've reached the last delimiter, so I was wondering if it's possible to have a calculated field to call a script or whatever is doable, or perhaps I would need to trigger on an event to set a field value when I change on a specific record at least.  I know I'm getting tedious here, but am trying to fulfill certain demands regarding my project here.

        • 1. Re: Built-in Function/script capability from within a calculation field?

          If you have FileMaker Advanced, you can define a custom function and since custom functions can call themselves, they can "recursively" loop through data in a field.

          However, I'm not convinced that you need to.

          I recommend that you use a script to pull this data into separate address records (or import the data into a second table in the first place) and then use a relationship to match a record in your current table to the multiple addresses. This would give you several simpler options for pulling together and displaying/printing these addresses.

          If you do use a script or custom function to extract the data from these fields, use getValue to extract all the text between the ¶'s instead of using a position function. The expressions will be much, much simpler.

          And one more observation: If you use Export Field Contents, you can export the text from one of these fields into a Tab file, then import the tab file into the related table and each value delimitted by the ¶ will be imported into a separate record. Then a replace field contents operation can update a match field to link them all to the record from which they were exported.

          • 2. Re: Built-in Function/script capability from within a calculation field?

            Yes it appears I do have custom function capability from File/Manage as I was able to do a simple function where I wanted to repeat a tab character a certain amount of times.  For a more simple example, I hope I can accomplish the following.

            I have 3 fields:

            AliasFirstName = Abe¶Bob¶Carl¶Doug
            AliasMiddleInit = E¶¶N¶
            AliasLastName = Adams¶Brown¶Cain¶Daniels

            and I would like to have an output of:

            Adams, Abe E¶Brown, Bob¶Cain, Carl N¶Daniels, Doug

            Assuming all 3 fields have the same count of delimiters I hope this can work at least.

            • 3. Re: Built-in Function/script capability from within a calculation field?

              While this can be done, I VERY STRONGLY advise against it. Your basic data structure is such that working with this data will be very cumbersome and it will be difficult to be flexible. I cannot state this too emphatically: You need to move this data into a table where each value in these lists is stored in a separate record.

              If you cannot import the data that way from your original data source, set up a script that moves the data into such a table.

              Example Script:

              Freeze Window
              Go to Record/Request/Page [first]
                  Set Variable [$K ; value: $K + 1 ]
                  Set Variable [$Fname ; Value : getvalue ( YourTable::AliasFirstName ; $K ) ]
                  Set Variable [$MI ; Value: getvalue ( YourTable::AliasMiddleInit ; $K ) ]
                  Set Variable [$Lname ; Value: getvalue ( YourTable::AliasLastName ; $K ) ]
                  Go To Layout ["Contacts" (Contacts)//name your table in a way that works for you and use those names here]
                  New Record/Request
                  Set Field [Contacts::FirstName ; $Fname ]
                  Set Field [Contacts::LastName ; $Lname ]
                  Set Field [Contacts::MiddleInitial ; $MI ]
                  Go To layout [ <original layout>]
                  Go to Record/Request/page [ next ; exit after last]
              End Loop

              Then, in Contacts, you can define this calculation field to get the full name:

              Trim ( LastName & ", " & FirstName & " " & MiddleInitial )

              • 4. Re: Built-in Function/script capability from within a calculation field?
                Split it properly to related records  ONCE or split it repeatedly EVERY TIME it is displayed ... Over and over evaluating again and again for all reports, all relationships,  all needs.  :-)
                • 5. Re: Built-in Function/script capability from within a calculation field?

                  The following Custom Function seems to work when I gave it a try:

                  v2NameFull (ALast; AFirst; AMiddle; Num ) =

                  Case ( Num <= PatternCount(ALast;"¶") + 1 ;
                         Trim(GetValue(ALast;Num) & ", " & GetValue(AFirst;Num) & " " & GetValue(AMiddle;Num)) &
                         If ( Num <= PatternCount(ALast;"¶") ; "¶" & Repeat(Char(9) ; 4) ; "" ) &
                        v2NameFull (ALast;AFirst;AMiddle;Num + 1)
                  (Note: "Repeat" is another custom function that enables me to repeat a character a certain number of times)


                  The one last thing I believe I need to do is have a calculation field from the record that has a one to many relation so that it returns all the values from a specific field or calculation field.


                  Case record contains a calculated field intended to display "Subject¶Protectee¶Associate 1¶Associate 2".

                  Person(s) records if there are 4 records may contain PersonID = "Subject", "Protectee", "Associate 1", "Associate 2"

                  I have a relation setup at least, I was also wondering if it's possible to have a varying relation where I can change a constant value from the Case record if possible.

                  • 6. Re: Built-in Function/script capability from within a calculation field?

                    Best thing to use is to define an auto-entered serial number field in your main table and have your script that parses the data use set variable/Set Field to copy that value into the related records.

                    Not sure what you have in mind by a "varying relation". Can you post an example? I will note that you can define more than one relationship between tables and if you have FileMaker 11 or newer, you can set up a filtered portal where user input modifies the data used in the portal filter and thus differernt related records are excluded from the portal.

                    • 7. Re: Built-in Function/script capability from within a calculation field?

                      I'm still using filemaker 11, am hoping to use 12 at some point soon.

                      Here's an example where I have 3 related records.  In most cases it will be 1 or 2 but sometimes 3 or more:


                      CFQPerson::MessageNumber = CFQCase::MessageNumber

                      Each record in CFQPerson will have a unique "Mindex" field starting from 0 on up.  I'm showing the "Personid" field below as an example of different values that can occur.

                      Main Record:
                      CFQCase::MessageNumber = "1000605"

                      Person Record 1:
                      CFQPerson::MessageNumber = "1000605"
                      CFQPerson::Mindex = 0
                      CFQPerson::Personid = "PRTEE01"

                      Person Record 2:
                      CFQPerson::MessageNumber = "1000605"
                      CFQPerson::Mindex = 1
                      CFQPerson::Personid = "PRTEE02"

                      Person Record 3:
                      CFQPerson::MessageNumber = "1000605"
                      CFQPerson::Mindex = 2
                      CFQPerson::Personid = "SUBJECT"


                      • 8. Re: Built-in Function/script capability from within a calculation field?

                        Assuming that you have this relationship:

                        CFQCase::MessageNumber = CFQPerson::MessageNumber

                        You can set up a filtered portal to CFQPerson and selectively filter for 1 or more Mindex values or any other field in CFQPerson.

                        You could also set up a relationship to a different table occurrence of CFQPerson that includes a global field formatted as a checkbox so that clicking a check box value includes that item in a portal to this new table occurrence. You could thus click 0 and 1 in the check box field and see records with mindex = 0 or mindex = 1, but exclude mindex = 2.