Accessing Values from Adjacent Files(Databases)

[Kremlarkin]

I have and will continue to have multiple issues with learning how to use filemaker well. I have decided to focus on individual components of my student information tracking system for my small school. I currently have the summaries component working. To be more specific, daily comments must be made on each student. At the end of each treatment week, those comments are presented on a form which displays thus: one page is dedicated to a given student with the five summaries corresponding to the five week days listed in chronological order. We also assign 'behavior points' to the students each day. I have a 'dailypoints.fp5' database that keeps track of their points. The points are broken up into 10 different elements and there is one calculated field that sums all the points for a given day.

I would like to display the total daily points information (field) next to the corresponding day of week on the summaries form. I have played around with relationships but have not met with success as of yet. The results i typically get are one of three things: a value is displayed, but it is the same value for every day of a given student (e.g. 37 points is displayed for student jane doe for every day of the past week, even though in fact the point values were [37,40,30,31,29]). This happens when i define the relationship by studentID. Both "dailypoints.fp5" and "summaries.fp5" are 'below' the Student.fp5 file. However, neither is below the other (they are on the same level). I have also tried to relate the two based on a date field value. This date is always the date on which the points and summaries were supposed to be given.

So, to recap, i wish for information in an adjacent (not above or behind) database to display in the summaries database.

How might this be done?

[ESpringer]

Hi, Kremlarkin,

Initial question: why not have daily points in the same database as summaries? After all, a record in the summaries database is basically *per* student *per* day, right? So, these types of data are essentially similar... (You could even put attendence here too!) The fact that information is in the same database wouldn't mean that you'd have to look at it all at once; most of the time, the different kinds of data about a student's day would be on different layouts... except when you don't want it to be. ; )

If you don't want to combine the databases, it sounds like you need to get a calculation to concatenate the student ID and the date. Make such a concatenation cStudent_Date = StudentID&"_"&DateToText(Date) for each of the two files, then establish a relationship so that the summary for a given student on a given date can tap the other file to get the points for that student on that date....

(I wouldn't mind some pointers from others on whether to prefer DateToText(Date) or just date, with the whole calculation result being treated as text... anyone?)

It's actually pretty similar to the attendance question you brought up earlier, but less complex...

-ESpringer

[Kremlarkin]

After reading several threads on this board and giving some time for reflection, i have been bouncing around the idea of combining the summaries and points databases. It would certainly make that problem go away. It might also make for a cleaner and more efficient database? What do you think?

[John Caballero]

I'm all for ESpringer's suggestion of combining the Summary and Points databases. It sounds like they both have one record per day, so that makes them very similar. That should make reporting much easier.

[Kremlarkin]

I have combined the databases and the current summaries_points database works perfectly for my needs. Given the small number of different types of data i will be recording, combining the databases will not create a behemoth.

Thanks guys!

[more questions to come! -) ]