Invoicing Clients from 2 separate tables

[Ch.]

Hi,

I have an invoicing solution for training services. I have also 2 files, Clients (i.e. payers) and Students (attendees). Sometimes Clients will send an employee and pay for the training. Other times the student will pay for the training. And other times, the Client will pay half and the student will pay the other half. I can get FM to create invoices for either table (using for example c_InvoiceTo defined as ClientName & StudentName), but the question is should I? or is there a better solution?

Until now I have always thought of these as 2 distinctly separate groups. But the problem with this is if I make a Student also a Client, I have to track virtually the same data in 2 places.

Any suggestions would be welcome.

CH.

[comment]

Perhaps this thread is similar enough to your problem to indicate a possible solution?

[Ch.]

Hi Comment,

Thanks for your reply. I had a look at your demo file and I like it a lot.

But I don't think it's right for what I need - your solution requires the data from both tables to be combined into 1 table, but I need to keep both tables separate (for reasons too interminable to explain). Invoice data also has to be exported to an accounting application.

The reason I'm asking if I should invoice payers in 2 separate tables (by entering either the pk_StudentID or pk_ClientID into the fk_InvoiceID) is beacuse I'm wondering if it will become too complex in reporting and analysis etc, or if there is some obstacle that I haven't thought of.

Edited May 1, 2006 by Guest

[comment]

Now I am not so sure what your question is. I'd think the invoice should have two foreign keys: StudentID and SponsorID. The relevant StudentID should be always filled. If a student has a sponsor, and the invoice is issued to the sponsor, then the SponsorID field should also be filled.

It seems very similar to a billing/shipping address situation, except that you need to generate two invoices when a student has a partial sponsorhip.

[Ch.]

Sorry if I haven't explained it properly:

A student can avail of 1 or more training sessions.

An invoice is to be issued to a Client for 1 or more students (i.e. the clients employees) for all training sessions attended (even if 1 student attended more than 1 session), or directly to a Student (who is paying for him/herself). The invoice line items will show who attended which training session, how many were attended by each student, the cost and when.

"I'd think the invoice should have two foreign keys: StudentID and SponsorID" - on the training session record I have both of these ID's so I can tell who is paying and who is attending.

I agree that 2 invoices need to be generated for partial sponsorship.