Jump to content

Can't get filtered relationship to work.


MikeKD
 Share

This topic is 2913 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi folks,

I have the following tables:

  • Pupils - details of the kids
  • Classes - details of classes etc they're members of
  • Assessments - general details of assessments; what academic year, what title, maximum mark etc.
  • PupilClassAssessJoin - the specific record of each assessment for each kid.

At the moment I have an Average% field in Pupils that gives the average of all their PupilClassAssess records.

 

However, it would be useful to know what their average is for the current academic year (set by gAcademicYear in the Classes table).

 

I've set up what I thought would filter on assessments from this academic year to give me the results in a field in Pupils calculated by

Average ( PupilClassAssess_forYrAvg::Percentage )

 

I don't think the relationship works - can anyone help please? Do I actually need a relationship, or can I just use a different calculation?

 

Many thanks,

Mike

post-66258-0-63668300-1417886757_thumb.p

Link to comment
Share on other sites

At the moment only for display, but I'd think it would get used for further calculations down the line (i.e. to flag if last grade is higher or lower than average, to compare this years average with the overall average, to compare it with other kids in the class and cohort).

 

Does that make a difference?

 

Cheers!

Mike

Link to comment
Share on other sites

Does that make a difference?

 

Yes, it does.  I suggest you try it this way (untested):

 

1. Place the gAcademicYear field in the Pupils table;

 

2. Define a relationship:

Pupils::gAcademicYear = Assessments 2::AcademicYear

 

3. Define a calculation field cYearAssesmentIDs (result is Text) in the Pupils table =

List ( Assessments 2::AssesmentID )

 

4. Define a relationship:

Pupils::PupilD = PupilClassAssessJoin 2::PupilD

AND

Pupils::cYearAssesmentIDs = PupilClassAssessJoin 2::AssesmentID

 

5. Change the Average% field in Pupils to average the records from PupilClassAssessJoin 2.

 

You might have to refresh window + flush cached join results when you select a new gAcademicYear.

Link to comment
Share on other sites

Hey; that's very cunning - I'll give it a try!!

 

The gAcedemicYear gets changed very rarely; potentially only once a year.

 

I haven't had a chance to look, but I think that gAcademicYear is used several times in portal & layout filters. Is it really terrible practice to have the Pupils::gAcademicYear field be a reference of Class::gAcademicField to save a load of work?!!

 

Thanks so much for your help!!

Best wishes,

Mike

Link to comment
Share on other sites

OK, I've had a go - some progress, but not there yet.

 

1. Place the gAcademicYear field in the Pupils table;

DONE (actually, it's a calc field which equals gAcademicYear in the Classes table...)

 

2. Define a relationship:

Pupils::gAcademicYear = Assessments 2::gAcademicYear

DONE

 

3. Define a calculation field cYearAssesmentIDs (result is Text) in the Pupils table =

List ( Assessments 2::AssesmentID )

DONE - never done this before; very nifty! It creates a list of ALL assessments for the academic year, rather than just for specific pupil, but that's OK cos we filter down later?

 

4. Define a relationship:

Pupils::PupilD = PupilClassAssessJoin 2::PupilD

AND

Pupils::cYearAssesmentIDs = PupilClassAssessJoin 2::AssesmentID

DONE - I can also get the whole school average for the year by forgetting to include the 2nd relationship ;-)

 

5. Change the Average% field in Pupils to average the records from PupilClassAssessJoin 2.

DONE, but I get no result in the Average field (except when I forgot to add the PupilID relationship).

 

Have I mucked up somehow?

Cheers!

Mike

Link to comment
Share on other sites

Well, I've had a little success, but I'm not there yet.

  • The list of this years assessment IDs still works.
  • The field now gives an average, but it's the total average of all assessments, not filtered to year as far as I can tell.
  • UNLESS, the pupil doesn't have any assessments from this year, in which case the field is empty.

I suspect the problem is in my relationships - I've tried to mimic EOS' file, but have used TOs, not the original Assessments and PupilClassAssess tables - going back over that now to see if that fixes it.....


OK, can't do that because there are already relationships in place.

Bother.

Link to comment
Share on other sites

I suspect the problem is in my relationships - I've tried to mimic EOS' file, but have used TOs, not the original Assessments and PupilClassAssess tables

 

Every field access happens via a TO; which one was the first (or original) one, is immaterial for almost all purposes. In that sense, there is no “original” table – there is just a table that is represented by any number of TOs.

 

Also, the name a TO has (or that you give it) doesn't hold any significance (it just needs to be unique); the important factors are the relationships between these TOs and your current context when evaluating field references and/or calculations.

 

Why don't you post your file as it stands? A clone with a few sample records in each table should do nicely.

 

