Looking for experienced advice on what (to do) and what (not to do) in this process. I have a tendency to over complicate things and give user too many options.

Goal is to build a solid fail proof billing scheme and script.

Below is the process up for discussion.

Process Outline...

General scheme for moving an Item through various billing states; "Estimate", "Approved" and finally, "Invoiced".

When an Item is created, ItemStatus is automatically set to "Estimate"

Once Item has been approved by Client, user then has to manually set ItemStatus to "Approved". This allows Item to be picked up for invoicing.

After InvoicingScript has been ran, the script will set ItemStatus to "Invoiced". Once invoiced ItemStatus can no longer be changed by user.

Invoicing Script Breakdown...

> finds Client(s) with "Approved" items

> create Invoice record, capture $invoiceID

> on found Items; Set ItemInvoiceID with $invoiceID, set ItemStatus as "Invoiced"

> preview invoice report

> go to next Client record

(loops above steps for each found Client, ends script on last record)

Are there any apparent problems with this? Maybe some important missing steps? Any way to modify, or make more streamline?

THANK YOU ALL for your help on this, I look forward to this discussion!

How would you allow user to change item's billing status?

A ) Scripted button

B ) Field with value list, once = "invoiced" field can NOT be modified

C ) Other, please explain

Are the items on estimates/work orders when they're in the "Estimate" and "Approved" phase? In other words, do the items appear on quotes and if customer approves, then the items should appear on invoices?

As a general rule, I think Invoice-line-items should be treated as separate entities from Order/Quote line-items. That is to say, they should exist in separate tables.

Are the items on estimates/work orders when they're in the "Estimate" and "Approved" phase?

As a general rule, I think Invoice-line-items should be treated as separate entities from Order/Quote line-items. That is to say, they should exist in separate tables.

Yes, Items are a child table of Project table. Project can have many Item children.

I have seen and used models where you create an Estimate, once approved by Client a script is used to basically duplicate the estimate into an Invoice.

This is basically what I'm trying to do minus the duplication of Item record. I do create a new separate table Invoice from Project. The Invoice has a special relationship to Items, when this relationship is made, Item show as invoiced.

I have attached a demo file to help explain my direction. Note Item table is call Assignment in the demo.

project_invoice_model_v3.fp7.zip

Your method of marking Items "Invoiced" seems to work. In the system we built at our company, Invoice Items are separate entities from Project Items — Seems to allow for most flexibility. Also, you may want to disable Field Entry in browse mode for your Invoice Report layout (Unless you want users to be able to edit data directly on the invoice.)

Your layouts look great, btw.

How would you allow user to change item's billing status?

With scripting.

...at our company, Invoice Items are separate entities from Project Items — Seems to allow for most flexibility.

Would you mind sharing a demo or empty clone file of this system? Thanks for your feedback!

Try it this way, maybe?

GenerateInvoices.zip

Try it this way, maybe?

Thank you Comment! This demo file is truly appreciated!