4 Replies Latest reply on Aug 26, 2013 3:22 PM by ShaneB

    Selecting records and creating a new child record linked to selected records

    ShaneB

      Title

      Selecting records and creating a new child record linked to selected records

      Post

           My database is designed around Observation records, which users select and group together to create new Observation records. I am recording this in my database on a simple table that has child_id and a parent_id fields.

           As an example, observation ids 3, 15, 25, and 45 might be used as the parents of a new child observation, id 55. And observation ids 4,12, and 23 might be used as the parents of observation 56. So it would look like this on the table:

           Users need to be able to select records and say "make a new observation based on these records", which will then populate the table with the information. I therefore have a few questions about this:

           1) How can users "select" records, besides just searching for them? For instance, if a user runs a search, but only wants to use a few records from the search for a new child record, is there a way to do this in the FM interface?

           2) Once records are refined to a found set of some sort, through a search or a selection, how can I create the option for a user to "make a new record based on these records"? I essentially need FM to make a new record and populate the o_child_id with the new serial number of the new record, and populate the o_parent_id with all of the serial numbers of the records that were selected.

        • 1. Re: Selecting records and creating a new child record linked to selected records
          philmodjunk
               

                    My database is designed around Observation records, which users select and group together to create new Observation records.

               Do you have one table of observations or two?

               Will a given parent observation need to be linked to more than one child observation record?

               1) can't really answer that as You haven't described the process your users need to use in enough detail. Will they have to choose from a standard list of obvservations each time or a subset that is determined by ...? Will they be choosing from a large list (30 or more) or just a few? (5 or 6).

               2) That would depend on whether this is a many to many relationship or a many to one relationship--which will be determined by your answers to my first two questions.

          • 2. Re: Selecting records and creating a new child record linked to selected records
            ShaneB

                 This is my database design. Ignore the line going off the DataPoints table on the lower right, that is for an experiment for removing duplicates from an import dataset.

                 The flow of data starts with Datapoints, which is field data the researchers use to create Observations. The Datapoints have distinct fields separate from Observations, such as "datapoint_source" and other project-specific fields, and are therefore on their own table. The Observations are linked to Datapoints with the join table ObservationsDatapoints, which is simply the id of the datapoint in one column with the id of the observation the researcher creates based on it in the other. They do this on a layout I created for the Observations table that shows the matching datapoint via a portal.

                 So now I am trying to create the next step, which is using the records on the Observations table to create new records on the Observation table, but have a parent-child relationship that is documented on the ObservationRelationship table, in the way I showed in my first post.

                 

                      Will a given parent observation need to be linked to more than one child observation record?

                 Yes, a parent observation could be used for more than one child observation record.

                 

                      Will they have to choose from a standard list of obvservations each time or a subset that is determined by ...? Will they be choosing from a large list (30 or more) or just a few? (5 or 6).

                 They will have upwards of 1000 observations for each project. They then will search for things such as Themes (tags), specific text in the observation_text, and so on, and select a set of Observation records. This may be as few as 5 but as many as, say, 15, of the 1000, to create a more refined Observation. The important thing is that the researchers are able to backtrack from their refined observations to understand how they got to the end points. Does this make sense?

            • 3. Re: Selecting records and creating a new child record linked to selected records
              philmodjunk

                   It makes more sense, but It seems that you've answered most of question 1 yourself. With that many observation records, your only practical option is to perform a find or use some sort of selection portal to pull up a found set of records. Such a found set can then be further manipulated to get a final set of records. You can omit individual records by selecting Omit record. You can use constrain found set to reduce the found set by specifying additional criteria not specified in the first find. You can use extend found set to add more records with a second find.

                   And all of that can be scripted any number of different ways. You can add a button that omits the record when you click it.

                   You can also add a button or check box field to click to select it. I recommend a scripted button that adds the ID of the selected record to a list in a global field if you have more than one user making selections like this at the same time.

                   Once you have a found set refined to just those that you want to link to your new observations record, you can use a script to loop through them populating new records in a join table, but it can't be defined as you show in your screen shot.

                   Observations------<Observation_SelfJoin>-------Observations 2

                   Observations::__observations_id_pk = Observation_SelfJoin::_fkObservationID
                   Observations 2::__observations_id_pk = Observation_SelfJoin::_fkLinkedObservationID

                   Please note that Observations and Observations 2 are two table occurrences with the same data source table.

                   Then your looping script can look something like this:

                   New Record/Request
                   Set Variable [$NewObsID ; Value: __observations_id_pk ]
                   omit Record
                   Go to Record/Request/Page [first]
                   Loop
                      Set Variable [$ObsID ; Value: __observations_id_pk ]
                      Go to Layout ["Observation_SelfJoin" (Observation_SelfJoin ) ]
                      New Record/Request
                      Set Field [ Observation_SelfJoin::_fkObservationID ; $ObsID ]
                      Set Field [ Observation_SelfJoin::_fkLinkedObservationID ; $NewObsID ]
                      Go To Layout [ Original Layout ]
                      Go to Record/Request/Page [Next ; Exit after Last ]
                   End Loop
                   Enter FiInd Mode []
                   Set FIeld [ __observations_id_pk ; $NewObsID ]
                   Perform FInd []

                   I added the scripted find at the end here to pull up the new observation record so that the user can enter data as needed into the fields of this new record.

                   Since I mentioned a selection portal, here's a demo file with 3 different examples of a selection portal so that you can see what I meant by that: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

                    

              • 4. Re: Selecting records and creating a new child record linked to selected records
                ShaneB

                     This worked amazingly. I found good information on building selection/deselection/elimination buttons here: http://fmlayoutmode.com/wp/?p=126

                      

                     and then used your script and table suggestions to get this to work. Fabulous.

                      

                     Now all I need to do is figure out how to make a button that searches and displays the parents of a particular child record.