One option is to just use the same record for Quote, Work Order and Invoice, changing a status field from Quote, to Work Order to Invoice as it moves through the process.
Many businesses, however, need to be able to look back and tell what information was current when the quote was sent to the customer, when the work order was sent to production and then what details were current when the invoice was used to bill that customer.
In those cases, you can still use the same table, but a script can use duplicate record to create a new copy of each successive record, but labeled with the appropriate status (Work Order or Invoice). This same script can also duplicate your related line items if you are careful in how you set this up.
Ok I have the duplicate script figured out(this includes duplicating the related line items). Now I am trying to find a way to relate them.
You can define a related table for this with an auto-entered serial number. When you create a new Quote, a script can create a new record in this related table and enter it's serial number in a field in the quote record. When you duplicate this record to create Work Orders and invoices, this field will also be duplicated and thus all three records for the same transaction will have this common serial number from the related table.
ok so how would i configure the tables and table occurrences?
OOOooo I see what your saying... So this related table just has one field that is a auto enter serial, and is only used for relating the records with each other
You might use two fields, QuoteID and SerialNumber and use this relationship to add the serial number to your Quote record:
Invoices::InvoiceID = masterSerial::QuoteID // enable "allow creation of records via this relationship for MasterSerial
This script then can be used to generate the serial number field:
If [INvoices::InvoiceStatus = "Quote"]
Set Field [MasterSerial::QuoteID ; Invoices::InvoiceID ]
Set Field [Invoices::MasterSerial ; MasterSerial::SerialNumber]
This can be run from an OnRecordCommit script trigger set on your Quotes layout. If the related record in MasterSerial does not exist, the record is created and the MasterSerial field in Invoices is updated to receive this new number. If the related record already exists, nothing changes.
Ok i get that, cause there will many orders to one master serial. But what is the point of having the MasterSerial::SerialNumber Field?
Actually it's Orders----MasterSerial The relationship is based on the serial number in Orders which is unique with each new record. The only purpose to that relationship is to assign a unique serial number to each new quote.
There will be exactly one order to a given master serial number, but up to three records in the orders table, Quote, Work Order and Invoice would have that same number as they would document different stages of the same order.
You could define another relationship:
orders::MasterSerial = SameOrder::MasterSerial
where SameOrder is a new table occurrence of Orders. This could enable you to set up a portal that lists all the "phases" that exist for a given order, just to given one possible use for it.
SerialNumber is the name I gave to the serial number field that is used to generate the serial number via FileMaker's Auto-enter serial number feature. Please note that my relationship in the last post only links a Quote record to this table. The MasterSerial field in orders allows you to keep your Quote, WorkOrder and Invoice for a given order linked.
I've set it up this way for two reasons:
- It allows you to generate a serial number "on command" that is not unique to one record in Invoices the way it would if you defined it in Invoices.
- It will work faultlessly in a multi-user environment where you might have more than one user generating new Quote records (and their master serial numbers ) simultaneously--a situation where many other approaches run the risk of allow duplicate numbers to be created.
It occurs to me that you may not always start with a quote in every case (repeat orders come to mind). You may need to modify this approach so that a WorkOrder with no master serial number can also generate this value so that its invoice record will have the same value.
I swear I should be paying you for all the help you've been giving me lol. Again thanks a ton.