3 Replies Latest reply on Aug 5, 2012 7:39 AM by LaRetta_1

    Are There Any Alternatives to Multiple Repeating Conditional Lists to Do This?

    KerillEzzy

      Title

      Are There Any Alternatives to Multiple Repeating Conditional Lists to Do This?

      Post

      I am just above novice on FMPro and am running 12.

      I am currently buidling an Archive Tracking DB for theatrical performances. The DB needs multiple fields that allow for the alterations in the show according to technical and artistic faults and errors. Often there are up to 10 different reasons for a show to be modified. Theses events can also occur on their own or in any combination

      So far, I have set up one conditinoal value list that allows for a breakdown of Department in one field and then Fault/Modification in the second field.

      The issue is that I need to use the same Departments and same Faults/Modification in a series of 9 more field pairs as I have already set up and then set up a search that allows the end user to be able to search for a specific combination

      I know that it is not efficient to have to set up a further 9 tables to repeat the information in order to make 9 more conditional value lists that are identical to the first one that I have created but I can't seem to see any other way around doing what I need to do. This would also mean that the user, if adding faults etc would need to remember to duplication that in all the tables which is hardly efficient and naturally leaves a lot open to human error.

      I am hoping I am making sense here but I have looked on this forum and on others and don't seem to see anyone who has the same issue or perhaps they just word their query better.

      As an aside, I also know that I will probably have to set up a specific search so that they can do what they want to do to search for a multiple fault/modification scenario.

      Any advise that anyone is most welcome.Undecided

      Cheers.

        • 1. Re: Are There Any Alternatives to Multiple Repeating Conditional Lists to Do This?
          philmodjunk

          The issue is that I need to use the same Departments and same Faults/Modification in a series of 9 more field pairs as I have already set up and then set up a search that allows the end user to be able to search for a specific combination

          That's the first issue. Setting up a related table where you can create and edit 9 or more related records will simplify your database design and be more flexible as well. In terms of your conditional value list, this will leave you with one relationship to your related table instead of 9 different relationships as would be required with your current set up.

          I know that it is not efficient to have to set up a further 9 tables to repeat the information in order to make 9 more conditional value lists...

          Even if you stay with your 9 sets of fields in one record structure, you would not need 9 identical tables storing the same information. Open Manage | database | Relationships and click one of the table occurrence "boxes" to select it. Then click the duplicate button (two green plus signs). This creates a duplicate reference to the same table so that you can use it in relationships, but does not duplicate any actual tables.

          • 2. Re: Are There Any Alternatives to Multiple Repeating Conditional Lists to Do This?
            LaRetta_1
            Since you have v12 you can use ExecuteSQL() To easily create conditional value lists using unstored calculation for your value list.  This website wil not allow me to paste from iPad but just google for FM12 ExecuteSQL unconference.
            • 3. Re: Are There Any Alternatives to Multiple Repeating Conditional Lists to Do This?
              LaRetta_1
              By the way ... for anyone using v12 this will change the way you work.  I first saw unstored calc used for value list 3 years ago by Michael Horak (Comment).  Powering it with ExecuteSQL makes it MIND-BLOWING.