3 Replies Latest reply on Apr 4, 2011 9:38 AM by philmodjunk

    Repeating Fields and Months

    james.gould

      Title

      Repeating Fields and Months

      Post

      Almost everything I've ever read about modern versions of filemaker says that you shouldn't use repeating fields to store data, as using a relationship is a much better solution for many reasons... However, I'm currently designing a financial system and I think that repeating fields might end all my problems! I just wanted to check I'm not going to cause myself problems later.

      Each project of undetermined length will need some financial information (e.g. Monthly budget) associated with it - preferably with a year to view at a time, where the user can easily edit the information. If I store the months as a child of the Project table it is proving difficult to get filemaker to create new 'month' record when the user types a figure into one of the month fields...

      I already know you're thinking that if the projects are of 'undetermined length' that repeating fields will put an end to it! What I'm thinking is that I have a child table consisting of years, rather than individual months. Since years usually consist of 12 months, I think that a child table named Year with a repeating field (e.g. monthlyBudget[12]) would be ideal... Then by "allowing creation of records through this relationship" if the user types a number in any of the repetitions filemaker would unbeknown to the user create the associated year for that project.

      It seems like the perfect solution, but I've been told not to use repeating fields so many times I thought I'd put it out there before getting myself into trouble!

        • 1. Re: Repeating Fields and Months
          philmodjunk

          I don't think repeating fields will be worth the trouble here. You'll likely have more trouble getting data in and out of them ( and totals correctly computed in reports), than they are worth.

          If I store the months as a child of the Project table it is proving difficult to get filemaker to create new 'month' record when the user types a figure into one of the month fields...

          Don't really see why that would be difficult to do here. Can you elaborate a bit?

          • 2. Re: Repeating Fields and Months
            james.gould

            When viewing 12 months at a time, I think I will need to setup a relationship for Jan, Feb, Mar etc... rather than just relating to one year - I'm presuming that I'm missing something...

            • 3. Re: Repeating Fields and Months
              philmodjunk

              Yep.

              You can display 12 months or more at a time like this easily:

              January 2011
                janurary data here

              February 2011
                 February data here

              March 2011
                 February data here

              And no relationships at all is required to group the data by month

              It's less easy, but you can also arrange this data in 12 columns with one column for each month. This is easier to set up in FileMaker 11, but you can also set this up in older versions. In Filemaker 11, you can set up 12 portals with the same relationship, but with a filter expression that selects for a specific month. In older versions, you would indeed need 12 relationships to get the data in to columns, but this is still easier to work with than using repeating fields.