Populating an Invoice db

[LaRetta]

Hi everyone. About three weeks ago, I posted a question on "basic structure concerns." Everyone helped me and it was determined that I needed a separate Invoice db because I have a many-to-many situation. I have done that. But last night, reviewing back through 'Finds', Kennedy responded to a gal with an Invoice file, NOT TO EXPORT to create her invoice file ... it was a bad idea. And that's exactly what I have done. Help!

If I do not export to create my invoice file, how do I populate it? I originally created an Invoice db with Invoice#, Service# (joined to Service db). Then I just placed the fields from Service db on the Invoice --- problem is, what is invoiced isn't what is in service. Service must be services specifcally performed, posted and then never changed. Invoices are charged to each Payor at different rates and must contain data fields for ProcedureType, LineItemTotal, etc. to take into account each Payors requirements. These figures will be different than Service and an Invoice may be split into two (primary payor) and (secondary payor).

1) When a service is created, I could have it also create a duplicate in the Invoice db, but it would need to update with any changes to the Service item. I don't want to be making a change in both db's if a Therapist makes a mistake.

2) When ready to invoice, the Service db items would need to be 'frozen' or 'posted.' At that time, I manipulate the invoices and change their lineitems according to their specific billing requirements.

So my question is this ... in my situation, would you export when ready to bill, or would you populate the invoice db on an item-by-item basis? If so, how do I create a mirror of Service which will update with any changes to Service?

[Kurt Knippel]

I am not sure why it is a bad idea to export, unless what the person was refering to was the fact that an invoice is NOT a copy of the invoiced services, thus you only need to relate to the service db and not really export anything to it.

In you case an invoice is a collection of serviced billed to a specific payor at a specific rate and with some specific criteria. This is the information that is collected in the invoice record, not the actual service information. You only need to have the InvoiceID field in the ServicesDB for the relationship to occur.

Give us a little run down on your current structure and maybe we can help some more.

[LaRetta]

You asked for a 'little rundown' but I can't. This whole thing is connected and I want you to see the picture.I apologize for the length of this, it

[CobaltSky]

At the risk of oversimplifying, it is best to use related data unless/until there is something you need to do which can't be done that way (for example, the service descriptions need to be customised so that they are different for each iteration).

Another reason for exporting data (or using lookups) can be so that if changes are made to items details after an invoice has been issued, the historical records will still be accurate (ie will not reflect a subsequent change

Even so, rather than exporting data and ending up with duplicate entries all over the place, it can be preferable to use lookups selectively, so that only those fields which will need to be customised - or preserved historically are looked up and related data is used for any/all other elements.

From your description, it is not clear that there is a need to create complete multiple copies of the data for manipulation so some combination of lookups and 'live' references to related data might be a viable alternative. If so, then it would be worth considering.

[LaRetta]

Hmmm, I will certainly review the process before I implement it. Many fields, such as ServiceDate, ClientCode, StaffCode, LocationCode wouldn't change (even though they are required fields on the Invoice. So I can leave them in Service and just look them up when needed. ProcedureCodes and Rates per Unit/Hour/Session, will most certainly change with each Invoice billing. So, I will export those fields, along with the Service# to preserve the relationship and go from there.

I've spent a lot of time reviewing the structure on paper, but I still run into things I hadn't thought of. I will be glad when I can run a few sample records clear through to see what happens. Thanks for taking the time to assist me on this!