4 Replies Latest reply on Sep 17, 2011 6:42 AM by jimb

    Creating multiple related records via script

    jimb

      Title

      Creating multiple related records via script

      Post

      I'm fairly new to Filemaker and am developing a database whose purpose is to support analysis of a set of student essays by expert raters. I'm using Filemaker Pro 11 Advanced.

      The essay analyses occur at two levels: holistic, and segment-by-segment. An existing table in the database (Essays) holds information about each essay, and a related table (Segments) holds information about each segment (i.e., a sentence or phrase). When analysts select a record in a layout based on the Essays table, they see information about the selected essay and, in a portal, its related segments. By clicking on a "new analysis" button, they create a new record into which they can insert their analysis of the essay. The logic is one-to-many: an individual essay can be analyzed by multiple users, and each analysis is a separate record in an EssayAnalyses table.

      So far, I've been able to script this button to create a new record in the EssayAnalysis table, linked to the particular Essay via its primary key (i.e., by using Set Variable to grab the EssayID from the Essays table, creating a new record in the EssayAnalysis table, and using Set Field to populate a foreign key field in the new record).

      What I'd like also to be able to do is to have this script, at the same time, create multiple related records for segment-by-segment analysis information, in a SegmentAnalyes table. The end result should be to create a two-level structure of analysis tables -- EssayAnalyses and SegmentAnalyses -- that parallels the structure of the Essays and Segments tables. The analysis editing and viewing layouts would be based on the EssayAnalyses table, with SegmentAnalyses information accessed via a portal.

      It's my understanding that to make this two-level creation of analysis records work, I'd need to do something like the following:

      1. find the entries in the Segments table that have the same EssayID as the selected essay.

      2. step through each of the matching records in the Segments table, grab its primary key (SegmentID) and create a new record with that SegmentID as a foreign key in the SegmentAnalysis table.

      Trouble is, I can't figure out the logic that will enable me to do this. Is this possibly something that the 'Repetitions' feature of Variables would be useful for? Is a looping approach needed?

      Thanks for any pointers,

      Jim

        • 1. Re: Creating multiple related records via script
          philmodjunk

          You need to use a loop. You can Perform a Find or use Go To Related Records to pull up the set of records, then loop through them. You may find it useful to use Duplicate record if the new records you create are part of the same table and you have multiple fields with data you want to keep the same except that the duplicated records get a new foreign key.

          Here's a link that includes such a duplicate records script used to duplicate an Product record and its associated Bill of material records:

          Duplicating Bill Of Materials (duplicating portal line items)

          • 2. Re: Creating multiple related records via script
            jimb

            Thanks for the tip, PhilModJunk. Your advice, together with a nicely detailed post on looping approaches I located elsewhere -- http://filemakerweetbix.wordpress.com/2009/06/12/thinking-outside-the-loop/ -- got me most of the way there. The other aspect I needed to wrap my head around was simply the process of scripting the "find" I needed to bring up the right subset of records. I hadn't realized that even when passing variables, it is necessary to switch into find mode, run the find by plugging the variable into the field, then return to browse mode, all invisibly within the script.

            FWIW, here's the script I ended up writing. I'd appreciate if anyone could run an eye over it and let me know if it's got any issues that might cause me grief in use, keeping in mind that this is being run in the context of a web-enabled multi-user solution employing IWP.

            Jim

            Enter Browse Mode
            Set Variable [ $EssayID; Value:Essays::TaggedID ]
            Go to Layout [ “Analysis” (HolisticAnalyses) ]
            New Record/Request
            Set Field [ HolisticAnalyses::EssayID; $EssayID ]
            Commit Records/Requests [ Skip data entry validation; No dialog ]
            Set Variable [ $AnalysisID; Value:HolisticAnalyses::zID ]
            Set Variable [ $AnalystID; Value:HolisticAnalyses::zCreator ]
            Go to Layout [ “Parses” (Parses) ]
            Enter Find Mode [ ]
            Set Field [ Parses::EssayID; $EssayID ]
            Perform Find [ ]
            Enter Browse Mode
            Go to Record/Request/Page [ First ]
            Loop Set Variable [ $ParseID; Value:Parses::zID ]
            Go to Layout [ “ParseAnalyses” (ParseAnalyses) ]
            New Record/Request
            Set Field [ ParseAnalyses::EssayID; $EssayID ]
            Set Field [ ParseAnalyses::ParseID; $ParseID ]
            Set Field [ ParseAnalyses::HolisticAnalysisID; $AnalysisID ]
            Set Field [ ParseAnalyses::zCreator; $AnalystID ]
            Go to Layout [ “Parses” (Parses) ]
            Go to Record/Request/Page[ Next; Exit after last ]
            End Loop
            Go to Layout [ “Analysis” (HolisticAnalyses) ]
            View As [ View as Form ]

            • 3. Re: Creating multiple related records via script
              philmodjunk

              Nothing wrong with that script as far as a quick scan of it tells me...

              You may or may not need to perform a find when changing layouts, and in some cases, you can use Go To Related Records instead of performing a find to pull up the desired group of records.

              All layouts that refer to the same table occurrence (one of the 'boxes' in Manage | Database | relationships) in layout setup | Show Records From will share the same found set, current record and sort order as long as you stay in the same file maker window. Thus, there are times when you can switch layouts and not need to perform a find.

              In the case where you have related records in a portal, Go To related records can be a one step script to pull up the desired records, but be careful to check and see if any related records exist or check for an error code if your script manipulates any data or deletes records after the Go TO Related records step. If there are no related records, Filemaker will stay on the current layout and continue executing the script--which could thoroughly screw up one of your tables if you have steps like Delete All Records or Duplicate Record following it.

              • 4. Re: Creating multiple related records via script
                jimb

                Thanks for the sage advice. I'll keep the caveat about Go to Related Records in mind.