1 2 Previous Next 22 Replies Latest reply on May 24, 2012 8:08 PM by RobertRichmond

    Using records from another file to "Pre fill in" values on a form

    RobertRichmond

      Title

      Using records from another file to "Pre fill in" values on a form

      Post

      Hello all,

      Been developing a database for our College and I have come across a problem I am not sure how to resolve as yet. I thought I might post a message here to see if I can get some ideas.

       

      The College has had me build a homework support database. The purpose of the database is to provide a quick and simple way, for a teacher to enter a homework record for a student who did not complete the requested work the night before. The student goes to a homework room with a supervising teacher, where they complete the work requested.

      We curently use Filemaker for all the student - teacher records, so it seemed the best option as it already had the information we required within it.

      I have built the area where the staff can search through the records (by importing records form the database) so I can test it. I have also built the supervisor area. Here the supervisor can check that the student has completed their work, and mark it as done.

      The problem I am trying to tackle now is HOW to get the records into the database in the first place!

      The exisitng database has a table called students, where the Student ID is located. I use this to uniquely identify each student, and collect the student name etc from this table. This table also has the subjects that the student attends. I have set a relationship between this table and mine.

      The existing database also has a table called staff where I get the staff code from. I also collect the staff members name from ths table. This table also has fields in it which name the classes that the staff member teaches. I have set a relationship between this table and mine.

      What I envisage ideally, is a button called "Homework Entry", that the staff member clicks. This button would be tied to a script, and this is the section I need a hand with.

      The script needs to get the staff members name, and then display to the user the list of subjects that they teach. When they select the subject they want to use, it then displays a list of students that are enrolled in this subject.

      The staff member then needs to select the students from a list. There may be one, two, six.. twenty student etc... this number will change.

      I would then like in list view, the student records to be displayed, along with the staff members name. The staff member then needs to enter the students homework and exit.

      The reason for doing it this way, was that we are trying to make it as quick as as easy as possible for the staff to get the information into the database, without needed to look up the students details, enter it in, find the next student, re enter it in etc....  

      Does anyone know how I might be able to create something like this?

      Thanks

      P.S.  have been reading Filemaker Pro 11 - The missing manual, but I am only up to Understanding calculations (chapter 8), and haven't noticed anything about this specific problem just yet.......

        • 1. Re: Using records from another file to "Pre fill in" values on a form
          philmodjunk

          You have structural issues:

          This table also has the subjects that the student attends.

          This table also has fields in it which name the classes that the staff member teaches.

          It would be better to list student subjects and teacher classes in related tables, not with separate fields in the same record. This design detail greatly complicates your task here.

          WIth a different structure, most of what you want here can be done with a combination of conditional value lists and portals with little or no scripting.

          Using what you have to work with at the moment:

          The script needs to get the staff members name, and then display to the user the list of subjects that they teach. When they select the subject they want to use, it then displays a list of students that are enrolled in this subject.

          You can set up a list view of all currently employeed staff members where each row in the list is a different record for one staff member. If you format the fields in the row as a button, clicking that button can switch the user to a layout with form view of the same record to list all their current classes.

          Each of these fields can be buttons. Clicking them runs the same script but with the contents of the clipped field passed as a script parameter ot a script. This script can then perform a find to pull up a list of those students. This can then be a list view layout based on the students table. Clicking the student name (make the field a button like the teacher list) can add the student's ID to a list in a variable selecting them for the "homework detention". A conditional format can make a color or other format change to show which students are selected and clicking a student name a second time can remove them from the list as a simple way for users to correct errors.

          The screen where the staff member enters the homework assignment should be a a new table where one record documents once such assignment. Your script can enter a teacher and student ID to link it to the teacher and student tables with additional fields to describe the assignment and document when it is completed.

          I've only outlined this rather than spell out every detail. Given that you're still learning how to use FileMaker, you may want to hold off on taking on such a challenging task until you have acquired more training on how to put a project like this together.

          • 2. Re: Using records from another file to "Pre fill in" values on a form
            RobertRichmond

            Hi Phil,

            Thanks. I'd like to think that If I am persistent, I'll get there eventually! Laughing

            I am now at the stage where I have the list of students in one portal with the student ID and the teacher staff code at the top of the page.  What I'd like to know how to do now is to take these to values and add them to a temporary table via a button which is listed against each item in the portal. When the person click the button for the student, it will then add the record into the table which then displays it in the other portal.

            So what I really need to know is how do I copy information from area into another table? I noptice that in some locations the original database has the Set Field value being used...but this doesn't seem to be writing the values into my table?

             

            I think If I can copy the vales between these tables, I should be right to complete the project as that is all I have left to figure out.....

            • 3. Re: Using records from another file to "Pre fill in" values on a form
              philmodjunk

              Think of it this way. Your script needs to put the values in a pair of buckets, carrry those "buckets" over to the layout to the assignments table and then uses set field to copy the data out of the buckets into the fields of a newly created record on the assignments layout. The "buckets" you will use in this case are variables.

              A button in the portal row (or the fields in the portal row configured as buttons) can perform this script:

              Set Variable [$StudentID ; Value: PortalTable::StudentID ]
              Set Variable [$TeacherID ; Value: LayoutTable::StaffCode]
              Go To Layout [Assignments]
              New Record/Request
              Set field [ Assignments::StaffCode; $TeacherID]
              Set Field [Assignments::StudentID ; $StudentID]

              Please use your table and field names in place of mine. With this script example, I am assuming that the "Teacher staff code at the top of the page" means that this is a field that is defined in the same table as that referred to in Layout Setup | Show Records From.

              • 4. Re: Using records from another file to "Pre fill in" values on a form
                RobertRichmond

                Hi PhilModJunk,

                Thanks. That got it working well! I almost have everything working now.

                Part of the solution I am building needs to offer a quick way for a staff member to create homework records for students. Staff don't have a lot of time to type in the homework for each student, espeically when most of the time they are exactly the same.

                What I now have is a form that shows the student records in list view. The staff are then able to add the homework for each student into each list items field. What I need to offer the staff, is a way to duplicate the first students homework, throughout the rest of the records being diplayed.

                I want to use a button, that says 'Duplicate Homework'. When the staff member presses it, I want Filemaker to copy the homework entered in the first listed students homework field, to the rest of the displayed records. 

                How might I do this? I have listed the students by the 'Student Full Name' field, so the first record will the first displayed alphabetically.

                 

                Thanks for your help.

                • 5. Re: Using records from another file to "Pre fill in" values on a form
                  philmodjunk

                  Take a look at replace field contents.

                  It can update a field in the current found set of records so that all the records have the same value as that of the current record.

                  This is a tool that can radically change data in lots of records in one batch and you can't undo it, so make back up copies when testing this out to learn how it works. (See Filemaker Help for more on this tool.)

                  • 6. Re: Using records from another file to "Pre fill in" values on a form
                    RobertRichmond

                    Thanks. This works well.

                    I think this is the last issue I need to address:

                    At the College we have certain days where a student could attend homework support. When I submit the records into the database, I want to ensure that the date and time that the student is listed to attend is correct.

                    The information I have to work with is this:

                    If the current time is before 1:30PM, record todays date.

                    If the current time is after 1:30 pm and it is not a Friday, record tommorrow's date.

                    If the current time is after 1:30 pm and it is a Friday, record the date two days later (Monday). 

                     

                    Whilst this method is not bullet proof (Monday could be a public holiday..Could be a school holiday etc) This should get the information correct most of the time.

                    (It would be great if I could tell it the College holidays so it didn't use these?)

                    • 7. Re: Using records from another file to "Pre fill in" values on a form
                      philmodjunk

                      We have to work with dates and company holidays in a similar fashion here. In our case, we have to tell certain customers that they can't get paid immediately for the scrap they brought in due to a manditory 3 day waiting period specified by California State Law. Our calc has to compute the waiting period but then must adjust for weekends and holidays to produce the expected date. We use a holiday calendar tool to track what dates are available and the calculation uses the count of related records to "days we are open" to determine the earliest date the customer can return for their money.

                      Let's ignore that option to start and set up a more simple approach:

                      Case ( Get ( CurrentTime ) < GetASTime ( "1:30 pm" ) ; Get ( currentDate ) ; // time is before 1:30 pm
                                Day ( get ( currentDate ) )  ≠ 6 ; Get ( CurrentDate ) + 1;                   // Date is not a Friday
                                /* else */ Get ( CurrentDate ) + 3                                                    // date is a Friday
                               ) //end case

                      You can use this calc in a set field or possibly an auto-enter calc to set the date appropriate for the time.

                      • 8. Re: Using records from another file to "Pre fill in" values on a form
                        RobertRichmond

                        Well when I started this project, I wasn't to sure of my ability to be able to complete it, but I figured if I took it small steps at a time, I'd get there.

                        Thank you very much Phil. Your assistance with this project has been great. I really do appreciate the assistance you have given me with it!! Today I completed the project. Whilst it is no "whiz bang" project, personally I am very happy with the result. I have tested all the different areas, and it all "seems" to be working. The next part I have to do is to add it to the real server we are using and make it go live. 

                        I have tested it on the test server I have here, and it all seems to tie in without any issue to our existing database.

                        One item I would like to add now is this:

                        When a teacher has completed entering all the data in, and there are no records left, they get a blank screen, where the records would normally show. I would like to add some text when there are no records, that says something like: " All Homework records have been completed ".

                        Is there a way such that if there are no records in a displayed portal, that some text can be displayed?

                        • 9. Re: Using records from another file to "Pre fill in" values on a form
                          RobertRichmond

                          Oh...And How do I reset the database so it has no values in it?  Laughing

                          • 10. Re: Using records from another file to "Pre fill in" values on a form
                            philmodjunk

                            Choose Save a copy as from the file menu, then specify a clone.

                            • 11. Re: Using records from another file to "Pre fill in" values on a form
                              RobertRichmond

                              I have tested it on the test server I have here, and it all seems to tie in without any issue to our existing database.

                              One item I would like to add now is this:

                              When a teacher has completed entering all the data in, and there are no records left, they get a blank screen, where the records would normally show. I would like to add some text when there are no records, that says something like: " All Homework records have been completed ".

                              Is there a way such that if there are no records in a displayed portal, that some text can be displayed?

                              • 12. Re: Using records from another file to "Pre fill in" values on a form
                                philmodjunk

                                Enter layout mode and use the text tool (Capital T button) to add this text to your layout in the appropriate spot:

                                All Homework records have been completed.

                                Then give it this conditional format expression:

                                Get ( FoundCount )

                                For this expression, click More Formatting...

                                and specify a font size of 500.

                                With this setting, the text will be invisible when you have 1 or more records in your found count. With no records in your found count, it will appear.

                                Another option would be to modify the script that is omitting records one by one as the teacher works with them to check the found count and use Show Custom Dialog to display this message when there are no more Homework records.

                                • 13. Re: Using records from another file to "Pre fill in" values on a form
                                  RobertRichmond

                                  Thanks Phil.

                                  Within the Homework entry area, I have created a button which I aim to work like the following:

                                  The teacher clicks the Homework Entry (By User) button, and Filemaker goes and finds out the username (or is it accountname?) of the current logged in user. It then Searches the "Staff for classes::Account Login Name" details for a matching record, and displays this record.

                                  Currently I am going to the Layout required, and then performing a find records when: "Staff for Classes::Account login name" has Criteria "Get (username)" This isn't working. I also tried the same but changed the Criteria to "Get (accountname)" and it also failed.

                                  I can see all the Account login name fields. How can I see the loginname or the account name values to I can see if I am matching correctly?

                                  • 14. Re: Using records from another file to "Pre fill in" values on a form
                                    philmodjunk

                                    You want the account name. The username is a name specified in Preferences so the same user could have a different user name just by using a different computer or logging into a different computer user account.

                                    Exactly how does it "fail"?

                                    If you have a copy of FileMaker Advanced, you can use the watch feature of the data viewer to check the name returned by Get ( AccountName ).

                                    If you do not, you can use Show Custom Dialog and put Get (AccountName) in the dialog to temporarily pop up a dialog to show you the current account name to compare it the values in your table.

                                    Also make sure you have the correct layout as the current layout when you set up and perform your find. If the layout refers to the wrong table occurrence in layout setup | Show Records from, this can keep the find from producing the expected results.

                                    1 2 Previous Next