Design Questions

[msadesign]

(there is every possibility that I have posted more than once and if so it is operator error…)

I am setting up a solution for my wife's yoga classes. The solution will run on her computer and she will access it via IWP on her iPad in the same building. A sweet setup, I hope, and no intent to make it complicated.

Among other things, I want to use it for attendance. Each class, in my thinking, is a record. She would have a check-box presentation of all her current students displayed in each class record. She would check off each student as present, or not, and, ignoring font and other presentation issues, would look something like the attached 'attendance.jpg':

The db is composted of three tables and they look like the attached 'tables.jpg':

I don't know what to do next. Are the tables constructed in a way that makes sense? It's simple to show all of the current students on the attendance form via a value list, but how do I indicate if a student has attended the current session? and then, how do I use that attendance data so that I can calculate (on a different layout) the student's remaining classes on her pre-paid card?

Thanks, and Merry Christmas everyone!

Michael

[msadesign]

Anyone?

This is really a stumbling block for me. I know the classes form will be where I will take my attendance but I cannot figure out how to show the student body in a portal and then indicate if the student was present or not for that particular class.

Help!?

[msadesign]

Help! What happened to the rest of this thread? Which has some really great advice?

Finally have time to get back to this and pow! off to another universe…

[msadesign]

I was hoping to receive at least one offer for consulting help? It's not a large project, really. Anyone?

[comment]

It's kind of difficult to reply to such a broad question. I'd suggest you back up a bit and construct an ERD based on your business needs.

You should probably have a table for Classes, Students, Enrollments and Sessions? (instances of class). If you really need to track attendance (e.g. for billing purposes), you should have an Attendance table where each individual attendance (student at session) is a record.

[msadesign]

I am looking for someone to work with me on a paid basis. I suppose that wasn't clear.

[Vaughan]

Since you have FM Pro and an iPad I'd get FM Go to access the database rather than using IWP. I believe it'll be better and cheaper to develop.

Post your details (like where you are). A simple course attendance database is relatively easy to do. In fact, see if one of the starter solutions that come with FMP will suffice (download the trial version of FMP and you get access to download the starter solutions).

[msadesign]

Hi Vaughan,

And thanks for responding. I'm in Naples, Florida.

I did look at the starter solutions and felt, wrongly or correctly, that more time would be involved in modifying than starting from scratch.

I earn my living as a consultant, which I think gives me some insight into working with other consultants, like a clear problem statement. I am looking for help getting over some conceptual humps in my planned solution. I'm having trouble understanding how to use the data for different purposes in different layouts and via portals. It's not that I'm a raw beginner–I am not. But I am up against a blank wall.

So, I was hoping to buy some hourly time. In the end, I plan to maintain the DB myself, although having someone we can call upon, and pay, would be a huge benefit for us, as we are using the same solutions for more than 10 years and could use an upgrade. For now, though, I'd like to have the solution roughed out by a professional, based on the information I'd provide.

Does this make sense? Obviously, I've had time to think out what we want the solution to look like and perhaps that would be a place to start, but would need leadership from the consultant.

It's not going to be a huge project. Perhaps that's why I am having trouble attracting the help that I need. Time is passing quickly, and Suzie is using Numbers to keep her student records and it is a horrible mess.

What do you think, mate?

[msadesign]

Perhaps this is an ERD?

General:

Postures is a boutique Iyengar Yoga School in Naples, Florida. Class sizes vary from 20 or so in winter to fewer than 10 in summer. Postures offers several different teachers. Postures also offers private lessons. We envision a very simple system. The solution would run on FM Server 10 and would be accessed via iwp on an iPad.

Semesters:

Semesters are 10 weeks and recur one after another with no break.

Fees:

Fees are based on the number of classes attended by a student. New pay a walk-in rate. This rate is applied to a series if the student desires to continue. Series rates depend on the number of classes per week for any semester and decline on a per-class basis as the number of classes is increased.

Attendance:

Taking attendance happens at a very busy time for the teacher, as students are walking in the door. Attendance checking involves identifying the student as well as double-checking the financial standing of each student before the beginning of each class. A solution needs to be touch-based and simple (iPad). Students are able to take any regular class during the semester, even if it exceeds the series price paid. This shortens the series. Students may not receive credit or refunds for unused classes.

Records:

