Help with Database setup

September 4, 200619 yr

Newbies

Hi

Not sure what forum this should go in....

Hoping you guys can help - first time using filemaker. I'm using it for my new business which is a dance school. Basically I need to keep track of people registered to the school, what classes they take in what term and what payments they should have made and whether they paid or not.

I don't know if I've done it right but I've set up the following tables

Person Details

Term Details

Class Details

Assignment Details

Person Details holds all the personal information and has a unique person id for each record

Term Details holds all the term details

Class Details holds all the class details - including the cost of each class.

Assignment Details tried to link it all together by holding a person id, a term name and a class name.

I've managed to create screens that can show the person details and then a portal which shows all the terms and classes that person is assigned to.

So I can see the cost for each individual class the person is doing but I'm now trying to do 2 things.

1. Add a total cost at the bottom of the portal adding up all the classes that person has taken.

2. Find a way to group the portal (or another way of showing the data) by term so that I can sub total cost by term on the screen,

Can anyone give me any clues as to whether I'm going about this the right way with my basic database structure and also some help on how to make the additions to the database that I'm trying to do,

Thanks

Martin

September 5, 200619 yr

It would help if you posted your file, so people can look at it.

That will give people a better idea of what you are trying to do.

Dave

September 5, 200619 yr

It's sounds like you're doing pretty well. You seem to have a grasp of relational design; though we could say better if, as faaslave says, we saw an example of the file. A clone (no records would do).

The piece you are missing is the Sum() function, in the Aggregate section. Used in a calculation field, it will add up all the values in a field which matches a relationship. So, if you point it to the cost field in Assignments, from Person, via the same relationship as the portal, it will total the cost. The basic syntax is:

Sum ( relationship::field )

It could do the same for Term. But that's a little more difficult. If you create a Sum() calculation in Term, it will add up the costs for that term, in all Assignments.

But if you want to see something like "costs for this term for this person, viewed from the person table" then you're going to need something else. Probably an unstored calculation field in Person, using Get (CurrentDate), possibly Month(), resulting in the current Term (it's based on the relative date, right?). Then combine that field with the PersonID in a compound relationship to Assingment. Sum() that.

Edited September 5, 200619 yr by Guest