Yet another filtering question...

[Randy]

Hello,

I am creating an attendance solution for a school. I have 3 files, Students, Teachers, and Attendance. In an attempt to not have to scroll to the bottom of the portal window to create a new record (automatically), I have created scripted buttons that create a new record from the student database into the attendance database and sets the status of attendance. This works just great.

I am running into problems displaying the info in a portal to give some sort of feedback to the teachers about which student status that have completed, or where they are in the list. The first records work just fine because it only has the one related record to display. By the way the relationship which allows the creation of records is Students:Student ID to Attendance:Student ID. I can conceptualize that I need a Key to actually filter the data being displayed in the portal. I know that it can't be a simple date to date relationship, because the records being displayed still need to be related to the Student ID so that the proper attendance info is shown for each student.

Now, it would be really cool if I could have 5 portals, one for each day of the week, and show the entire weeks "status" for each student. Even cooler yet, would be to display the previous day or two worth of info. For example, on Monday show Thurs. and Fri. of last week, Tues show the previous Friday and the current weeks Mon info.

Thanks in advance for any light you may shed on my questions. I have read many of the previous posts on filtering and their answers, I can't seem to grasp the whole concept of local and foreign keys. So, maybe a little background info on that would be a good place to start.

Thanks again,

Randy

[Pupiweb]

Create a global date field gDate

Create a series of calc fields, number/text result, equalling gDate, gDate - 1, gDate +1 etc ... depending on which days you want to look at (previous, next etc ...)

Create a series of calc fields joining StudentID & " " & each of these fields

Make a portal for each calc

Enter in gDate the date you like and you'll see that student's attendance for the date chosen in gDate and in the other days

Further customizations at will ;-)

[Randy]

Thanks for the reply.

It all seems reasonable to me, except that what do I base the relationship on for the

calc fields joining StudentID & " " & each of these fields

I've tried matching the Student ID field in the attendance database with this calc, result is no matching records. That is assuming I've done everything correctly.

While I'm writing let me run this one by everyone. In my attempt to show each day of the week records regardless of the day of the week I have concocted this set of calculations, this one is for the Monday portal:

Case(

_Date = Today- 1, _Date - 1,

_Date = Today- 2, _Date - 2,

_Date = Today- 3, _Date - 3,

_Date = Today- 4, _Date - 4,

_Date = Today, _Date)

So that if the global date field, _Date, is today show the matching records from today, or if _Date is Today -1(on Tuesday) show the match from Today - 1 which should be Monday, etc. Does this seems like it should work?

Thanks again!!

Randy

[Kurt Knippel]

Using the Today function is generally a bad idea and is a holdover from old version without the status functions. It also limits you to only seeing this week rather than a specified period.

Start by setting a global field to the current date:

_SelectedDate = status ( currentdate )

Then your match ( _MondayDate ) calculation becomes:

Case(

DayName ( _SelectedDate ) = "Monday", _SelectedDate

DayName ( _SelectedDate ) = "Tuesday", _SelectedDate - 1,

DayName ( _SelectedDate ) = "Wednesday", _SelectedDate - 2,

DayName ( _SelectedDate ) = "Thursday", _SelectedDate - 3,

DayName ( _SelectedDate ) = "Friday", _SelectedDate - 4,

DayName ( _SelectedDate ) = "Saturday", _SelectedDate - 5,

DayName ( _SelectedDate ) = "Sunday", _SelectedDate - 6

)

The above gives you maximum flexability in choosing your date, while still generating the correct date for each portal.