complicated problem

[Gaz 007]

I have been given an almost finished database and asked to make so dramatic changes to it so this is long and complicated so please bere with me,

ok there are 4 tables details, courses, accom and invoices.

So the students details go in on the details screen name etc, when they book a course a new record is created in the courses table and accom functions in the same way. at the end of the details layout there tab with portals to show the info from the accom and courses table.

Currently each record in accom and courses had a field called invoices which is linked to the invoices table. So when someone wants to print an invoice they create a new record in the invoice table with students stu num and copies the value for invoice number into the relevant fields on the accom or courses layout which then generates an invoice on the invoice layout.

Now here's what needs to be changed users are forgetting to copy and paste the invoice number into all the fields that they want to bill someone for so the student dosen't always get invoiced for what they are supposed to. So what I need to do is generate an invoice and have it filled in courses and accom are booked. Sounds simple but the problem is people may make multiple bookins which end up on different invoices, e.g. a student books 3 courses and accom for 4 weeks so that would go on invoice 123 but then a week later they book something else which should not go on invoice 123 but on a new invoice say 129.

Ok so here is how I thought I would implement this but I ran into some major problems .

ok i create two fields in the details table isgenerated and isprinted both set = 0. So when I want to either enroll someone in a course or book them accom I click the relevant button which runs a script that checks if isgenerated = 0 or if isprinted = 1. If either of these are true it creates a new record in the invoices table and copies a the new invoice number into the relevant fields and sets is generated = 1.

So every booking made after that sees that isgenerated = 1 so it knows a invoice is already generated and uses that numbers. Now when I use the print button it makes isprinted = 1 so the next booking needs a new invoice my problem when there are multiple invices is tieing the setting of isprinted = 1 to that paricular invoice number so that if there are multiple invoices when i click print which runs the script that it dosent set all the invoices isprinted to 1.

So basically what I saying is that I need to tie the two fields isprinted and isgenerated to a particular invoice number aswell as a particular student.

Has anyone got an idea or a simpilar implementation?

[T-Square]

The way I have dealt with separated services and billing is as follows:

1) Store a student's account balance in their record, as a numeric field.

2) Create an AddService routine that calculates the billed amount and subtracts this from the client's balance.

3) Create an AddPayment routine that adds to the client balance and tags unpaid services with the payment ID.

My clients don't send out invoices, but it would seem to me that you could create a separate table for invoices that noted the student ID, the date, and maybe the invoice amount. Add a numeric field to the accoms and classes tables for the InvoiceID. Then, when you create an invoice for a student (passing the student ID), a script would locate all classes or accoms (whatever they are) for that student that do not have an invoice ID, and would put the invoice ID into those records. An invoice could then be displayed on a layout with a portal showing all the related classes and accoms, which would easily be printed.

Does that help?

David