From Project to Invoice

[Dr. Evil]

I have built a project management database. Projects are parent to Assignments. Assignments carry the service/product and billing info.

So the question is... How do you handle/create from "Project" to "Invoice"? Because in essence a Project is like an Invoice and Assignments are like itemized Charges. A mirrored structure.

I know I can not simply label Project status from "Bid" to "Invoice" because a Project may stay active for many months and require several billings during this time.

So what I have done is...

In a separate invoice table I'm creating a record that carries/relates to Assignment(s). Via this relationship Assignment is mark as "invoiced." So a Project may have multiple Invoices carrying its assignments.

Actually, how I currently have this structured, an Invoice may carry multiple projects and their assignments. Script looks at all Assignments marked as "Invoiceable" for a particular Client and creates an invoice to carry them. This way a single Client can have MANY working projects, but at the end of the month when Invoices are generated, they only have ONE neat invoice to pay on. Clients appreciate this, it is easier for both Company and Client to manage.

So is this a flawed structure?

It does not feel right to have Assignments related to both Projects AND Invoices. But the alternative, Invoices having their OWN children does not feel right either. Sense these children would carry the same exact info as the assignments already do.

Is there another way to properly structure this?

I don't want to duplicate info and I don't want to double relate. Do I?

I would LOVE some feedback on this!

I would be happy to build a demo file on this, discuss, work out the best direction, and develop a best practice on handling this problem. I'm sure there are many out there with a similar situation.

THANK YOU all for your time and interest, very much appreciated.

[comment]

It is always awkward when children (Assignments) are created before the parent (Invoices). Ideally, you'd mark the selected assignments with the current InvoiceID - but this needs to be scripted carefully, because records may be locked by other users.

The other option is to store a list of AssignmentIDs in the invoice itself. However, this carrries the penalty of the assignment's 'invoiced' status being unstored.

I see no issue with Assignments being related to both Projects and Invoices.

[Dr. Evil]

I see no issue with Assignments being related to both Projects and Invoices.

Thanks for the reinsurance! Yes, awkward is the feeling I got when building this, but was the only solution I could come up with. I will continue to further develop structure of Assignments being related to both Projects and Invoices.

Also awkward is I would like to just attach an Invoice to a Client. But with large company Clients, they have multiple Departments, some with different address, etc... So Invoices have to be separated not by Client, but by Department. So one Client may have three invoices at the end of the month because their departments of HR, Foundation and Community Relations all ordered services/products.

This really does not seem to be much of an issue, makes logical sense and I have it working.

BUT where to you put the button/script to invoice the creation of an invoice? Possible to put them in two different places?.. one place when ONLY invoice a particular Client, and another place when invoicing ALL Client with invoiceables.

Put it on the Department layout, Client Layout, Other? Feels right to put it on the Client or even the Project layout. Does not seem to feel right to put it on Department layout for some reason, might just be me. Sense the relationships are a bit complex, I'm struggeling on where to intuitively put this.

[comment]

But with large company Clients, they have multiple Departments, some with different address, etc... So Invoices have to be separated not by Client, but by Department.

If each department has their own projects and invoices (not to mention address), why not make them separate clients?

where to you put the button

That really depends on your workflow.

[Dr. Evil]

If each department has their own projects and invoices (not to mention address), why not make them separate clients?

I did it primarily for reporting reasons, for example if Client request an end-of-year-statement I can call on that Client sort by Department and give nice sub-summaries, etc.

Also, to keep everything Client related in one spot, under one record. Keeps the list down clean too, for example, don't want in value list ACME HR, ACME Community Relations, ACME Foundation. Feels cleaner to me to just choose ACME, then choose from a list of Dept. under ACME.

Also, to utilize the fact that portals can store unlimited data records. If each Client Dept had own record, chances are that record would only need ONE address.

General workflow is:

Create Client (If they don't already exist) > Create Client Dept (assign a Contact and Address to that dept.)

Create Project (Assign Client and Dept to Project) > Create Assignments > Generate Invoice(s) for "invoiceable" Assignments

I guess I will work and play around with this workflow and figure out what feels most intuitive, clean and easy.

Maybe keep the user on the Client record/list and have the script do all the navigations in the background.

So keep user on Client record/list layout, or have user go to the Invoice list layout to generate invoices?.. questions to ponder.

[comment]

Have you considered starting from a 'dashboard' layout? You could have a message there, e.g. "27 assignments waiting to be invoiced" along with a button the deal with them.

[Dr. Evil]

Have you considered starting from a 'dashboard' layout? You could have a message there, e.g. "27 assignments waiting to be invoiced" along with a button the deal with them.

Yeah, I have considered it. I have a dashboard type area on all the "form" view layout on the left hand side. Contains a self-type relationship and actions. I could put the invoice script in here. Or maybe create a whole new layout like you suggested and call Overview or something. I will try some things out and see what feel the best. THANKS! :)