Membership Database with Reporting

[OCE_PTO]

Hello,

My use case is this: I am creating a membership database for a school PTO (parent/teacher organization). I have tables named Parents, Teachers, and Students. The Parents and Teachers tables each have columns for Dues and Donations. I would like to be able to report income from membership in terms of Dues and Donations, but also how much of each is coming from Teachers and how much is coming from Parents.

Is there a way for me to do this? I can create a table with columns that roll up values, but if I make my line items “Dues” and “Donations” and have rollup columns for Teachers and Parents, I can only roll up one type of value per column, meaning it only works for one row (either Dues or Donations - I cannot sum Dues in one row and Donations in the next).

If I create a table with line items for “Teachers” and “Parents” and try to roll up Dues and Donations in columns, it still doesn’t work because you can only roll up using one table per column and I’d need to pull from Teachers in one row and Parents in the next.

Sorry if that is confusing, but can anyone help?

[JonathanBowen]

Hi @OCE_PTO - I think the answer to getting the data you want is going to come from the base design. If I was building this, I would think in terms of different “things” I want to track. I would have tables for:

• People
• Dues
• Donations

Students, Parents and Teachers are all essentially the same thing - people or members - just each with a different type, so I would have these in the same table:

Arguably, Dues and Donations are the same things - financial transactions - and could be dealt with in the same table, but presumably, dues are regular, subscription-type payments and donations are more one-off, so there are differences and it might serve you to track these separately.

Have a look at this simplified version showing people and dues:

Airtable

PTO - Airtable

Explore the "PTO" base on Airtable.

Dues can be logged and each payment linked to a person:

Back in the People table, with a rolloup, you can see the total dues and with a bit of formula work, you can see the dues by type:

There are lots of possibilities to report by person type - a grouped report on the Dues table or just the columns in the People table. Your dues table has a date field, so you could easily form a month/year field from this and report on dues by month/year.

You could set-up donations as its own table - essentially a copy of Dues, perhaps with some different information and link this to People in the same way. In the People table you could then see Total Dues, Total Donations and, summing the two, Total Contribution.

Hope this helps

JB

[OCE_PTO]

Thanks JB - I actually figured this kind of solution would be the first response. Unfortunately, because of the many-to-many relationships involved, I don’t think this will work for me.

You see, to track membership by class, I have to associate teachers with students. So the Teachers table that links to Students, that contains all students in his or her class. However, students also have to be associated with their parents. So the Parents table has a column that links to Students as well, and associates each family member with one or more students. Each student also may have one or more family members.

Here’s the thing - some teachers are also parents. So if I combined them all into one table, not only would I lose the ability to link students to parents and students to teachers, even if I kept students separately, having teachers and parents in the same table would mean that a teacher’s child(ren) would also get included her class incorrectly, as there would be no way to differentiate a teacher relationship vs. a parent relationship to a child.

At least, that’s how I understand it. Feel free to correct me if I’m wrong!

[nea_lpatil]

@OCE_PTO

My initial version for PTO-data.
It has many tables.
Link to the base is as following.

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

Rollup fields need to be created.

Neal

[JonathanBowen]

Hi @OCE_PTO - I’ve made a few changes to the base - see if this works for you.

I’ve added some new views for students, parents and teachers:

I’ve made “type” a multi-select field so that a person can be a parent and teacher at the same time. I’ve also added two link fields - self-referencing links to the people table - that allow you to link one person with another. This pattern is often used where you have a thing of one type (person) but there is some sort of hierarchy, e.g. student/teacher, team manager/team member.

So, to see Teacher Jane’s pupils, I can just filter where teacher = Jane and this won’t include the children she is a parent of (unless she is the teacher and the parent of course)

The parent and teacher link fields use the parent and teacher views so when selecting a teacher, for example, you only see teachers.

Not sure if this will work for you as how you want to report on the data might influence the table structure, but this is the “textbook” way of linking objects of the same type.

JB