Scripting deliemma? Suggestions? FMP 5.5

[uwrazor]

Hi Everyone,

I have a database of students ranging from years 1-15 approximately. Each year may have several classes. eg. 1/01, 1/02, 1/03....

All the students should be placed into 1 of 4 houses (Blue, Green, Red, Yellow) for PE competitions.

As much as possible, the assignment of the houses should be as equal as possible.

The script below is suppose to decide for new students what house assignment they should have - based on the numbers already present in the school.

The algorithem that is needed is more or less as follows in priority listed:

- House assigned should be in the least number of students of 4 houses

(where least students in a house must be only 1 house of the 4 houses)

- based on gender in the class

Otherwise:

- based on class

Otherwise:

- based on gender in the year

Otherwise:

- based on year

Otherwise:

- based on gender in school

Otherwise:

- based on school

Otherwise:

- Random house

Here's what I've accomplished so far.

Using Count(), some calc'd compound key fields & some relations, I have 4 fields that will calculate 4 house summaries for all the above counts needed. I simply need to insert what should be counted into one side of the key fields. ie. insert either one of:

- gender & class

- class

- gender & year

- Year

- gender & school

- school

The difficulty that i'm having is finding an efficient & recursive way to make comparisons between the 4 house counts. The main problem is that if 2 or more houses have the same minimum count, then the algorithim should go to the next (otherwise) calculation.

From the algorithim above, it would seem logical that each (Otherwise) statement is a loop. In the loop, the script needs to compare the 4 house totals....I can't figure out a way other than to use: if green = red, or green = blue, or green = yellow, or....bla bla bla...

Of course, bla bla bla is what is making me look for an alternative way to script this as I'm sure I can script it this way - it just wont be very efficient or pretty!

Does anyone have any suggestions? either on the comparison method i need or my script as a whole?

[Ender]

Are the Classes grouped by House too? What files have you broken these entities down into?

[uwrazor]

Hi Ender,

One class usually has a cap/ceiling limit of 25 students. The more students in a particular year, the more classes there will be, eg.

- if 50 students in year06, that gives 2 classes with 25 students.

- If 60 students in year07, 3 classes with 20 students in each year07 class.

Within all classes, the students are supposed to be distributed evenly into the 4 houses. eg.

- 25 year06 students in class 06/1. 16 Males, 9 females.

- Ideally, in this class; 4 males & 2 females in each house, plus 1 extra female assigned based on algorithim

The set of databases used is based on a relational model, so although there are several databases, students.fp5, grades.fp5, subjects.fp5, attendance.fp5, etc...all the 'entities' - i'm assuming you mean fields - are within the student.fp5 file. A record in this file represents a student, thus one of the fields is their house = Red or Green or Blue or Yellow.

I'm not too clear on your question, but I hope the above answers it....Thanks

[comment]

I have implemented, more or less, your algorithm (see attached) - but there is a small problem. The way it works, we start at the first level and assign succesive houses to a student in a loop. The script stops if the currently examined house has less than 25% of the currently examined population.

For example, we start by assigning Blue house to the new student. Then we check how many students in the same class and of the same gender are in Blue house, and compare that to how many students are in the same class and of the same gender overall. If the fraction is 25% or more, we assign the next house. If all 4 houses have passed the test, this means they are equal, so we move to the next level.

The problem is, the first houses examined get priority. The first student in class is always assigned to Blue house. If the next student is of the opposite gender, he/she will also be assigned to the same house, and so on. If there were 6 students in each class, 3 male and 3 female, the last house in the loop would be completely empty.

A better solution would be to make the initial, tentative, assignment randomly - but then a mechanism is needed to ensure we check all 4 options, once, before moving on up to the next level. It can be done, but I haven't bothered with it.

I am curious if a completely random assignment wouldn't achieve adequate dispersion, without all this hoopla.

assignHouse.fp7.zip

[Ender]

For a larger population, I'd try a completely random distribution, but for class sizes of 25 divided into 2 genders and 4 houses, I think another method may work better (this sample size isn't large enough to even out randomly.) I have used looping methods for this type of thing in the past, including starting from a random point and looping down, then back from the top down again. However, it occurred to me there may be an easier method in this case.

Regarding "Entities", I meant: Do you have related files for Class and House, or in this case Class-House? Having a Class-House file will help because you can keep a count for each gender by Class-House. This can then be used by the assignment script.

Roughly speaking, I'd set up two relationships from the Enrollment file (the Student-Class join) to this Class-House, one for each gender. The relationships would be sorted (ascending) by the Count field so the House with the smallest Count is the first related record. The assignment script would be run from the Enrollment file, and would see which House to use by looking at the House field in the related Class-House record for the appropriate gender.

[uwrazor]

Hi Comment,

I don't suppose you could re-save the attachement as a FMP 5.5 compatible file please? I don't yet have FMP7.

