IF you can invoice the same job more than once, the job (job jacket) table should not be the table used for invoices. A separate invoice table linked by job number should be used for invoices.
You Invoice status table is a start in the right direction, but instead of invoice STATUS, it should just be your table of invoices. The data pertinent to that one invoice should be recorded either in fields in Invoices or in a related table linked directly to Invoices instead of Job Jacket.
I see a group of fields: Item Number1, Item Number2....
These fields probably list specific items for a given invoice. Using separate fields in the same table limits you, only 4 items can be listed in any given invoice and complicate your database design. (Note the 4 separate relationship lines that, I'm guessing, all link to different Tutorial: What are Table Occurrences? of the same table.)
Instead, a related table should be defined for this info and it looks like data that should be linked to the new Invoices table rather than job jackets with a portal (think MS Access Subform) used to work with those individual items. That's an approach that both simplifies your relationship graph and allows a user to list any number of items for a given invoice.
To sum up, your relationships should look like:
Getting your existing database converted over to such a restructured design will need to be done with care. Not only can you use Import records to move data from fields in existing records into such new tables, you can also use Replace Field Contents and looping scripts to move the data into the new tables. Once testing shows that the data has all be successfully moved, you can delete the fields and relationships that are no longer needed.
I agree with your approach but that will take some time to fix. My current solution is to create the invoice, save it as a PDF & store it in the Container field so that there's a record of the original invoice (Job #-1) when it gets overlaid with the next one for that job (Job #-2). However, the InvoiceStatus table keeps track of all invoices sent & payments rec'd.
It would make everyone's job easier if the attached form could automatically populate invoice # and amounts when an invoice is created instead of manually typing that in, leaving room for error.
On what table occurrence is this layout based? Job Jacket?
Assuming that it is, you need two fields for now, and eventually, I'd use one more once you added an actual invoices table.
One field should just store the sequence for that job's invoices, 1, 2, 3, 4
One field should store the original job number.
Your invoice number field would then combine the original job number with this sequence in a calculation:
FirstJobNumber & "-" & InvoiceSequence
A script run from Job Jackets could then run like this:
Set Variable [$JobNumber ; value: JobJacket::JobNumber ]
Set Variable [$InvSeq ; value: Max ( InvoiceStatus::InvoiceSequence ) + 1 ]
Go to Layout ["InvoiceStatus" (InvoiceStatus) ]
Set field [ InvoiceStatus::JobNumber ; $JobNumber ]
Set field [InvoiceStatus::InvoiceSequence ; $InvSeq ]
Set FIeld [InvoiceStatus::FirstJobNumber ; LeftWords ( $JobNumber ; 1 ) // strip off any -1, -2....]
Go to Layout [original layout]
But please note that two users performing this script from the same record in Job Jacket at the same time could produce two new records in InvoiceStatus with the same InvoiceNumber.
Make InvoiceNumber a text field and set up the specified calculation as an auto-enter calculation, clear the "do not replace existing value" check box. Then click the validation tab and specify unique values for this field to guard against that issue.