Looking for efficiency.

[lorivol]

I work at a university and have a database with student information. Some of the students have different levels of scholarship awards that they use at different times in their collegiate career.

The student information (name, demographic info, major, etc.) is in one table.

I created a new table for the scholarship awards and linked it through the student's serial number in my database.

All the award winners start out with an award amount ($5,500) that they can use whenever they are ready to work on their projects.

I created a new layout with the student's information coming from the first table, and then a portal with fields for the project money. The way that it is set up now, I can total all the awards for an individual student, but can't figure out how to pull out certain awards as the student uses his/her money.

In order to disburse the awards, I have to send a scholarship authorization to the financial aid office, which I have recreated in another layout.

I need to be able to enter in the student's receipts for purchases made toward their projects, and then click over to the disbursement layout and have only certain entries show up. there could be more than one entry per disbursement.

For example:

Student A is going to an Engineering Conference and has already booked a flight and paid a deposit for a hotel.

In the portal for funds to disburse, I've entered:

348.80 - Plane Ticket to Conference

88.48 - Hotel Deposit

I need to add these two amounts and make them appear on the scholarship disbursement layout as a total amount of disbursement for each student.

The the students work toward spending their 5500, I assume this will become more complicated because I'll need the disbursement to only pull in the NEW expenses, and not reimburse for previous expenditures. Ex. the same student when he returns from the conference will have receipts to turn in for meals and the rest of his hotel. I will add those in and need to reissue another scholarship authorization ONLY for those new amounts.

Additionally, though, I need to be able to have a running total of all the expenses so that no one goes over their 5500 limit.

I know this is very long, but I tried to be as detailed as possible. Any suggestions/help is much appreciated!

[bcooney]

Welcome to the forums, Lorivol.

Great first post, and a perfect place to start a new system. However, step back from the keyboard and let's make sure that you have the correct tables and relationships (data model).

Here is my take on an ERD from your description above.

It allows for:

1. Students to have multiple Scholarships.

2. Students to have multiple Purchases.

3. Each Purchase to have multiple items.

4. Each PurchaseLI to be assigned a Scholarship ID (or not). This intersection of Scholarship and PurchaseLI is a Disbursement.

5. If you wanted to group Scholarships, I've added a Type table.

Question:

1. Can a Purchase LI be split between two Scholarships?

erd.pdf

[lorivol]

Thank you for responding!

There is only one scholarship awarded to the student ($5,500). However, the student is awarded the scholarship in his/her first-year, but likely will not begin spending the money until his/her third or fourth year. Once the students start spending the money,they probably won't use the entire $5,500 at one time.

Therefore, they will have multiple purchases. My original idea is to use a field I called "Disbursement Number" to sort the list for the scholarship authorizations (a separate layout), which are basically reimbursement requests for money that the student has spent on their research project.

So, the student who is going to a conference has Disbursement 1, which consists of a plane ticket and hotel reservation.

What you're suggesting that instead of "disbursement #" being a field, that it should be a table and then have purchases as portal within that table?

