1 2 Previous Next 15 Replies Latest reply on Jan 25, 2015 6:39 PM by robear

    repeating fields


      how do you calculate repeating fields?

        • 1. Re: repeating fields

          What specifically are you trying to do? Set the value of the field, use the value in a particular repetition, aggregate the values of all of the repetitions, etc ?

          • 2. Re: repeating fields

            You should use repeating fields with great caution, however the attached screenshot shows there are three repeating functions available so you should investigate those to see if they answer your question.


            Be aware, though, that there is almost certainly a better way of doing whatever it is you are trying to do with a repeating field(s).


            Screen Shot 2015-01-16 at 9.33.48 am.png

            • 3. Re: repeating fields
              Stephen Huston

              I agree with the above posts, that there are a few functions which will work if you must use repeating fields, but that there are almost no reasons left to use repeating fields anymore. I've been at this over 25 years, and haven't used repeating fields for anything for nearly a decade with the better options which have become available in FMPro.


              For what are you needing to use them?

              • 4. Re: repeating fields

                I would add the very useful Get(CalculationRepetitionNumber) which leads to calculations like this one:


                Let ( [

                crn = Get(CalculationRepetitionNumber);

                val = Time(6;55;900 * crn) ;

                mytime = Time( Hour(val); Minute(val) ;0);

                mycolor = RulesThatApplyToMeAllDay::RuleColor * (mytime  ≥ RulesThatApplyToMeAllDay::RuleStartTime and mytime  ≤ RulesThatApplyToMeAllDay::RuleEndTime)];





                • 5. Re: repeating fields

                  avoid repeating fields if you can, but they do have the occasional use eg a calendar. as always it just depends etc.

                  • 6. Re: repeating fields

                    I do not agree with the general assumption that repeating fields are problematic.


                    The idea that you should avoid repeating fields is a historic misunderstanding from the time (pre version 3) when FileMaker was not a relational database. At that (pre-historic) time some did, as an example, have a company card with employees registered in repeating fields (one for name_first, name_last, telephone, email etc etc). This was of course a disaster!

                    Thus the general view that you should stop using repeating fields.


                    And therefore here another way to say it: Always avoid repeating fields if you can see that they are handling related data. Here a portal will work as a repeating field without breaking your schema.

                    But do use them if they are giving you what you need to structure/layout and give a better user interfase ... for everything not related.


                    And now ... here I can accept that a lot of you for principal reasons disagree: If it is not a part of the main system-bearing schema and ERD for occasional handling af many to many purposes ... avoiding a middle table. BUT only for small ad-hoc solutions where you need this very fast. If it is more than that ... stick to the middle of the road:-)

                    • 7. Re: repeating fields

                      I never use a repeater as a replacement for properly normalized schema.


                      But I do not avoid them as they are useful.


                      Avoiding repeaters because of a knowledge gap is like choosing an axe to cut down a tree because you don't know how to use the chainsaw that you have.


                      Repetitions of variables are also useful.


                      Check out briandunnings repository of custom functions for insight on how to do calc with repetitions.

                      • 8. Re: repeating fields

                        Carsten Levin wrote:

                        And therefore here another way to say it: Always avoid repeating fields if you can see that they are handling related data.


                        IMHO, you can put it more succinctly: do not use repeating fields to store business data.


                        Repeating fields are great to handle the odd calendar, calculate a bunch of UI messages, store associated graphics (iconBlack / iconGrey), even create a cross-tab report, and whatnot; but that's for UI and derived data.


                        btw, while the community is in hot debate, the OP is never heard from again, or what …?

                        • 9. Re: repeating fields

                          erolst wrote:

                          Carsten Levin wrote:

                          And therefore here another way to say it: Always avoid repeating fields if you can see that they are handling related data.


                          IMHO, you can put it more succinctly: do not use repeating fields to store business data.


                          I would not say that as absolutely has you have done. While I avoid repeating fields for reasons others have given before, they proved in one application very useful if it comes to searching to in some sense "related" data that must be kept together and for which some, but non-uniform data structure exists. In addition, if searched over the Web, the search must be very fast and can't therefore be done in a related table which is a performance killer.


                          The screenshot below shows sort of a key-value type model for journals and series. It is done with three repeating fields, one used for the keys (i.e. the "field names"), one for the values (title, abbreviated title, title variant(s), coden, preceding title, succeding title, whatever ...), and the last for keys that link to other journal records related to the given record.



                          This model is very flexible and allows to add many title sub-items or variants, of which the number and type a priori is not known and may change over time (e.g. because of title changes, journal mergers, journal splits, and so on). Newly introduced properties (e.g. "field names" and associated values) can be added on the fly without changing the underlying data model and without having to adapt a calculated field that combines the values of individual fields - the information is all in one field that is indexed, is in the main table and therefore can be searched fast on the Web.


                          From this example, the German reader also recognizes that there are two typos in the last line (GESSELSCHAFT instead of Gesellschaft). This wrong data was added on purpose because users copy-paste this string from another database (Web of Science, where it is wrong on hundreds of articles) into the search bar of this system. It helps to find the record, but is not displayed:




                          Also, the search with the title abbreviation yields all records for this journal, because it is stored either as "main" or as "related" information:




                          Information retrieval systems such as library catalogs usually stuff as much as possible into one field to allow this searching flexibility. Aleph, which is the library system with the most installations world-wide (sold by Ex-Libris) and which is based on Oracle, uses one big, segmented field for the catalog record.

                          • 10. Re: repeating fields

                            Repetitions with Fields and Variables can both be a valuable tool to be used in development and should not be outright discarded so long as you are aware of the advantages and drawbacks. 

                            Both are referenced in the same way in a calculation:




                            Repetitions in both regular fields and calculation fields work just fine. However, you should avoid repetitions within Auto-Enter Calculations since this is one area that does not yet correctly deal with repetitions as far as I know.


                            Understanding the Extend() function is really key when referencing normal (non-repeating field data) from within a calculation field with repetitions set.  If you forget to use this, and the calc happens to be evaluating the 4th repetition for example, it will attempt to reference a repetition value of 4 in a field that does not contain any repetitions, which returns an empty value.

                            • 11. Re: repeating fields

                              WillM.Baker with Beezwax wrote a useful blog post about repeating fields a few years back that I have referred to in the past:

                              Working with Repeating Fields | the beezwax buzz

                              • 12. Re: repeating fields

                                One more consideration: there is only one way to fill an array (I like calling repeating fields arrays) in one shot, with another array already containing values: the lookup. Why would you like to do that ? Well, mostly in order to empty an existing array. I have such empty arrays at hand and with a simple relookup I can clear all the current array's cells impressively faster than any other method.

                                • 13. Re: repeating fields

                                  Another use of arrays is "dynamic" buttons. Example:


                                  In our internal ticketing system we display a quicklist of 5 most probable clients for a new ticket. Each coworker has his own quicklist and can choose to fill it with 1) a custom list, or 2) last 5 clients or 3) the 5 clients he most worked for.


                                  2) and 3) do not contain duplicate client names and are calculated on demand with ExecuteSQL. The results are stored in arrays of length 5 and each array has a trigger attached to it - a script that returns the repetition number followed by a go to Field[].


                                  Besides that, I think that erolst perfectly nailed it: do not use arrays for business data. But do think about them for the interface.

                                  • 14. Re: repeating fields

                                    I recently designed a report using repeating fields that was very easy to script whereas using individual fields was a nightmare.


                                    It was a monthly summary so there were 12 columns.

                                    There were more than a dozen different rows with complex sums.

                                    It had to be updated regularly. The client didn't want a list report he wanted a single layout designed to look like a spreadsheet.


                                    I did it the hard way first using fields. Pain.

                                    Then I rewrote it using repeating fields.

                                    Row one was a repeating field

                                    Row two was a repeating field.



                                    A simple loop grabbed the totals and put them in the correct cell. It was so easy....


                                    This was a good use of a repeating field. Using one to create an invoice like we did in the begginng is less of a good use.

                                    1 2 Previous Next