Filtering what gets shown in the portal

[Lupine]

So I'm coming back to FM after about a 10-year hiatus...so using portals is new for me and I'm still trying to get my brain around it.

I am developing a CRM for my school, and I'm having difficulty in producing the report cards and transcripts the way I'd like. Here's the deal.

I have a file that holds registration and grade information (Registration). It relates to other files that hold specifics (StudentInfo, Courses, Classes, Faculty, etc.). Course stores info about courses like the name, resources, credit offered, and credit domain (like Math or English). The Classes file stores info about which semester and class period a class is offered, and relates to the Courses file (this allows for multiple instances of a Course during the same time period without double entry of data). Registration, in turn, relates a student with a class and stores their grade along with the credit they have earned in the course's domain.

My StudentInfo file contains the layouts for the report card and transcript. They use a different relationship for each semester...the left side of the key uses a calculation in StudentInfo that matches a concatenation on the right side in Registration to pull only the classes for a given semester into a portal.

Here's my problem: in the Transciript, I have a portal for each semester that the student was matriculated with our school. I only want to show the records from Registration that affect GPA - I want to filter the Pass/Fail classes.

Example:

On the Grade Report, Johnny Smith shows the following in the portal for Fall2003:

Math Algebra1 B+ 3.5

Literature Cr. Writing A- 3.7

Language French1 B- 3.0

Pers.Dev. Research Sk. Pass

Pers.Dev. Typing Sk. Pass

etc.

On the Transcript, I only want to show the classes with decimal values, as those are the only ones that affect the Grade Point Average. How can I filter out the records that are Pass/Fail (in the example, the Pers.Dev. classes)?

Sorry for the length, but I wanted to be thorough to avoid the ping ping post. TIA for any advice.

-= Lupine =- [color:"blue"]

[DanBrill]

In your main file, create a "constant" field, a calc that = 1. (I don't think I ever have a file without one. I usually just call it 'c').

For the records that you want in your portal, create a calculation field 'Flag' so that they are flagged with a 1. For example, suppose the number grade is held in the field 'DecimalGrade'.

If (DecimalGrade="", "", 1)

Now create a relationship between c and Flag and base your portal on this.

[DanBrill]

Whoops. Sorry. Realized it is a little more complex than that since you only want each student to be related to his or her own grades.

So you'll also need a unique identifier, ID, for each student. In the main file, create a calc field C_And_ID: c&"_"&ID. In the related file make that formula for Flag

If (DecimalGrade="", "", "1_"&ID)

Now relate C_And_ID to Flag.

This should do it.

Dan

[CobaltSky]

Hello Lupine,

Rather than creating a handful of constant calcs and flag fields, I suggest that you create a single additional field in the registration file.

The field should be a calc field which tests to see if the registration relates to a pass/fail subject and if it does not, returns the concatenation sequence that you are using for the relationships that the report card and transcript portals are based on. Eg something along the lines of:

Case(not PatternCount("|Pass|Fail|", "|" & Grade & "|"), [Your concatenation formula here])

Once this calc is in place, point the relationships that the report card and transcript portals are based on at the new calc field (ie use it on the right side of the relationships dialog) in place of the existing concatenation field you are using at present. Then the relationships will no longer 'see' the pass/fail registrations.

Indeed, if you don't need to reference the pass/fail subjects from within the StudentInfo file for any other purpose then you could simply modify a calc rather than creating an additional field. In this case all you'd need to do is extend the existing concatenation formulae to include a test (such as the one outlined above) to make the concatenation result conditional on the grade format.

[Jason Goldsmith]

Hi CobaltSky,

Could you provide an example ... I'm having a difficult time "seeing" your explanation.

Thanks!

Jason

[CobaltSky]

OK Jason,

Two severely truncated and simplified examples attached - one with a fixed filtering mechanism (loosely along the lines described above) and the other with the addition of a global field to control the filtering.

The fixed filter is in the file called Transcript_Fixed.fp5

The variable filter is in the file called Transcript_Variable.fp5

Both files are giving filtered portal views of data from the file called Registration.fp5

Hope that's sufficient to 'ignite your ocular faculties'...

FilterDemos.zip

[Jason Goldsmith]

Thanks CobaltSky!

[Lupine]

Cobalt, et al, I apologize for never replying to this. I got VERY busy with teaching classes, and I'm only now just getting back into some redesign.

This solution looks like it will be much better than what I'm doing now. I will be implementing this in the next few weeks...I'll keep you updated on progress.

[CobaltSky]

Hi Lupine,

All the best implementing the changes.

Meanwhile:

Lupine said:

Regardless of the amount of Work performed, as [color:"red"]Knowledge approaches zero, [color:"green"]Money approaches infinity.

It sounds odd - but what it actually means is that the amount of money *required* to achieve a given amount of work approaches infinity as the amount of knowledge available to bring to bear on the task approaches zero.

In other words, if employ a building full of workers but you have no clue what instructions to give them, it is going to cost you a *lot* before you get anything useful out of them.