Invoice Portal

[mikemccloskey]

I have created a client database (FM 3) that I am wishing to alter to be able to include invoicing options. The way I have it set up now is that each client may have any number of sessions (records) in my client record database. Client info (address, etc.) is looked up from another file. I have been producing invoices from doing finds and sub summary sorts in my client records file. I would like to change it to have another file produce invoices based upon portal records already created (related to) in my client records database. My question is this - being that my clients records already have been created, how can I monthly create invoices for clients? I know I need to have a relat. based upon invoice # but how can I tell my invoice record to relate to records that have already been created? thanks, mike mccloskey

[Tylonius]

You have two dilemmas to solve that I recently had to solve myself.

1. Creating the Records

Use the import records command to bring older jobs into your Invoice DB, making sure each record has its own invoice# (a job# derived code works best). Then, each month, create a found set for each client or job being billed in the appropriate DB, and then import the records into your Invoice DB. You only need the job#, and whatever other data you need to relate to your Client Records DB.

2. Getting the Numbers Into the Records

Create a calculation field in your Invoice DB called Total and set it to Sum(Client Records by Job Number::Billing Data). It will enter its own data and update automatically.

Make sure you have a relationship to Client Records by Job Number.

Gracious Thanks to Kurt Knippel for answering dilemma 2 (and ending my headaches) in another topic.

[This message has been edited by Tylonius (edited October 18, 2000).]

[This message has been edited by Tylonius (edited October 18, 2000).]

[This message has been edited by Tylonius (edited October 18, 2000).]

[This message has been edited by Tylonius (edited October 18, 2000).]

[mikemccloskey]

Thanks for your kind reply. I am not sure if I was clear. Let me try. This is a database for a psychotherapy practice. Each session with a client is logged in the "Client Records" Database. Each session or payment or charge is indicated by a separate record. Therefore, John Doe may have any number of records associated to his name. If I create an "Invoice" database, I would like each record to be an invoice which would show - via portal - the records in the "Client Records" which are to be apart of that invoice. I don't think I need or want to import records here. Am I not seeing that correctly? I can create an invoice record with a clients name on it and relate, via portal, the sessions that correspond to the invoice via invoice #, etc., but how do I do this automatically each month. Manually I would have to create a new Invoice record, decide which "Client Record" records to display in the portal (relate these two by unique number, etc.) and print it. If the Client Records have already been created over the last several weeks and have no key number that would associate them to a particular Invoice number, how would I do that automatically from the "Invoice" DB. Whew! Clear as mud? thanks

[mikemccloskey]

Thanks Kurt. That was the only solution that I came up with. As I was thinking about it, it began to become very convoluted in terms of finds, replacements, etc. I was hoping there was a more elegant way.

[Kurt Knippel]

quote:

---

Originally posted by mikemccloskey:

Thanks for your kind reply. I am not sure if I was clear. Let me try. This is a database for a psychotherapy practice. Each session with a client is logged in the "Client Records" Database. Each session or payment or charge is indicated by a separate record. Therefore, John Doe may have any number of records associated to his name. If I create an "Invoice" database, I would like each record to be an invoice which would show - via portal - the records in the "Client Records" which are to be apart of that invoice. I don't think I need or want to import records here. Am I not seeing that correctly? I can create an invoice record with a clients name on it and relate, via portal, the sessions that correspond to the invoice via invoice #, etc., but how do I do this automatically each month. Manually I would have to create a new Invoice record, decide which "Client Record" records to display in the portal (relate these two by unique number, etc.) and print it. If the Client Records have already been created over the last several weeks and have no key number that would associate them to a particular Invoice number, how would I do that automatically from the "Invoice" DB. Whew! Clear as mud? thanks

---

You are going to need some method of deciding which ClientRecords records are part of each monthly invoice and assign them the InvoiceID.

I would recommmend a script which starts by grabbing the InvoiceID, then goes to a found set of records in the CustomerRecords file, where there is no InvoiceID set for that client. Then simply replace the empty InvoiceID with the InvoiceID that you copied out of the Invoice file.

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=