3 Replies Latest reply on Jun 14, 2012 5:59 AM by philmodjunk

    Inserting field data into a calculated field with repetitions

    zinny

      Title

      Inserting field data into a calculated field with repetitions

      Post

      I am attempting to create a calculation field with 3 repetitions that calculates its contents based on the content of 3 other normal fields in a record.

      Because repetitions are legacy I can’t find information on how to do this, just advice not to even go near repetitions.

      I know that repetition fields are considered legacy and dangerous but repetition field is copying field data stored elsewhere, so the data is safe. And no, a related record is not a better option because what I am trying to do is create a relationship anchor with the calculation field to then use in a relationship.

      I have a table of “personnel” who are either a pilot, crew member or manager (their roles). I have a table of “Jobs” which detail individual helicopter flights. Each flight has a child key field for a “Pilot”, “Crew”, or “Manager” with a pop up list that selects from the personnel table based on their role (linked to different global fields in the jobs table).

      In the personnel form I have all the usual stuff, phone numbers etc. but I also want to put a portal through to the Job table listing all the Jobs that each person goes on. If I use a traditional relationship then I have to have three separate portals but if I create a repeating field that includes all the child keys from the three different fields then it will display the 1 job in each of those personnel’s portals (I have checked this and it works a treat)

      I can use set field script steps to do this process but I want to do it as a calculated field. Please, how do you do this?

        • 1. Re: Inserting field data into a calculated field with repetitions
          philmodjunk

          Repeating fields aren't dangerous, just awkward and inefficient when compared to newer alternatives for most cases where you might use one. Even today, there are cases where a repeating field can be useful, but you need to understand their limitations and the advantages of possible alternatives before you can make an informed choice to use them.

          If I use a traditional relationship then I have to have three separate portals but if I create a repeating field that includes all the child keys from the three different fields then it will display the 1 job in each of those personnel’s portals (I have checked this and it works a treat)

          That is one of the cases where a repeating field is still a valid option, but a single text field with multiple match values listed and separated by returns can serve the same purpose and not be limited to the number of repetitions you define for the match field.

          I'm not sure your tables are really set up to support what you want to do. Check me on this:

          A given job record can link multiple personnel and a given personnel record can link to multiple jobs?

          If so, that's a many to many relationship and best implemented with a join table between jobs and personnel.

          Many to many relationships can be set up in FileMaker with either a repeating field (not a good idea) or a return separated list of values (better), but these options are not as flexible as using that join table.

          • 2. Re: Inserting field data into a calculated field with repetitions
            zinny

            Thanks for that Obi-Wan (the alex guinniess version),

            I had tried a return separated field but it hadn't worked and I thought repeating fields were the only way. This was because I used a calculating fields and it turns out that child keys need to be indexed.

            So the basic answer that I was looking for is that you can't use a calculated field to create a relationship with another table. Repeating field or not (I feel so dumb)

            Return deliniated fields are much better (but of course you knew that) and I've done a lot of other cute stuff with it today.

             

            Cheers

            • 3. Re: Inserting field data into a calculated field with repetitions
              philmodjunk

              But you can use a calculated field, but it must be an indexed field. A calculation field that pulls data from other tables--not what you need here cannot be stored and thus has no index. Such a field can even be used in a relationship if it is used on the "one" side of a one to many relationship.