Do I Really Need a Line Items Table?

[Mark Ballard]

Hi to all, please excuse my lack of knowledge but i'm learning fast!

I am creating a solution for my wife's osteopathic practice here in England. I have a good "patient" table which works fine and i have added a "bookings" table which appears through a portal in the main layout. The bookings table is very simple just with a BookingID, PatientID, Name(Looked up), date, time and treatment type. Now I need to add an "Invoice" table.

What i need to know is do i really need to produce an "InvoiceLine" table to produce the invoice or can i just use a portal to list these bookings relevant to the patient?

Her patients require different combinations of invoices from each appointment separately to one invoice for a whole set of treatments. How would it be best to facilitate this, just by a search?

On a side note i'm hoping that with the booking info, i will be able to get this into "calendar developer tool" by formulationspro. Any advice on steps i need to take in advance to make this happen.

Any help is very much appreciated.

Mark

[Oldsneekers]

If my understand of the problem is correct, if you have more than on invoice per booking, it would be good the have a separate table occurance (TO). One patient can have more than one booking, therefore you have a separate booking TO. Similarly if a single booking can have more than one invoice, you would find it easier to have a separate invoice TO. However, if the number of invoices per bookings are limited you may wish to create a separate field in the bookings TO for each possible invoice. Since the fields for invoices do not contain huge amounts of text (I assume), it will be easier to work with a separate TO for invoices.

I hope this helps.

Edited January 26, 2006 by Guest

[Mark Ballard]

Thanx for the info.

I think i didn't explain it quite as well as i could have done. Each invoice needs to show either one, or several bookings. The Bookings are shown and entered in the main patient window through a portal.

Would it work to put a simple "yes/no" field in the bookings table to select which bookings were to be invoiced. Then place a button that would switch to the invoice layout, do a new record and then import the "yes" booking info and the patient information? Or am i expecting too much!?

I've attached the file this time, the patient window has had the portal added on, The payment record fields are where we currently keep a record of the patients payments.

Many Thanx

Patient.fp7.zip

[stanley]

Mark:

Welcome to the Forums.

You can use your current Bookings table to produce the line items for your Invoices so long as each booking produces a single line in an invoice. However, you'd need an Invoices table (with an Invoice Number, the date invoiced, etc.) which would show multiple related line-items (taken from Bookings, but showing the charges, etc.) I'll have a look at your file and comment more once I've digested it.

-Stanley

[stanley]

Mark:

In your file, you have the Invoice related to the Patients, which are related to the Bookings. You should probably relate a second table occurance of Bookings to the Invoice - this way, you can tell which booking belongs to which invoice. Otherwise you'll keep building a larger & larger invoice for any given patient, rather than unique invoices for selected items.

-Stanley

[Mark Ballard]

Hi Stanley,

Again thanx for the help.

I've added the second table occurrence as suggested. But am unsure as to how to tell each booking which invoice to be associated with.

Would my suggestion from above actually work and is it possible to do all this with one script?

prev.post:

"Would it work to put a simple "yes/no" field in the bookings table to select which bookings were to be invoiced. Then place a button that would switch to the invoice layout, do a new record and then import the "yes" booking info and the patient information?"

I'm heading off for some sleep now as we're 5 hours ahead of you in New York, but i'll check back tomorrow. I really appreciate your help here.

ps any views on the calendar developer tool by formulationspro?