5 Replies Latest reply on Oct 4, 2011 10:14 AM by philmodjunk

    Create multiple new records by making check-box selections from two tables

    d.b

      Title

      Create multiple new records by making check-box selections from two tables

      Post

      About a year ago I received some great help setting up a comment entering script for my teacher grades database.  Here is a link to the thread:

      http://forums.filemaker.com/posts/f686b6195d

       

      That script has served me well, but I would now like to be able to enter comments for multiple students at once and possibly multiple stuents at once if easily workable.  The current script selects from a table of comments or a newly entered comment.

       

      I would like to be able to select multiple students from the current class (I am guessing he best way would be with an additional checkbox field) and record the same comment for all students checked at once.

      I'm not sure if it would be easy to create a script that also allows for multiple comments to be selected (I am guessing from another portal).  I would like to enter those comments individually (not concatenated) as some comments have a score attached to it.  And then to loop through each comment and each student and clear the check boxes when the script is completed.

      I was going to try creating the script myself and loop through every student and every comment, but then I realized that I could save some processing by first doing a find based on the check boxes, but then I thought there may be an even better method that I haven't thought of.  I make hours of mistakes with syntax before I get things working correctly when working with scripts, so it would be awesome if anyone has the time to put together a sample of what the script would look like (PhilModJunk did that for me last year and it was wonderful).

       

      I've uploaded an image that shows the current script that records individual comments one at a time in case the text below is not clear.

      RecordComment

      If [ not IsEmpty ( Enrollment_StudentsAndClasses::Student ID ) ] If [ not IsEmpty ( Scores::Comment) ]

      Show Custom Dialog [ Title: "Confirm Entry"; Message: StudentInfo 2::First Name & " " & StudentInfo 2::Last Name & " -- " & Scores::Comment; Buttons: “OK”, “Cancel” ]

      If [ Get ( LastMessageChoice ) = 2 ] Set Field [ Scores::Comment; "" ]

      Exit Script [ ] End If

      Freeze Window Set Variable [ $StudentID; Value:Scores::StudentID ] Set Variable [ $AssignmentID; Value:Scores::AssignmentID ] Set Variable [ $Comment; Value:Scores::Comment ] Set Variable [ $ScoreID; Value:Scores::ScoreID ] Set Variable [ $CommentValue; Value:Scores::CommentValue ] Set Variable [ $CommentType; Value:Scores::CommentType ] Set Field [ Scores::Comment; "" ]

      Go to Layout [ “CommentsSpecified” (CommentsSpecified) ] New Record/Request Set Field [ CommentsSpecified::Comment; $Comment ]

      Set Field [ CommentsSpecified::CommentValue; $CommentValue ]

      Set Field [ CommentsSpecified::CommentType; $CommentType ]

      Set Field [ CommentsSpecified::AssignmentID; $AssignmentID ]

      Set Field [ CommentsSpecified::StudentID; $StudentID ]

      Set Field [ CommentsSpecified::ScoreID; $ScoreID ]

      Go to Layout [ original layout ] End If

      End If

      Screen_shot_2011-10-01_at_6.51.46_PM.png

        • 1. Re: Create multiple new records by making check-box selections from two tables
          philmodjunk

          Can you use your next reply to post a screen shot of your current Manage | Database | Relationships window? I'd like to see how CommentsSpecified is linked to the other table occurrences there.

          One option to think about is that you may not want to create duplicate comment records for every selected student. If there are a number of assocated values (such as a score) that will be the same for all students getting the same comment, you may want to create a single comment record and then link all your student records to this same comment record.

          A single value check box is the easiest way to set up a method for selecting students by mouse click, but it may create issues if there are two or more people accessing the same database and making similar selections of the same records (Each user would see all the selections, not just those they made...) If this is a possible issue, let me know and I'll describe a method for selecting students where this will not happen.

          • 2. Re: Create multiple new records by making check-box selections from two tables
            d.b

            Uh, yeah.  About that... I feel like the relationships diagram has become a little messy.  I'm sure there are some unnecessary relationships in there as I have been learning as I have been creating this database over the past year.  Not sure if this is normal, but the screen shot shows what it looks like.

             

            Multiple users is not an issue.  The database is only for me.

             

            I understand what you mean about having a list of comments linked by ID rather than re-posting the text everytime.  I'm not sure if that's really necessary since almost all of the comments that are posted are very short (ex: "Talking.", "Good effort.", "Bellwork complete.", etc.).  And I also want to be able to post specific comments (ex: "Took remote control for projector and returned it without batteries.").  I'm already using this database functionally this year with the commentsSpecified table holding the text for the comments, but I'd consider adjusting the database for next year.

            The Comments table contains the options for possible comments.  CommentsSpecified contains the comments that were selected for that student for that assignment.

            Strange, I just notice that there is no relationship set up for AssignmentID from CommentsSpecified to Scores. Oh, I see, ScoreID covers the need for that.  In that case, AssignmentID is a redundant field in CommentsSpecified then.

            • 3. Re: Create multiple new records by making check-box selections from two tables
              philmodjunk

              A looping script looks pretty reasonable here, but I do have a question:

              if," The Comments table contains the options for possible comments.  CommentsSpecified contains the comments that were selected for that student for that assignment." what is the purpose of the relationship that links this table to the Scores table? This relationship is not needed if this table is only used to supply values for a value list so I'm wondering why you have a "comment" field in Scores that links to the matching field of same name in Comments.

              The growing "spider web" of relationships you have here suggest that you might find this article useful:  http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

              • 4. Re: Create multiple new records by making check-box selections from two tables
                d.b

                The comments that are used repeatedly have values for classwork or citizenship (ex: bellwork complete = +1 for classwork, talking = -1 for citizenship), so the link allows me to lookup that value.  Now that you ask, I realize that it should be linked by an ID/key value.

                 

                However, I don't think I want to use an ID/key value to link the comments because I do not want to add comments like "Field trip to ADK.", "Field trip to MCC.", "Field trip to elementary school.", "Field trip to RIT.", "Field trip to UofR.",... to the comments table.  Then I would have to scroll through all those comments to find the common comments that I use.

                • 5. Re: Create multiple new records by making check-box selections from two tables
                  philmodjunk

                  Thanks for the explanation. You can still use an ID key can be able to enter comments not listed in the table if you want. Though for a small utility table of just a few records like this, you might just "break the rules" and keep it as is as this makes it possible to use simple auto-complete enabled value list of comments.

                  To use and ID key, you'd have two fields, one for the ID number and a text field that uses a looked up value setting to copy the comment from the comment table. Then you have the option of typing in non standard comments directly into this text field while not selecting a comment ID in the ID number field's value list.