PS: “eos”, please …  :laugh: (if I wanted it capitalized, I'd have written it thus) 

Link to comment
Share on other sites

Here it is - please don't laugh at my incompetence!!

Anything remotely complicated was done with a lot of help from folks on this site :-)

 

While you're looking, there's an odd thing in the portal navigation; the first time I click on a portal row, it goes to the relevant record / layout from the top portal row, which ever one has been clicked on.  Going back and doing it again works fine...

 

Obviously, if you have any ideas for improvements, (I'm especially looking at monitoring & spotting trends in the kids grade data at the moment) I'd be uber grateful!

 

Thanks so much!!

PupilGradeV075 Clone.fmp12.zip

Link to comment
Share on other sites

It's a bit hard to find ones way through the thicket that is your DB :D , but you should check your IDs: AssessmentID in Assessment is text (a UUID), but ThisAssessID in the join table (which at one point is matched against AssessID) is a numeric field (and even validated as such). … and you're prefixing it with something; I'm not sure how they would/could match with the original AssessmentIDs.

 

Speaking of which: I suggest that you give your TOs more meaningful names; it's hard to detect (or remember …) e.g. where Pupils2 is within your structure, or what it is supposed to do, without paying a visit to the Relationship Graph.

 

Also, maybe create one layout per table, set to table view and holding the most important fields, so you have a place that gives you a direct view at your data. I've always found this very helpful in cutting to the heart of an issue, without being distracted by UI clutter.

 

Last (but probably not least), you've got a lot of enumerated fields (xy1, xy2, xy3…); maybe read a bit about data normalization.

 

 

While you're looking, there's an odd thing in the portal navigation; the first time I click on a portal row, it goes to the relevant record / layout from the top portal row, which ever one has been clicked on.  Going back and doing it again works fine...

 

I don't know which portal you mean here – but in general this behaviour points at a mismatch between the portal TO and the TO you've specified as the target TO in a GTRR. (A sensible (or any) naming scheme for your TOs will not prevent this, but making a mismatch more obvious and/or easier to spot.)

Link to comment
Share on other sites

Thanks dos - will read and digest again!

I am right in thinking that if I rename a TO, then all links / relationships to that will also get renamed?

Does this also apply to scripts?

 

Cheers,

Mike

Link to comment
Share on other sites

Ah; yep, I found and fixed the guilty numeric validated UUID. (I added the initials at the start of these just to help identify what they all were.)

Unfortunately, that didn't fix the original issue. 

 

I like your diagnostic table layout view idea.

 

I've been aware of the terribly named TOs for a while; just been a) plucking up the courage to mess with them and B) trying to think of better names!

 

The only enumerated fields are in the pupil table, for Y7-13 notes and for instrument 1-4. I suppose it would be better to have lots of the same info in a separate table; I am planning to do that for instruments (to cater for those that play more than 4 and to give more scope for info - have they given up, when, who teaches them etc)

 

I'll do a bit more error hunting!!!!.....

Link to comment
Share on other sites

OK, I've created simple table layouts for all the relevant TOs.

As far as I can tell, the filtering isn't working at all - all TO show all records, but AIUI, they should only show the records that they're related too. Is my understanding of how this should work totally wrong?

 

e.g. Pupil_class_assess table is related to Pupil table by the PupilID field AND a calculated field with a list of AssessIDs, but shows all records. Is this correct behaviour or is something weird going on?

 

Cheers,

Mike

Link to comment
Share on other sites

As far as I can tell, the filtering isn't working at all - all TO show all records

 

A layout always shows all records of its native table. A relationship (each relationship is a “filter”) determines the set of related records from related TOs.

 

“Filtering a table” means e.g. performing a Find (or using other methods to arrive at a subset of the total record count; Omit, GTRR etc.).

 

but AIUI, they should only show the records that they're related too

 

Imagine a layout based on a TO related to several other TOs; which of these related TOs should be used to determine the found set of your layout TO? (And by which relationships would they determine their found sets …? etc.)

Link to comment
Share on other sites

The gAcedemicYear gets changed very rarely; potentially only once a year.
 
Not to interrupt your learning experience, but in such case you might consider an alternative approach:
 
Define a calculation field in the PupilClassAssessJoin table, along the lines of =
Case ( Assessments::AcademicYear = Classes::gAcademicYear ; Grade )
and average this field in the Pupils table, using the existing (core) TOs and relationships.
 
 
Of course, this is only good if Grade is the only datum you need for the current academic year.
Link to comment
Share on other sites

Thanks comment; the case bit works well - only giving a value for records that match the academic year.

For some reason, averaging that doesn't seem to work - I'll have a look at that later; I've got no spare time for a day or two.

 

Many thanks!

Mike

Link to comment
Share on other sites

This topic is 2913 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.