That does make sense to me. Would I still, though be able to do a summary field or a calculation and add up all the totals of all the disbursement numbers for a certain student (to ensure that they don't go over their $5,500 limit)?

I don't think I'd need to have the third level of separate items within each purchase because I could then make each individual item the "purchase."

Student-> Disbursement-> Purchases

Currently I do not have a scholarship ID set up. Also, to answer your question, there would not be a purchase split between 2 scholarships.

Again, thank you for your help with this.

Please let me know if I should proceed by adding a new "Disbursement" table. The most important thing being if I did that, would I be able to summarize:

1. The total amount of money spent per student.

2. the total amount of purchases made per disbursement (to use with my scholarship authorization layout).

Many Thanks!

[comment]

Is there always one disbursement for a specific purchase (purchase being a group of expenses, such as plane ticket and hotel in your example) - or can a disbursement cover more than one purchase, or only a portion of a purchase (i.e. several disbursements for a single purchase)?

[bcooney]

It seems to me that you'd want to address each purchase line item individually, but until Lorivol returns, we won't know.

Also, I wonder what "students have different levels of scholarship awards" means? I took it to mean several scholarships, and apparently that is not the case: "There is only one scholarship awarded to the student ($5,500)."

[comment]

It seems to me that you'd want to address each purchase line item individually

I came to the same conclusion shortly after posting. That would certainly be a lot simpler. Perhaps it could be even simpler, by tracking only the balance between the two totals.

[lorivol]

Hi everyone... sorry for the confusion, by the different levels, I just meant that the students can be continuously using it so it's a different amount for each person at any given time, but they all start with $5500.

(We do have another program where the students are awarded different amounts, but I'm not even going to get into that one right now!).

To try to answer the questions:

The way I have it set up is that a disbursement can contain multiple purchases. I was just grouping them in this way because I need to be able to pull out the groups (disbursements) into the scholarship authorization layout. However, when I tried this using just one table for disbursements/purchases, I couldn't get the summary field to only total the purchases for one disbursement instead of the whole group.

I'm sorry... as I'm typing I know it's very confusing, and I'm likely using terms that make it even harder to see, so I've taken some screen shots.

One is just showing the table relationships (the only ones I'm using for the current issue are the honors student files table and the HSP research funds table.

The other is the layout where I plan to enter in the students purchases as they make them. So each line would be a unique purchase, i.e. plane ticket for conference $300.

I suppose the question is whether I need to make a new table for disbursements and add this purchases portal into a layout with that as the "show records from" table.

Then, though, what type of calc would I use to tally up all the expenditures per student?

[lorivol]

Besides being able to summarize all the scholarships, it is equally important that I am able to take groups of the purchases and total those up as a separate disbursement.

Attached is the scholarship authorization form, which I am required to use. I recreated the form in Filemaker and would want that total of purchases to be the number in the total stipend and the disbursements fields.

The form also pulls out the student information (name,address,ID) from the honors student database table.

This is what is causing me the issue. I can make the purchases add up, but then I couldn't figure out a way to only pull out a certain group of purchases (which would be new purchases) and add those together for this disbursement form.

Please let me know if I failed to answer any questions! And thanks again to everyone!

[comment]

I don't understand this part:

I was just grouping them in this way because I need to be able to pull out the groups (disbursements) into the scholarship authorization layout.

If it's necessary for a disbursement to be related to specific expenses, then yes - you need a table of Disbursements and a table of Expenses. The inconvenience with this method is that the children (expenses) are created before the parent (disbursement). So when you create a new disbursement, you must isolate unpaid expenses and allocate them to the current disbursement. That's why I prefer, whenever possible, to keep just a single log of transactions - where an expense would be a "deposit" and a disbursement a "withdrawal".

[lorivol]

Trying to clarify: In this situation expenses or purchases and disbursements are all debits from the original $5500 beginning amount.

The student has $5500.

He purchases a plane ticket (out of pocket) for $300.

He confirms a hotel for $100 (out of pocket).

He turns in those receipts to me.

I need to keep up with

A. What he has spent his money on (purchases)

B. How much of his money has been spent (summary field)

I also have to fill out the disbursement form so that he is reimbursed for the $400 he just spent.

Since he turned in those two purchases, I would reimburse them both at the same time (by using the scholarship authorization layout) instead of doing two separate authorization (disbursement) forms.

A month later, the same student might turn in another receipt for $30 in lab supplies from an online supplier, $50 in lab supplies from a local store, and $100 in expenses from the bookstore. (All paid for out of pocket by the student).

Now I would have to be able to put all those new purchases into the database and create a second scholarship authorization (or disbursement #2) for $180.

Now, that students total scholarship should reflect that he only has $5020 of his original $5500 to spend.

[comment]

See if something like this can fulfill your needs:

Expenses.zip

[bcooney]

I'm posting before looking at comment's file, but I want to point out something you may be overlooking.

Do you always reimburse the entire expense amount?

Do you always reimburse a submitted expense?

How do you submit expenses (at least what you designated as "Amount to Reimburse?" Do you need a form (with an ID) for each Student or can you submit a report that includes all expenses that have not been submitted for reimbursement, sorted by Student?

When they do pay, what do they reference? The expenseID? or just the StudentID? Or both?

Finally, what happens if their submitted expenses exceed their Scholarship?

Edited September 22, 2010 by Guest

[lorivol]

bcooney,

[color:red]Do you always reimburse the entire expense amount?

Yes

[color:red]Do you always reimburse a submitted expense?

As long as the student is submitting receipts for expenses related to their research project, we'd be authorizing the scholarship for the full amount. If someone gave me a receipt for pizza, for example, I would not enter it into my system (and I'd send them an angry email about misappropriation of funds :

[color:red]How do you submit expenses (at least what you designated as "Amount to Reimburse?" Do you need a form (with an ID) for each Student or can you submit a report that includes all expenses that have not been submitted for reimbursement, sorted by Student?

Yes, I'd rather do the full list of all students who have turned in their receipts for reimbursement at one time. The Scholarship Authorization form layout is what is required to reimburse the students. In the previous example, it just shows one student, but it is set up to be used in the "list view" where it would show multiple students. I would want it sorted by student, so that each student had only one each time I submit the authorization form. See attached.

[color:red]When they do pay, what do they reference? The expenseID? or just the StudentID? Or both?

The scholarships office is on an entirely different system (filemaker is simply what I use in my office to keep up with things), so any expense IDs or numbers that I make up would be purely for my own record keeping/tracking purposes.

The scholarships office uses the student ID, which is a number generated by the university. That field is set to merge into my scholarship authorization layout because the honors student table is related to the HSP Research Funds table.

[color:red]Finally, what happens if their submitted expenses exceed their Scholarship?

To speak to your last question... I hadn't previously thought of that, but If the student gets to the end of their scholarship and have $20 left of their $5500, but submitted a receipt for $50, I would just enter in $20 to filemaker and make a note on the receipt that $30 was not covered by the scholarship. I think that would be the easiest thing to do.

[bcooney]

Do you always reimburse the entire expense amount?

>Yes

Do you always reimburse a submitted expense?

>As long as the student is submitting receipts for expenses related to their research project, we'd be authorizing the scholarship for the full amount. If someone gave me a receipt for pizza, for example, I would not enter it into my system (and I'd send them an angry email about misappropriation of funds

Finally, what happens if their submitted expenses exceed their Scholarship?

>To speak to your last question... I hadn't previously thought of that, but If the student gets to the end of their scholarship and have $20 left of their $5500, but submitted a receipt for $50, I would just enter in $20 to filemaker and make a note on the receipt that $30 was not covered by the scholarship. I think that would be the easiest thing to do.

The above answers tell me that you determine if it's a valid expense and then either submit the expense (purchase line item) in total or if the student is over $5500, then submit what you can. So, the system needs to allow you to submit all or part of a purchase line item.

How do you submit expenses (at least what you designated as "Amount to Reimburse?" Do you need a form (with an ID) for each Student or can you submit a report that includes all expenses that have not been submitted for reimbursement, sorted by Student?

>Yes, I'd rather do the full list of all students who have turned in their receipts for reimbursement at one time. The Scholarship Authorization form layout is what is required to reimburse the students. In the previous example, it just shows one student, but it is set up to be used in the "list view" where it would show multiple students. I would want it sorted by student, so that each student had only one each time I submit the authorization form.

And that is a sub-summary report of expenses that have not been submitted, by student.

When they do pay, what do they reference? The expenseID? or just the StudentID? Or both?

>The scholarships office is on an entirely different system (filemaker is simply what I use in my office to keep up with things), so any expense IDs or numbers that I make up would be purely for my own record keeping/tracking purposes.

The scholarships office uses the student ID, which is a number generated by the university. That field is set to merge into my scholarship authorization layout because the honors student table is related to the HSP Research Funds table.

Lost you there. Not sure what role the HSP Research Funds table plays in this solution.

I see the need for you to go back into your system, Scholarship check in hand, and mark the submitted expenses paid (and it may not be the full amount submitted for reimbursement, right?). So, I'm looking for a cross-reference to make your job easier. If all you get back is a StudentID and not also an ExpenseID or "Request for Scholarship Distribution ID," then, you'll need to look at the amount of the check and apply it to open submitted expenses.