It's a good idea to add in the student first then test....Nice to have other minds at work help.....THanks....

[comment]

But wouldn't you run into the same problem? Suppose you have 10 classes, each one perfectly balanced in terms of gender and house assignment. Now you enroll 10 new male students, one per class. In every case, the portal defaults to record creation order, so all 10 get assigned to the same house.

[Ender]

Hmm, perhaps we need some clarity from uwrazor.

I was assuming the distribution into Houses was kept separate for each class. If the House distribution is across all classes for each Year, say, then the entities involved would be a little different. But I think the same method could work (relate to House-Year instead.)

[comment]

Oops... Sorry, missed that completely. I am afraid there's no way to save a backward compatible file. I would have to re-do it from scratch - and I am not going to do that. I suggest you download a demo of v.7. This will allow you to examine the file, and then you can adapt anything you like from there.

[uwrazor]

Regarding "Entities", I meant: Do you have related files for Class and House, or in this case Class-House? Having a Class-House file will help because you can keep a count for each gender by Class-House. This can then be used by the assignment script.

Hi Ender,

I would agree w/ you on the random method not working coz the student populations are too small.

I have setup a method of counting each gender by Class-House, I think...have a look at the 9 calc'd fields below....

zv.CountHouseBLUE, zv.CountHouseRED, zv.CountHouseGRN, zv.CountHouseYLW:

- THESE 4 FIELDS GIVE THE TOTAL NUMBERS OF STUDENTS IN WHATEVER COMBO I CHOOSE

The combo depends on what values I put in the "zi.TempText0X.g" fields - which have been tested, counted & verified. So for gender by class-house, I will add in:

zi.TempText02.g = gender

zi.TempText03.g = class

If i wanted Gender-House count for a year group, I will add in:

zi.TempText02.g = gender

zi.TempText04.g = year

If i wanted House count for a year group, I will add in:

zi.TempText04.g = year

....where all other zi.TempText0X.g fields will be empty.

As i'm not that advanced as some w/ FMP, is this method overly complicated?

----------------------------------------------------------------

zk.HouseSummary =

House &"_"& "_"& "_"& "_"& SchoolGroup &"

[comment]

Actually, I rather like your idea, and I think it could work quite well with a slight improvement. The two relationships would be - a self-join based on same class and same gender, and a self-join on same house. The portal to the first relationship is sorted first by count of related, then by count of total in the house, and finally by a random number (unstored).

BTW, IIUC, each student has only one class, so there would be no Enrollment table.

[uwrazor]

Okay, firstly, students are added 1 at a time, so there will never be the case where 10 students need to be assigned a house simultaneously.

So the priority in terms of house assignment is as follows with top priority at the top of the list (where number refers to number of students in a house). The algorithim should only check the NEXT priority, if & only if it cannot make a decision on the current check:

- Keep the number of same gender in the same class as equal as possible

- Keep the number in the same class as equal as possible

- Keep the number of same gender in the same year as equal as possible

- Keep the number in the same year as equal as possible

- Keep the number of same gender in the same school as equal as possible

- Keep the number in the same school as equal as possible

PS. there're 2 schools: Primary & Secondary

This last priority should only happen if all the all houses have equal numbers in gender, class, year & school, in which case, the assigned house would only be offset by 1 student. Not a big deal when there's hundreds of students.

- Assign student to random house.

Hope this clarifies things....

[comment]

I am afraid you're missing the point. It doesn't matter if they are assigned at the same time, or one month apart each. If you start with a perfectly balanced set, and there is bias towards the first house, they're all going to end up in the first house.

[comment]

I took Ender's idea and expanded on it a bit. It takes quite a lot of relationships, but I tried to push most of the complexity to the Houses file.

For each new student, you set some globals in the Houses file to match the student's data. Then each House counts its own similar students, and the Houses are ordered in a portal. I ran a few tests, and it seems to even out quite nicely.

This one is in version 4 (I don't have 5 or 6).

assignHouse.zip

[uwrazor]

The way it works, we start at the first level and assign succesive houses to a student in a loop. The script stops if the currently examined house has less than 25% of the currently examined population.

Hi Ender,

Previously, i was using the Min() function but the problem is when there are 2 or more houses that give the lowest results. eg. Consider in a class, by gender where there is the house distribution of; Red 1, Green 1, Blue 1, Yellow 5...& 1 more same gender student needs to be assigned to this class?

Also that students may leave anytime (& often do in our school) so house numbers may be dramatically offset.

Or am i misunderstanding your method?

The demo is taking forever to download....is it possible perhaps to take a screen shot of your script & post it to the forum?

[comment]

Yes.

Archive.zip

[Ender]

Looks good comment.

[uwrazor]

Hi Comment,

Ok, i've figured out your 2 files & yes....great solution!