10 Replies Latest reply on Apr 7, 2009 4:39 PM by philmodjunk

    Repeating fields: to repeat or not to repeat

    JohnWolcott

      Title

      Repeating fields: to repeat or not to repeat

      Post

        One of my databases currently in use was designed in 1994 with version 2.0 and has lots of repeating fields, which was a huge asset back then, and I have dutifully upgraded it to the current version each time a new version of filemaker is released.  Today repeating fields are considered a no no.  I think there are times when they are the right solution, but perhaps I am missing something.  If so, please help me see the light so I can be a better designer.

        Consider the following fields in a database that contains data on companies: ContactLastName and Supervisors, both which are repeating fields.  I turned them into separate records in related files and have portals to bring in all the names for a given company into the company database.  Here is what I lost.  First, if I update either of the related files, it isn't reflected in the company database "modified date" and "modified by" fields.  If I had one portal, I could handle that.  With 8 portals, it becomes a bit more involved.  Second, and more important, when someone leaves the position at a given company, users tend to delete the name and not the record so now I have a number of records in the ContactLastName and Supervisors files that are blank but are still linked to the company database.  When users try to do a search for all companies where we don't have a contact last name or a supervisor, they get results that have both blank and non blank fields.  If I go into the related files and delete all the records which have blank fields, then the search produces what they expect.  So it works great at first, but over time it fails.

        Third, a minor point, recently I moved my filemaker server to a cetralized facility which entailed a new IP address and discovered about 70% of my links were lost.  The related files were fine, but the related tables had to be reestablished individually.

        Repeating fields don't do reports well.  When printing you get only the first entry.  When exporting to excel you get an extra symbol character after each repetion, and they all go into one cell.  On the other hand, they can tell you when you modified the file you are in at the time; they produce search results that users expect, and, like portals, you can search multiple names with only one entry.

        If this is an inappropriate question, or if it should be posted elsewhere, please let me know.  I did do a search on "repeating fields" and found 218 entries in this forum, but in reading the ones that didn't deal with a specific detailed question about repeating fields, I didn't see much more than "don't use repeating fields; use a portal instead."  Portals are great, but how do you handle the modified time and the blank field problems?

        • 1. Re: Repeating fields: to repeat or not to repeat
          comment_1
            

           


          JohnWolcott wrote:

          when someone leaves the position at a given company, users tend to delete the name and not the record so now I have a number of records in the ContactLastName and Supervisors files that are blank 


           

          Set field validation to 'not empty', with a message advising users to use the provided 'Delete" button.

           

           

           


          JohnWolcott wrote:

          if I update either of the related files, it isn't reflected in the company database "modified date" and "modified by" fields.


           

          I am not sure I see the need for this "centralised" modification data, but at least the date can be derived relatively simply by using the Max() function.

           



          • 2. Re: Repeating fields: to repeat or not to repeat
            philmodjunk
              

            Today repeating fields are considered a no no.  I think there are times when they are the right solution, but perhaps I am missing something.  

             

            You aren't missing something. Repeating fields have their uses. Just keep in mind that all database design options typically involve trade offs. Understanding the pros and cons of each option enables you to make the best choice. Understanding how design changes in FMP have changed things over the years is also very helpful.

             

            Consider the following fields in a database that contains data on companies: ContactLastName and Supervisors, both which are repeating fields.  I turned them into separate records in related files and have portals to bring in all the names for a given company into the company database.  Here is what I lost.  First, if I update either of the related files, it isn't reflected in the company database "modified date" and "modified by" fields.  If I had one portal, I could handle that.  With 8 portals, it becomes a bit more involved. 

             

            You've definitely identified one tradeoff due to splitting your repetitions into related table records. Since FMP now gives us tools like sub-scripts, loops and Go to related record, scripting solutions to this issue would seem manageable even when there are multiple related tables.

             

            Second, and more important, when someone leaves the position at a given company, users tend to delete the name and not the record so now I have a number of records in the ContactLastName and Supervisors files that are blank but are still linked to the company database.  When users try to do a search for all companies where we don't have a contact last name or a supervisor, they get results that have both blank and non blank fields.  If I go into the related files and delete all the records which have blank fields, then the search produces what they expect.  So it works great at first, but over time it fails.

             

            This is something that could easily be corrected through adding a script to find and delete records with blank fields set to perform automatically when a user opens or closes the file.

             

            Third, a minor point, recently I moved my filemaker server to a cetralized facility which entailed a new IP address and discovered about 70% of my links were lost.  The related files were fine, but the related tables had to be reestablished individually.

             

            I wouldn't call that minor! However, with FMP 9 and onward, you can merge your files into a single multi-table file. I've just about finished doing that with my current project. It's a job, but can be well worth it.

             

            Repeating fields don't do reports well.  When printing you get only the first entry. 

             

            I print repeating fields from one of my systems and get all repetitions. I don't see why you can't do the same.

             

            They produce search results that users expect, and, like portals, you can search multiple names with only one entry.

            Not! Maybe that changed after 5.5, but in my experience, entering search criteria in repetition 1 only searched repetition 1.

             

            I did do a search on "repeating fields" and found 218 entries in this forum, but in reading the ones that didn't deal with a specific detailed question about repeating fields, I didn't see much more than "don't use repeating fields; use a portal instead." 

             

            That's because many newcomers try to use portals for situations that are better served by using a related table via a portal. That doesn't mean that it's always the best option.

             

            Portals are great, but how do you handle the modified time and the blank field problems?

            With scripts and relationships. I usually put a delete button at the far right of a portal row to encourage users to remove an entry with a single mouse click instead of deleting the field contents.

             

            I hear you John. I've worked with FMP for years. Here is an additional advantage for repeating fields that I can think of:

            You can display repeating values horizontally instead of vertically. Sometimes horizontal is the only way that works.

             

            There are many many advantages for portals that usually far out weigh their disadvantages.

            Here's two of my favorites:

             

            Portals can be set to automatically sort their contents in a useful order that will constantly update as the contents of the portal is updated.

            Portals can be set up with a Value list formatted field so that the user can pull up different groups of records to reduce the number of records visible at one time.

             

            I use repeating fields sparingly, but sometimes they're still the best way to go. In one of my solutions, I display horizontal repeating fields in a portal in order to create a kind of "mini spreadsheet" effect! Controls in the layout manipulate the portal be creating and viewing different sets of records (portal rows) so that the user seems to be working with multiple "pages" of information.

            • 3. Re: Repeating fields: to repeat or not to repeat
              marcusedward
                

              One use for repeating fields is to replace the inability to nest portals (a portal within a portal).  

                

              I have a database that tracks projects in one table, sub projects in another and finally milestone dates in a third table that applies to the sub projects.  When I look at a project I have a portal to all the sub projects then use a repeating calc field that looks up the first 5 milestone dates for each sub project (for my purposes 5 milestones was enough to cover all my needs but this could be extended).  The repeating calc field combines the date and label of each milestone.  This gives me a verticle list of all sub projects with a horizontal list of the milestone dates and allows each sub project to have unique milestones labels.  I could have combined the milestones into the sub projects table but with the milestones in a seperate table I can use the date field as a link to my calendar view.  This allows me to see each milstone of each sub project in a calendar view.

               

              Also there was a reply about using repeating fields to do a horzontal list.  You can do this with a portals.  Create a portal with 1 row that starts at row 1.  Place the cells you want to have in the portal.  Duplicate the portal and place it directly to the right of the first portal.  Set the second portal to show 1 row that starts on row 2.  Repeat as necessary to create your horizontal portal view.  Setting this up on the layout is a little more time consuming but makes it much easier to change what information is displayed (changing the cells on a layout rather than changing the calc to a repeating field)

               

              Hope this all made sense...

               

              Marcus 

               

              • 4. Re: Repeating fields: to repeat or not to repeat
                obeechi
                  

                Repeating field also good for

                a. RGB values, where each value (for R, G, or B) is 0 to 255, and a given color is pinpointed by ( for example ) 200, 180, 255  

                b. A global container field with repeating values where you paste in color, or graphics

                • 5. Re: Repeating fields: to repeat or not to repeat
                  philmodjunk
                    

                  "Also there was a reply about using repeating fields to do a horzontal list.  You can do this with a portals.  Create a portal with 1 row that starts at row 1.  Place the cells you want to have in the portal.  Duplicate the portal and place it directly to the right of the first portal.  Set the second portal to show 1 row that starts on row 2.  Repeat as necessary to create your horizontal portal view.  Setting this up on the layout is a little more time consuming but makes it much easier to change what information is displayed (changing the cells on a layout rather than changing the calc to a repeating field)"

                   

                  This has been a good thread with lot's of good ideas being shared.

                   

                  I think I still prefer a repeating field for many of the situations where I want fields displayed horizontally. In the cases where I've used it, the data being stored didn't require any calculations so a single repeating field was much easier to format and place on the layout.

                   

                  I will keep the "horizontal portal" idea in my mental tool box for possible future use just in case...

                  • 6. Re: Repeating fields: to repeat or not to repeat
                    JohnWolcott
                       Ah, the power of distributed neurons.  Thanks for the tips.  Being slow of foot and slower of mind, how do I create a button to delete a particular record in a portal that has, say, 8 rows?  I read the Knowledge Base article 3461 on deleting records in a portal, which involves writing a script the gets the record of whatever related record the cursor is in, and deletes that record, and I read the forum discussion on adding a button to the end of a portal row. If I do the latter, and put a button at the end of each row, do I use exactly the same script for each button, that script being the script described in KB 3461, or is there a better way?
                    • 7. Re: Repeating fields: to repeat or not to repeat
                      marcusedward
                        

                      You can create a button that that performs the command "delete portal row" (with option for delete without dialog as an option).  Add that button to the end of your portal row.  This will delete the record that the row you clicked on corresponds to.  

                       

                      I believe that is the most common method used.

                       

                      Good luck,

                       

                      Marcus 

                      • 8. Re: Repeating fields: to repeat or not to repeat
                        philmodjunk
                          

                        John,

                         

                        In layout mode you'll see only one "row" to your layout just like you see only one "body" row to a list layout when in layout mode. Thus, you put one button on this one row and it'll appear in all the portal rows that have a record plus one more row if your relationship permits adding related records.

                        • 9. Re: Repeating fields: to repeat or not to repeat
                          JohnWolcott
                            

                          Thanks Marcus and Phil.  I tried both your suggestions, and they work!  According to the Filemaker help entry on the script step "Delete Portal Row" (within Filemaker client click on Help and type in the above phrase) you will be instructed to use "Go to Portal Row" to specify which row to delete when using the "Delete Portal Row."  There is a statement that "(If no portal row is selected, nothing happens when this script step executes.)"  The example uses both script steps, which is why I didn't think it would work to just add a button at the end of the row.  In fact, just adding the button at the end of the row and assigning it the script step "Delete Portal Row" worked.  Perhaps because the button is in the row, that row is automatically selected, and therefore the "Go to Portal Row" isn't necessary.

                          -John

                          • 10. Re: Repeating fields: to repeat or not to repeat
                            philmodjunk
                              

                            Perhaps because the button is in the row, that row is automatically selected, and therefore the "Go to Portal Row" isn't necessary.

                            Exactly, the act of clicking the button makes its row the current portal row.

                             

                            In other contexts, filemaker can get confused as to which portal row and even which portal is "current."

                             

                            Thus

                            go to field [PortalTO::portalfield]

                            Go to portal row [Last, first whatever]

                             

                            is needed prior to using delete portal row.

                             

                            In your case that's not needed, you don't even need a script as you can select the script step directly from the button set up dialog.