Need a check deposit form

[bails52]

I'm a long-time but basic user and cannot figure this out. I have a database for a workshop where a student might have several checks from various sources covering his tuition. And they could come in at different times. And one check might cover several students! I'd like to be able to enter the check info and generate a list of check #s, bank #s, and amounts to use as a bank deposit slip. I think I have all the data fields I need, but can't get things to come together correctly. I'd be happy to send the file so you could see what I'm talking about.

Thanks!

Steve

[jamesducker]

First things first...

...do you have a table of cheques received?

What you need is such a table, and then a portal on your student page which shows all the cheques that apply for each student.

You can then do sum(cheques::cheque value) to find out how much each student has paid.

From the cheques table you can find all the cheques received since the last date you went to the bank (regardless of who they are from) and use that for your bank slip.

Hope that helps.

James

[bails52]

Thanks James. I think I get it. The checks would be entered on another file and show up for that student, right? Any idea how we might handle school checks that cover multiple students?

Steve

[jamesducker]

Correct.

If you get a cheque covering multiple students you need to put in one entry per student, so that the correct balance is shown as having been paid off each student's account.

If you put a cheque number on each entry then this will help you identify where a cheque has been split and entered many times. If you printed your bank paying-in report with a subsummary by cheque number then you would be back to seeing one entry per cheque, even for the ones that had been split.

James

[comment]

You don't need another file - you can have a single file with 2 tables, Students and Checks, related as:

Students::StudentID = Checks::StudentID

To handle checks that relate to multiple students, you could (maybe) settle for turning the StudentID field in Checks into a checkbox, and adding a calculation field cAdjustedAmount =

Amount / ValueCount ( StudentID )

Then change the calculation field in Students to sum the adjusted amount, instead of Amount.

Note that this method assumes that the check amount is always divided equally among the students assigned to the check; it also has other limitations, esp. in reporting. A proper arrangement would have a third table joining Students and Checks, where the amount can be split any way among the students.

[bails52]

James,

I haven't tried doing this yet, but will later today. Just wondering. Is there a reason why this can't be done within the original data file itself?

Steve

[jamesducker]

It can be done in the same .fp7 file, what you need is a new TABLE within that file.

J

[bails52]

OK, I'm learning. The table seems simple enough. Bu here's something that seems clunky. Because we might have as many as three checks paying for one student, we have created three fields (Paid by:1, etc.) with corresponding fields for bank #, check #, date. These, however, are not really tied together in any "relationship" and seem to make creating a nice, simple check listing for the bank rather difficult. Is there a better way to set this up?

Thanks for your time!

[jamesducker]

You shouldn't have three fields if you have three cheques:

You should have one record in the student table per student.

You should have one record in the payments (cheques) table per payment made...

...if a student pays for himself only in one cheque, you have one record;

...if a student pays for himself in seven instalments, each with different cheques, you have seven records;

...if a cheque arrives that covers the payments for three students, you have three records.

I hope that's how you have it set up. Because you are entering payments, not cheques, in this table it is probably better to name the table 'payments'.

You are right: there is no 'official' relationship between multiple payments that happen to have been made on the same cheque (ie where one cheque covers multiple students).

To get a simple cheque listing for the bank you need to create a report of the payments that is summarised by cheque number. For this you will need a summary field on the payments table called "total value" (= total of payments::value). Then to make the report:

1) make a list layout of payments if you haven't got one already

2) add a SUB-SUMMARY PART (sub-summary when sorted by cheque number) and display the 'total value' summary field on it

3) go to browse mode, find all records

4) sort by cheque number

5) go to preview mode.

You should see the sub-summary part appear once per cheque, underneath all the payments that the cheque covers.

Note that you can delete the 'body' part from the list view to give you only a list of the cheques.

Steps 1 & 2 will only need doing once; make a script for the rest that will make it easier to run your report.

Hope that helps.

James

[bails52]

Ah. This is making more sense to me now.

Let's say a student makes two payments. He has his own record (which we have now). And there are two records for him in the payment table. How can I see on the student's record both of his payments?

[jamesducker]

You create a portal (grid) of payments on the student layout: portal shows all related records from the payments table so it shows as many payments as there are for that student.

Relationship between the two tables should be students::uniqueID --> payments::studentID. If when creating the relationship you tick 'allow related records to be created in this table' then your portal will always have a blank row at the bottom. Start typing in this blank row and it will create a payment record for the student automatically.

Hope that helps.

James

[bails52]

Cool!

OK, I'd better get to work.

Thanks again.

Steve