script for creating monthy tuition charges?

[tylertul]

Help us get back to teaching music! We are trying to get rid of Quickbooks and put it all under one roof with Filemaker. Here’s what we have and what we need…

Main table layout (for student info)

Related table (for monthly charges and payments) in a portal on main table layout. Contains fields for tuition, paid, date paid, student ID (relationship to main table based on this)

I need a script that I can run on the 20th of each month that will determine if a student is currently registered by comparing the enroll date with the withdraw date and if today (the day I run the script) is between those two…

Create a new charge for the following month dated the 1st of that month (i.e. if I run the script in Jan, it should date the newly created transactions Feb. 1st) based on a field in the student’s record (main table layout) that contains their current tuition rate.

Also, if a transaction for that date (Feb. 1st for our example) has already been created, do nothing and move on to the next student and so on until all students that are currently enrolled have a new monthly tuition entry.

Any help would be really appreciated. I am pretty new at programming Filemaker even though I have been using it since 1989. Mac Plus – 4 megs of ram!

Thanks in advance for your ideas,

Tyler

[CyborgSam]

Tyler->

Welcome to the Forums!

There will be a lot of work answering your question in detail, since it involves knowledge about several different areas of FileMaker, as well as details about your database.

One thing you'll need for sure is a way to tell if a student's tuition is due. One calculation that checks if their enrollment dates overlap the current date, example:

QuarterStartDate <= TodaysDate <= QuarterEndDate will return 1 if the student is enrolled (true), 0 otherwise (false). Another calc (which can be combined with the first) is if the student paid this month. here's an example

( DateTuitionPaid >= Date ( Month ( TodaysDate ) ; 1 ; Year ( TodaysDate ) ) ) and

( DateTuitionPaid <= Date ( Month ( TodaysDate ) + 1 ; 1 ; Year ( TodaysDate ) ) )

Is this enough help to get you on your way?

[tylertul]

Thanks!

That first part seems right for our application. We are going to send monthly statements to each student even if they have paid because sometimes they don't pay the correct amount and still have a small balance due.

I do know enough about Filemaker programming to understand most things you might say with just a brief example or explanation. Thanks again for your ideas!

How would you create a new entry for each of the students in the child table after finding all of the currently enrolled students? Evan a brief outline of the procedure would be really helpful.

We bill students monthly for tuition. We have enrolled date and withdraw date fields.

Also, do you know how to create a statement with only the last 3 months of account activity showing but a running total on each line that is based on ALL historical charges and payments for that student (not just those 3-4 records that are showing?

I am thinking of using a portal on the statement that does not contain any scroll bars. Show just 3 or 4 lines and then force it to show record 4th from most recent at the top and the most recent months activity in the bottom row. Do you think this is the right approach?

We are in Seattle too by the way!

Thanks,

Tyler

[CyborgSam]

Tyler->

You can create a field in the table that has the calculations above, then create a relationship between the calc and a constant value of 1 (a global calc with just a "1" in the calc works OK). This relationship is used in a portal. A script can limit the found set by doing a find and searching for the calc = 1, and then process the records (print, etc.).

A total based on all applicable charges can be a calculation based on a relationship, and depending on how things are done, a summary. Which is better depends on exactly how the data is organized. Again, think of creating a calculation field that contains just the info you want and then summing it.

I'd use scroll bars in the portal. As soon as you decide how far back to look, someone will want to look back one more record. You can change how the portal sorts dynamically by sorting by a calculation field.

Where in Seattle do you tech music?

[tylertul]

Thanks again,

I'll get to implementing these suggestions and then I just have one more question.

Could you provide an example of a script that could be used to create new monthly charges (in a portal)…

1) Find registered students

2) Test to see if a record dated the 1st of next month already exists. If so skip this student go to the next registered student. do the same if test and then if no record exists dated the 1st of next month, create one dated the first of next month.

Sorry for the redundancy. I just wanted to be really clear.

We are www.44school.com

We have locations in Kirkland, Lynnwood, Everett & Bothell. Where are you?

[CyborgSam]

Tyler->

I made a quick and dirty database showing one way of doing this. There's a script that calculates who owe's tuition this month, and presents them in a portal. Check it out and let me know if it does what you need.

_StudentTuition.fp7.zip

[tylertul]

Perfect Sam!

Thanks for all the help.

I am interested in seeing if we can develop a scheduling solution that needs to support recurring weekly appointments with occasional exceptions and have a nice weekly calendar overview. Currently we use excel for this. I want it to tie in with the accounting solution that I am currently finishing up. I don't think I have the expertise to do this. Again we are just exploring the idea and don't have s big budget or anything but if you would be interested and feel like you could create or co-create a solution let me know.

you can go to the website above and email me from there if so.

Thanks again for your help!

~ Tyler

[CyborgSam]

Tyler->

There are many good free calendars available, I suggest downloading a couple and seeing how they work. Then you'll have an idea about how you could incorporate a calendar into your solution. FM 8 makes calendars a lot easier because of it's built in popup calendar.