Repeating fields: to repeat or not to repeat
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?