We would need reports like classes remaining by student, as well as overall reports like number of students per week, average fee per student.

Visiting Teachers:

Postures also sponsors visiting teachers. Students pay a lump sum fee for this series of intensive classes. These workshops normally occur over a weekend, and normally attract non-recurring as well as recurring students. The visiting teacher is paid 70% of the gross less any expenses (room rent, airfare, etc.).

Teacher Training:

Postures is a certified teaching institution. Teacher training sessions are identical to the Visiting Teachers program, with the exception that Suzie Muchnick is the sole provider of teacher training.

Existing Data:

Our existing office Address solution would be the basis of student information. Currently this solution creates keys by combining the first parts of the first and last names and likely needs to be upgraded to a serial number system.

Class Times:

Class times do not change during a semester. Normally, the hour isn’t critical to record; Monday Evening, Wednesday Morning would be examples of acceptable data.

[comment]

Perhaps this is an ERD?

No - that would be more or less a description of your business needs. An ERD is a diagram of your tables ("entities") and their relationships.

I am not sure how you expect to accomplish all of that using three tables... Anyway, let me ask a clarifying question:

Suppose I am a teacher taking attendance at a class. Is there a list of students that are expected to take this class?

[msadesign]

Tables would be, I think:

Students

Classes Offered

Classes Taken (but is this many to many? Students can take more than one class, and of course many students take many classes)

Invoices (money received for classes)

Direct answer to the question: yes, but not complete, as some students fail to pre-register or indicate that they are continuing from one semester to the next. The Students table could contain all possible students, then the Classes Taken table would relate to Classes Offered.

This part is confusing, since Classes Taken needs data from Students and from from Classes Offered.

[comment]

Whew. Let's try getting the terminology straight. I am not sure whether "class" is a series of "lessons" (for lack of a better word), or is it a one-time thing. If the former, then it seems you would need something like:

Students -< Registrations >- Classes

to begin with. Then we expand this to include:

Classes -< Lessons -< Attendance >- Students

This is assuming you accept walk-ins, otherwise it would be:

Classes -< Lessons -< Attendance >- Registrations

i.e. only students registered for the class can attend the lesson.

[msadesign]

Sensible questions. It works like this: the studio offers classes several days per week (www.postures.com). Several teachers participate. Students may take any class they wish.

We sell classes either by the each (walk-in), or a student can take more than one class per week: once per week, twice per week, etc. The cost per class depends on the # of classes taken per week.

By and large we know the student body, but there are always those not attending or new ones that are unknown to us.

Registration is not required and not desirable. We'd want all historical students to appear in the student database, and for the teacher to be able to add students as needed. This is rarely more than one or two at a time.

[comment]

I am afraid you haven't cleared up my confusion: what exactly does "student takes a class" mean? "Takes" as in "attends" or "enrolls"? "Class" as a series of 10 weekly events or a single event?

[msadesign]

A 'class' is 90 minutes and could be called a lesson, too.

[msadesign]

I see that our world has specialized jargon that I didn't realize.

Taking a class means the person attends one of the offered 90 minute classes. Classes are offered with an open door. Anyone can attend. Payment will depend on how many classes per week the student attends (takes).

Enrolls probably doesn't have any meaning in this context.

