complex tabular computation

complex tabular computation

i am working on a bunch of very complex tables . diagram 1

TABLE 1 (Original table)

Now this is the old table design. In this table design for department of english/economics wehave two courses shown above for example.Now in each course like english we course subjects like practlical/listening skill,basic grammar this table is for 1st year semester one.i have created all the semester entities , candidates  entities and done all the realtionship models.if you look at the table use that the total sum of credit unit for all subjects in english course is divide by(/) total grade point for all subjects in english course to get the GPA(grade point average gor english couse) only.the same thing goes for economics and other courses which i didnt show.i understand this part. please be patient guys i want to breakdown the full details of the task at hand.Now let me upload the new table format 


This is the new table old table i had the privelege of groupping subjects by their respective course(eg Engish and economics) in individual tables . Now i have to list the subject belongin to all the courses for that particular semster of year one of study.the course taken column contins the name for all subjects for all the courses for the semester. 

Now the main difficulty i am encountering is how can write a complex script/ query that will for example calculate the sum of  all the credit units for all the subjects belonging to course English only not economics  for all the semesters not just emester one. That means my code should filter for the candidatid that student id and get sum of the credit units for all the subjects for a particular course across all the 6 semester and assign it to a totals column  .which can reuse in the final table which am about to show you


Now based on what i just explained this final table , you can see the courses listed above in this image for english TCUP means total sum of the credit unit for all the subjects pertaining to english language  course only across the 6 semesters, the value total is automatically populating the TCUP  column.The same formula oges for genral education course and political science for a particular student. if i can grasp the concept of it for tcup , i can applly that knowledge for CGPA.i am uploading an espace of what i have done so far. so please i just need some technical help in terms of tips and guidance . i hope to learn more on how to deal with difficult tasks in outsystems as i progress.pleae feel to ask me any question with regrds to this. Thanks guys.

i am currently working on the dat model

here is how i want to go about it.
Candidate entity(primary table)
course entity with foreign key to candidate table
course subject entity with foreign key to course entity
semester enityt lin to candidate entity

so do you guys have any suggestions based on the details provided above. i believe having a solid data mpdel will influnece how this task is handled.Thanks

here is my erd diagram. what do you guys think.i am open to suggestions on any modification neede based on my above explanation.Thanks

Hi Tom,
From my understanding, your "semester" entities are similar (same columns), so I suggest to use just one entity to store the class grade (ClassGrade) and create a new static entity for the semesters. 

What do you think?

Thank you vera!!. great idea.i will give it a try. now what worries me is this scenario. That FINAL TABLE  snaphot i posted above i don't know how can  query a course by it by its subjectid . here is the break down . in that snaphot "final table " general education is a course and in that general education course there about 6 subjects per semester.the general education is done in 2 semesters out of the 6 semesters. to compute the value in that TCUP column(which stands for total credit unit). here is the formula

sum of course subjects per semester for general education only =TCUP

for cgpa = sum of course subjects per semester / sumof credit units per semester for general education only.

so the same formula goes for english and political science. the problem is how can i compute for a course by the sum of the individual subject  that make up each course when try to use advanced query ,it computes the total for all the different courses . it not on per course base. please vera . this is really challenging , i have been trying to deal with that computation all day long.any tips will be go  a long way.Thanks

hi vera there is an issue with using static variable if i want to do a master details screen for multiple tables it wouldnt let me have it. look at what i mean  .

They want to have a view similar to this which means unless i create 6 physical tables per semester ,there is noway i can do a master details table using only one enitity. if i drag it , it doesnt' let me.unless i create 6 physical tables per semester. it to handle a 3 year alternative might be to create a kind of sheet view like tabs for each table but that will mean a lot more hassle to get it to work. what do you think?