1 of 1 people found this helpful
I'm not sure I understand how your tables are set up. Why is the student's year/grade field on a separate table?
Also, you do not need a separate calculated field for the first initial, you can use the left() function on your calculation. Assuming your filed is called LastName, Left(LastName,1) will return the first initial.
You need to make your filter calculation a CASE statement which will perform slightly different test depending on whether OR NOT values have been selected in each filter or are empty.
If there are 2 filters, there need to be 4 case results possible. Your calculation can either test the field/filter contents or their pattern counts to determine what are matching results.
The Student info is in a separate db because I have future plans to reuse that data in other areas later, and don't want to deal with duplicates.
Regarding the tables, it just made sense to me to have within the Students db a table for personal details like name, DOB, address etc, and a separate table for all the school-related data such as year group, class/homeroom, etc.
Is there any performance impact from having multiple tables? I figured it wouldn't, and whether I'm referencing a field at Students::Personal::LastName or Students::School::LastName wouldn't make a difference.
Thanks for the info about the "initial" field. That function is exactly how I calculate the values in that field, but it didn't occur to me to just do that calc directly in the filter- saves one field. I'm all for simplicity.
Thanks for your reply- I'm sorry for being obtuse, but could you provide an example of how I might use the CASE function in portal filtering? I'm unsure what I would want in the "result" section of it.
What I don't get is why the folowing code doesn't work-
Each portal record will be visible when:
(Incidents::gFilterLetter = Personal::LastNameInitial) and (Incidents::gFilterYear = School::YearGroup)
Your current test requires that neither filter be empty and that their content match exactly what is in the record fields.
- (Incidents::gFilterLetter = Personal::LastNameInitial) and (Incidents::gFilterYear = School::YearGroup)
It's the part about neither filter being empty that needs to be handled. It's not really a CASE statement that's required, but a calculation which has several possible TRUE options since filtering is a boolean test:
( isEmpty ( Incidents::gFilterLetter ) and isEmpty ( Incidents::gFilterYear ) ) or
( isEmpty ( Incidents::gFilterLetter ) and Incidents::gFilterYear = School::YearGroup ) or
( Incidents::gFilterLetter = Personal::LastNameInitial ) and isEmpty ( Incidents::gFilterYear ) ) or
( Incidents::gFilterLetter = Personal::LastNameInitial and Incidents::gFilterYear = School::YearGroup )
This should allow filtering on either field individually if the other is blank, or on both if their both filled in, or on neither if both filters are empty.
Along with some rectification of some field relationships, your code has made this work perfectly. More importantly, I understand what it's doing as well
Thanks so much!