A 'semester' is a block of time 10 weeks long. (http://www.postures.com/postures/Schedule.html).

**********************

I've been looking at some training tapes (VTC, Lynda). Our situation appears similar to an invoicing solution, where the students=customers, where classes offered=products. At least that's how I am starting to see it.

Thanks for staying with me on this.

[comment]

I am not sure how these two are reconciled:

Classes are offered with an open door. Anyone can attend.

Attendance checking involves identifying the student as well as double-checking the financial standing of each student before the beginning of each class.

But why don't we leave this for now.

You are correct that attendance taking is very much like invoicing - except I would say that the class is the invoice, and students are the products (you add students to the current class, same as you add products to an invoice). In any case, the end product of the process are the "line items", i.e the attendance records. I'd suggest you look at the demo posted here for an UI idea:

http://fmforums.com/forum/showpost.php?post/355429/

The next hurdle you'll need to overcome is grouping the attendance records into invoices. It is always a bit awkward when parent records need to be generated after the children have already been created.

[msadesign]

I am not sure how these two are reconciled:

>I am pretty sure this is simply confusion on describing the process.

But why don't we leave this for now.

>OK.

>To summarize where I think we are, the table are (and this is the problem with my ERD):

>Class Sessions, _pk classID. Examples would be Wednesday evening, friday morning, etc.

>Students, _pk student ID.

>Teachers, _pk teacherID

>students2classes, which would be students->students2classes->classes

>teachers2classes, which would be teachers>-teachers2classes-<classes

You are correct that attendance taking is very much like invoicing - except I would say that the class is the invoice, and students are the products (you add students to the current class, same as you add products to an invoice). In any case, the end product of the process are the "line items", i.e the attendance records.

>Interesting. Except that the students get the Invoices, yes? Looking at it that way, the Invoice example, of which I do have a good one to modify, is appealing.

I'd suggest you look at the demo posted here for an UI idea:

http://fmforums.com/...hp?post/355429/

>ok.

The next hurdle you'll need to overcome is grouping the attendance records into invoices. It is always a bit awkward when parent records need to be generated after the children have already been created.

>now I see why there were so many questions about registration. An earlier suggestion used a script to populate a single class session from all of the student records, then the teacher would enter either present or not. This isn't as cumbersome as it might sound, as there are only about 100 or so active students at any single time. Or, teachers could easily use a value list to take attendance?

>I think my next hurdle, though, is really getting a handle on how to make those join tables like teachers2classes.

[comment]

Except that the students get the Invoices, yes?

Yes - but Students are not Invoices, because (I assume) a student will be invoiced more than once. So while taking attendance, it is the Class record that plays the role of an invoice. At some later point, you'll need to take the Attendance records and group them into Invoices.

An earlier suggestion used a script to populate a single class session from all of the student records, then the teacher would enter either present or not.

That is a possibility, but it creates a lot of redundant records. It's much more efficient to record only attendance (or, in cases where students are registered, absences).

Or, teachers could easily use a value list to take attendance?

That's what my demo mentioned above is about.

[msadesign]

I modified the solution and while it starts to make sense to me, and is providing some useful layouts, there's a difference in how I am seeing it.

I see the classes are the product, and the students are the buyers, and that's how I set it up. Some of the field names I changed but inconsistently. I hope this isn't structurally wrong. Actually looking at it this way, the invoice is actually attendance, isn't it? In fact, students aren't ever billed; suzie just tells them how much they owe when they come in. She also marks off who is in the class and needs to tell students how many classes are remaining.

The attached isn't pretty but I am hoping to get comment on it. I do need a layout that shows student information- including a list of all the classes the student has attended.

Postures.fp7.zip

[comment]

The question 'who is the invoice and who is the product' is largely semantic because the technical arrangement is symmetrical:

Invoices -< LineItems >- Products

To Filemaker, these names are meaningless. It sees only:

ParentA -< JoinTable >- ParentB

However, when your workflow is such that you are parked at a record of ParentA and selecting multiple records from ParentB to be joined to the current record, then I would say that ParentA is the "invoice".

In some cases, the UI allows the selection to be made from either side - so that each parent can play the "invoice" in turn.

I see the classes are the product, and the students are the buyers, and that's how I set it up.

No, you set it up with your Invoices table being classes, and your Classes table being actually students. Otherwise why do you have 'Student Name' in the Classes table?

the invoice is actually attendance, isn't it?

No, attendance is a line item.

students aren't ever billed; suzie just tells them how much they owe when they come in.

I don't quite get this point. When do they actually pay?

[msadesign]

Working through your comments (thanks) but to quickly answer your question, they 'pay at the door'. Students walk into the studio. Normally they know how many classes are left in the series they purchased. Often they forget. Suzie tells them before class if they owe money, and that is when they write the check.

On the names issue I know! it's the human operator needs memnonic names…

[msadesign]

Working through your comments (thanks) but to quickly answer your question, they 'pay at the door'. Students walk into the studio. Normally they know how many classes are left in the series they purchased. Often they forget. Suzie tells them before class if they owe money, and that is when they write the check.

On the names issue I know! it's the human operator needs memnonic names…

I am going to need another level of help on this project. This would be best discussed off list.

Would you mind please either sending me a PM (you are set up not to receive PM), or email me ms at msadesign dot com, so we can make contact?

Thanks…

[comment]

you are set up not to receive PM

I am not aware of that. Stephen, are you monitoring this?

In any case, I have sent you an e-mail.