Calculations If( )

[AlisonHollier]

HI everyone - HELP!! Im Stuck or brain dead

I have 2 databases which are linked by a relationship.

One is a student database and the other an accommodation database

In our school we put students with homestay families - when one student leaves we put another one with that family.

I am trying to do a field "current student" in my accommodation database so the current students name will show up only.

I have done a claculation according to the student status - each student has a code number depending on if they are still at school or not 141, 142 or 143 means they could still be in the homestay and 144 means they have left school - my calculation was

If (international students database::student status ≠ 144 ;international students database::english name ;0 )

it would then show the student name in my homestay database - however my problem is that it only shows the first record so if the student has left it shows 0 as in the student databse it still has that homestay info there, but wont search for the next record.

what am I doing wrong ?? I have a feeling it is so simple but my tired brain cant work it out

any help would be appreciated.

thank you

[comment]

I am trying to do a field "current student" in my accommodation database so the current students name will show up only.

So the current students name will show up where?

Do I understand correctly that a homestay (homestead??) has many students related to it, but only one - or perhaps more? - is "current"?

[omegagoh]

Dear AlisonHollier,

I guess you are new to FileMaker and yet to explore on Portal and Relation Predicates. Anyway, do take a look of the attachment.

Regards,

Omega Goh

Speedy Scaleable Solutions (Asia)

HI everyone - HELP!! Im Stuck or brain dead

I have 2 databases which are linked by a relationship.

One is a student database and the other an accommodation database

In our school we put students with homestay families - when one student leaves we put another one with that family.

I am trying to do a field "current student" in my accommodation database so the current students name will show up only.

I have done a claculation according to the student status - each student has a code number depending on if they are still at school or not 141, 142 or 143 means they could still be in the homestay and 144 means they have left school - my calculation was

If (international students database::student status ≠ 144 ;international students database::english name ;0 )

it would then show the student name in my homestay database - however my problem is that it only shows the first record so if the student has left it shows 0 as in the student databse it still has that homestay info there, but wont search for the next record.

what am I doing wrong ?? I have a feeling it is so simple but my tired brain cant work it out

any help would be appreciated.

thank you

Student.zip

[AlisonHollier]

So the current students name will show up where?

Do I understand correctly that a homestay (homestead??) has many students related to it, but only one - or perhaps more? - is "current"?

The students current name will show up in the homestay database and yes the homestay has many students related to it but yes only one is usually current.

Thanks

[bruceR]

Typically you want a join table for something like this. This allows you to keep a history, for one thing. See attached for a start at this.

Student.fp7.zip

[comment]

the homestay has many students related to it but yes only one is usually current.

"Usually" is not "always" - these exceptions need to be planned for. Have a look at the attached for a rather simple solution.

Note that here a student can be assigned to only one home at all times. This means that if you re-assign a student to another home, you will lose their previous assignment. To keep a trail of all assignments, past AND present, you need another table for the assignments.

CurrentStudents11.zip