3 Replies Latest reply on Mar 16, 2015 9:50 PM by philmodjunk

    Counting occurrences in repeating fields

    WilliamDuncan

      Title

      Counting occurrences in repeating fields

      Post

      I have a simple, flat database that I use for creating certification exams for my professional association. Each question has a repeating field that records the links to the major topics. I set it up this way for inquiry purposes: by doing a "find" on the repeating field, I find all questions related to that topic.

      I would also like to be able to count the number of questions that reference each topic. I can do this manually through a series of "finds," but with almost 50 topics, that's obviously less than ideal.

      I know that I can count the total number of entries in the repeating field, but that's not what I need.

      Any ideas?

      I'm using FM Pro 13.0 on a MacBook Pro running OS X 10.10.2.

        • 1. Re: Counting occurrences in repeating fields
          philmodjunk

          I have a simple, flat database that I use for creating certification exams for my professional association. Each question has a repeating field that records the links to the major topics

          Well what you describe shouldn't be a flat database. FileMaker is a relational database for a reason. Using a repeating field for this purpose became obsolete with the release of FileMaker 3.

          By using a table of related records, one record for each repetition in your repeating field would make setting up a summary field to count the values like you want very easy to do. And converting your repeating field into such a table is actually quite easy to do. Import records can be used to copy this data into a new field and split the data in the repetitions up into separate records.

          • 2. Re: Counting occurrences in repeating fields
            WilliamDuncan

            Good suggestion. I'll consider it. I'm not sure the effort to make this file relational is worth it, but I have another database that I've set up with repeating fields that probably should be relational, so this might be a good refresher.

            • 3. Re: Counting occurrences in repeating fields
              philmodjunk

              The process is actually pretty simple in terms of moving the data, but a bit more work in terms of layout updates that may require updating one or perhaps many layouts.

              You first need a field in your table that uniquely identifies each record such as an auto-entered serial number field. If you don't have one, you can add that field and then use Replace Field Contents to assign serial numbers to your existing records.

              You then can use Import Records with the New table option to import just this serial number field and your repeating field into this new table. If you have several repeating fields where one repetition from each field forms an entity, you can specify the group of repeating fields as part of the same import. You then click the check box to split the repetitions after clicking "import".

              Final step in terms of your data is to go into Manage | Database and add a relationship linking the serial number field in your original table to the corresponding number field in your new table.

              But the real work then begins, any layouts, scripts or calculations that interact with your original repeating Field(s) have to be updated to reference your new set of related records and a portal or portals have to be added to layouts to take the place of the original repeating fields.

              PS. I've "inherited" a massive "Winchester Mystery House" of  FileMaker database with my recent new job and am looking at exactly this task for one of the files in it that should never have been set up with repeating fields in the first place...