10 Replies Latest reply on Feb 17, 2010 1:17 PM by dane1

    How to update data and move old data to a new field.

    dane1

      Title

      How to update data and move old data to a new field.

      Post

      hello,

       

      I installed filemaker 10, and justbuilt my first database for my work. It's a student report database used to give weekly reports to rehabilitation treatment centers that use our online highschool program. I have it set up in a sub-summary with the top sub-summary being the students name, and treatment center they are at, the body containing information about the students courses, and the buttom sub- summary containing the total progress for the week made by the student. Here is my problem.

       

      The body shows the student's class data which includes the following feilds.

       

      Course Name, Last Weeks percentage, This Weeks percentage, the Gain the student made in this week, and the student's overall grade in the class.

       

      The whole point of building the database was to eliminate as much as possible, the need to key in data by hand. But I do not know how to allow filemaker to update the existing data with the new week's data. What I want is this: to be able to have filemaker match the class with the new data, put the new percentage in the "this weeks percentage" feild, and move the old percentage to the "last week's percentage" feild.

       

      Justfor information, I am using the MacOSX version of filemaker 10, and I am planning on keeping this database in-house on our companies office server.

       

      I would appreciate any help that you could give.

        • 1. Re: How to update data and move old data to a new field.
          philmodjunk
            

          I was suprised to see no one responded to your earlier post of the same question. I thought I'd responded to that one, but see that I did not.

           

          As I asked in the original thread where you asked about setting up the summary report part of the project,

           

          Are you really sure that you need to move data in this fashion? There's probably a way to simply keep your records labeled by date and you can simply perform a find to locate the most recent data on each student. This has the additional benefit of enabling you to easily look back through a list of records for one student and see the past reports on that student.

          • 2. Re: How to update data and move old data to a new field.
            dane1
               that is true. I didnt' even think about that. I found another way to do it where I just added a button linked to the "Last Week's" field that when pushed it pulls the data straight over from the "current progress" field. But your idea might work better.
            • 3. Re: How to update data and move old data to a new field.
              dane1
                 So i am still having trouble. This is how our reports work. we show treatment centers what progress the student was at last week, and then this week so they can see what progress has been made. The only way we have of gathering data is through a excel file that is downloaded from the online programs, so there is a new spreadsheet each week. I cannot figure out how to update the exsisting data with that on the new spreadsheet without clearing the database and importing from scratch, or having duplicate records for each class. I know there has got to be an easier way to do this. Any ideas?
              • 4. Re: How to update data and move old data to a new field.
                philmodjunk
                  

                Is there a date column in the imported data? If so, your records aren't really duplicates and you may wish to keep them to show past performance vs. current. If your imported data does not include a date, you can date them immediately after importing.

                 

                Import records will leave the newly imported records as the current found set. You can then use replace field contents to update all the newly imported records with today's date or a date you specify.

                 

                To find a student's current progress, either include the date of the last set of imported records in your find for that student of find all of that student's records and sort them by date so that the most recent record is first or last in the list.

                • 5. Re: How to update data and move old data to a new field.
                  dane1
                     oh I see now! thank you so much!
                  • 6. Re: How to update data and move old data to a new field.
                    dane1
                       is there a way to set a feild with a lookup function to find the last matching entry by the date specified?
                    • 7. Re: How to update data and move old data to a new field.
                      philmodjunk
                        

                      You can set up a relationship that joins a table to it self, there are also ways to use GetNthRecord that may serve.

                       

                      What does "last matching entry" look like with your data? Hopefully, you can use more than student name and a date (Two students might have the same name.)

                      • 8. Re: How to update data and move old data to a new field.
                        dane1
                          

                        I have a student records that I can create a student Number if that would be easier. Im sorry Im such a nag, but I really am having struggles figuring this out.

                         

                        if you could help me figure this out, I would really appreciate it. This is what I have so far.

                         

                        Table 1:

                        Student Name

                        School

                         

                        Table 2:

                        Student Name

                        Course Name

                        Last Week's Progress

                        Current Progress

                        Progress Gain

                        Overall Grade

                         

                        which ends up looking like this.

                         

                        Student name                                     School Name

                         

                         

                        Course 1 Name                   Course 1 Last Week Percentage         Course 1 Percentage        Progress Gain          Course 1 Grade


                         

                         

                         

                        I've got it set up to display information for all fields except  the last weeks. I want filemaker to find the matching class (Course 1 Name) for last weeks, and have the current progrress data from that record placed into the "last week's" field for the matching (Course 1 Name) of this week.

                         

                        i know this project is getting annoying, but if you could help me figure this out, I would be so thankful!

                         

                        • 9. Re: How to update data and move old data to a new field.
                          philmodjunk
                            

                          Don't feel nagged at all :smileywink:

                           

                          I'd definitely add a student ID field to both tables so as to avoid problems when you get two students named "John Smith" or some such.

                           

                          When you bring up your data, how can you tell which record is "last week's record". Do you have a date field that tells you that?

                           

                          Let's call your table: StudentRecs. Define a new auto-entered serial number, RecID, that uniquely identifies each record in this table.

                          Make a second table occurrence of Student Recs and link them like this:

                           

                          StudentRecs::StudentID = StudentRecs 2::StudentID AND

                          StudentRecs::CourseName = StudentRecs 2::CourseName AND

                          StudentRecs::RecID ≠ StudentRecs 2::RecID

                           

                          Specify a sort order for this relationship that sorts them by date in descending order.

                           

                          This relationship will match all records of for the same student, same course but will not match to itself.

                           

                          Now you can place the Course percentage field from StudentRecs 2 on your layout to show the previous record's percentage.

                           

                          A calculation field can compute the change in percentage as CoursePercentage - StudentRecs 2::CoursePercentage.

                          • 10. Re: How to update data and move old data to a new field.
                            dane1
                               OHHHH!!! thank you so much! tha makes sense now!