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.
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.
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?
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.
oh I see now! thank you so much!
is there a way to set a feild with a lookup function to find the last matching entry by the date specified?
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.)
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.
Last Week's Progress
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!
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.
OHHHH!!! thank you so much! tha makes sense now!