4 Replies Latest reply on Jul 21, 2016 8:29 AM by jplfrogs

    Nested ID records

    jplfrogs

      HI All,

      I'm in the process of developing a database to manage project information for a conservation organizations (any developers doing probono work it would be great to talk with you..). I need to capture information on project objectives and activities. I have project table that is related to an objectives table which in turn is related to an activities tables. One project will have multiple objectives and one objective will have multiple activities. I need to automatically a generate objective and activity numbers. Activities number will be based off the objectives and objectives will start from 1 on each project. The table below might explain this better.

      project
      objectivesactivities
      a
      1
      1.1
      1.2
      2
      2.1
      2.1
      b
      1
      1.1
      1.2
      1.3

      Any help would be much appreciated.

        • 1. Re: Nested ID records
          DavidJondreau

          What specifically are you having trouble accomplishing?

           

          Objectives should have a field for Project ID. Activities should have a field for Objective ID and probably Project ID as well.

           

          The 'numbers' would be generated by using a relationship to look through a relationship and grab the highest current number for the Objective that shares that Project ID. This could be a scripted process as well.

          • 2. Re: Nested ID records
            jplfrogs

            Thanks for the quick response.

            I need to know how to automatically populated the Objective ID and Activities ID based on that. I'm guessing a script is the way to go but not sure what to search for in order to find an example to work off. I'm new to Filemaker!!

            • 3. Re: Nested ID records
              philmodjunk

              There are a number of variations of the following method possible:

               

              Use a script to create new Activities record.

               

              Run this script from the context of the Objectives record to which you plan to link the new Activities record:

              Set variable [$ObjectiveID ; value: Objectives::ObjectiveID ] //this should be the primary key field in objectives

              Set variable [$ActivityIDLabel ; value: Max ( activities::LabelNumb ) + 1 ]

              Go to layout ["Activities" (Activities) ]

              New Record/Request

              Set Field [Activities::ObjectiveID ; $ObjectiveID ]

              Set Field [Activities::LabelID ; $ActivityIDLabel ]
              Go to Layout [original layout]

               

              A calculation field in Activities can then combine ObjectiveID with LabelID to produce the desired result:

              ObjectiveID & "." & LabelID

               

              Please note that this value is NOT used to link the activities record to a given objective in a relationship. It is purely used for display and possibly search/sort purposes. It also is not the best choice for use as a primary key in activities.

               

              Also note that I have provided a very simple way of creating related records. There are several other methods that can be used to produce the same results that can be a better, but more complicated option as the other methods can avoid tripping script triggers or changing focus on the current layout.

              • 4. Re: Nested ID records
                jplfrogs

                This is great, thank you very much. I will have a play and see if I can get it to work. I already have the relationships built in so it is just a matter of generating this new number. I have a feeling this isn't the best way of doing it but will meet our needs for now.

                thanks again