Portals and Calculations

[Agentshevy]

I just created any database, kind of a simple database for keeping records of student test scores, grades and finally the result.

I have a layout with portal containing the class and respective scores of a particular student.

Please, how can I go about doing other calculations with these scores from the portal.. Like how can I get the total score of the student? Average score? What other use can the portal be to me in respect to dealing numbers and figures?

[eos]

Take a look at the Aggregate functions – to create calculation fields in the parent table that use related records – and summary fields – that let you summarize records in the child table, AND that you use as related fields to display summarized values for related records on a Parent layout.

 

Check it out: in the Scores table, create a summary field as AverageOf ( score ), then go to the Class layout and put the related field Scores::sAverageScore on the layout. This will give you an average of the score value in all portal rows (.i.e. all related records) [caveat: unless your portal is filtered …]

 

Or create a field cAverageScore in the Class table, defined as Average ( Scores::score). That works, too, but isn't as flexible as a summary field – if you had several tables that look into scores (Students?), you would have to create such a calc field in each one of them, while you could simply use the same summary field from Scores via different relationships. 

 

(Tables names based on a semi-educated guess at your table structure; adapt to your reality.)

 

Note that all this works via relationships, not portals; a portal is just a presentation tool that utilizes a relationship.

 

And maybe start studying the functions that FileMaker offers (and not only the aggregate functions) – this should give you a broad idea of what's possible.

[Agentshevy]

thanks a million eos...

[Agentshevy]

I have done one of the options you stated and here is my result:

portal with two scores 50 and 100 returns a summary of 300

portal with two scores 23 and 70 returns a summary of 186

NB: my portal is Unsorted, I do not know if that is affecting my result..

If it is, please, how do I sort the portal, because it brings me up to perform a calculation which I don't really know what to imput there, therefore I leave it Unsorted..

[eos]

I have done one of the options you stated and here is my result:

portal with two scores 50 and 100 returns a summary of 300

portal with two scores 23 and 70 returns a summary of 186

 

Then you made a mistake somewhere on the way; and sorting is not necessary for an average aggregate. Please re-read the explanation.

 

 

how do I sort the portal, because it brings me up to perform a calculation which I don't really know what to imput there

 

II think you are confusing the portal sorting dialog and the one for portal filtering.

[Agentshevy]

on the portal setup, I didnt use either sorting or filtering... Numer of Rows is 19..

You mentioned something like ''Caveat Unless your portal is filtered''

Mine isnt... Can it in any way affect my result? Because a summary of 50 and 100 suppose to be 150 and not 300..

[eos]

Did you check that you your field or fields are defined as described in my first post? Please post screenshots of your field definitions or, preferably, a copy of your file.

 

Because a summary of 50 and 100 suppose to be 150 and not 300..

 

That depends on the type of summary; didn't you want to compute an average?

[Agentshevy]

Average is part of them... Also Minimum and maximum score.. But as it stands, I wanted to start with Summary of the scores which is currently displaying a false result.. It all means Computing average and the others will be same..

I want to upload screenshot, but am finding it difficult... Currently on mobile and theres no option for that..

When I switch to full Version, it says I do not have permission

the best part is to upload the file... I need to find a way to do that... Maybe uploading to a file sharing site

best bet is to upload the file, i will need to use a file sharing site in that regards... Thanks for your Replies...

[doughemi]

Zip the file and you will be allowed to upload it.

[Agentshevy]

here are the screenshots... I uploaded on sharemobile

http://sharemobile.ro/file/779415

http://sharemobile.ro/file/779416

[Lee Smith]

To attach a file, follow the steps here. How to upload a file

[Agentshevy]

Thanks Lee for the Upload help link... Here is the file. I tried removing the account for access but filemaker says It must have one account. so I created one instead  User: Demo  Password: demo

 

Thanks again for your help

studentSIS.zip

[eos]

The summary field you show does not sum up the “scores” field from the portal, but the field “S_scores” – which is the summary field from the child table that itself calculates a total of score.

 

So in effect you don't sum two records with 100 and 50, respectively, you sum two records with (100+50) each.

 

Either display that related summary field on the layout, OR create a calc field with Sum() of score – do NOT Sum ( relatedTable::sumOfScore ). 

 

Please have a look at other sample files to see how tabled and relationships should be set up. There should be one table for the enrolments of all terms, not one table per term. You can then use fields / foreign keys to categorize the records by term, and create a single relationships from Students and Classes each that includes the term.

 

If you do that, you could use the same sSummary from the Enrolments child table with all relationships, and wouldn't have to create one Sum() calc field for every different relationship.

[bruceR]

The attached carries out some of the relationship simplification described by EOS. 

 

It is not complete; and it also disrupts some things that need to be put back together.

PrimarySchoolSISMOD.fmp12.zip

[Agentshevy]

thanks a million.. eos and Bruce..

[Agentshevy]

Thanks to Bruce and eos, my file is now sorted by term and Gradelevel

please, I have noticed that the Total scores isn't affected with the selection of Either Term or Gradelevel.. It remains as it is.. Still gives the summary of the entire scores. Same goes to Average, minimum and maximum score..

When I select the Term and grade, only the records in the portal change.

I want it to calculate the summary or average per term and Grade when I select the Term and Grade checkbox

[Agentshevy]

<p>My portal row has a Two Calculation fields ''Grade'' and ''GradePoint'' among others. But basically, the values of these two fields are gotten from the Scores field.<br />

<br />

Both have same calculation syntax except that Grade field displays Text as result while that of GradePoint displays Number as result..<br />

<br />

My problem is that: The Grade field displays correctly all its values, but that of Gradepoint field displays only the first value (First value on the Portal) correct and assigns automatically every other Gradepoint value the same value..<br />

<br />

In summary, irrespective of the values on the score field, the values on the Gradepoint Field is the same all through the portal rows.. If I change the value of the Very first Score field, the values of all the GradePoint fields on the Portal rows changes and are the same throughout..<br />

<br />

(It seems that it only uses the value of the first score value on the Portal rows for its calculation)<br />

<br />

Everyother Gradepoint on the portal automatically changes to the value of the very first one irrespective of the Scores..</p>

<p>&nbsp;</p>

<p>here is my screenshot</p>

[Agentshevy]

[Agentshevy]

The Grade field uses the calculation syntax below

 

Let (
  s = ClassEnrollments::Scores ;
  Case (
    s >= 70 ; "A" ;
    s >= 60 ; "B" ;
    s >= 50 ; "C" ;
    s >= 40 ; "D" ;
    s >= 30 ; "E" ;
    "F"
  )
)

 

 

 

 

While the GradePoint field uses the calculation syntax below

 

Let (
  s = ClassEnrollments::Scores ;
  Case (
    s >= 70 ; "5" ;
    s >= 60 ; "4" ;
    s >= 50 ; "3" ;
    s >= 40 ; "2" ;
    s >= 30 ; "1" ;
    "0"
  )
)