Creating multiple invoice number sequences and create on commit/print
I have created a database with clients, treatment records and am now trying to set up an invoice part.
The invoice part works fine with invoice numbers in sequence (10001, 10002... etc) created by a number field with auto-enter serial.
My two challenges is:
1)I need to set it up so that every user/account (at the time 3 or 4) has it´s own line of invoice numbers.
Therapist 1 : 10001, 10002...etc
Therapist 2: 30001, 30002...etc
Therapist 3: 50001, 50002...etc
Each therapist is self-employed (I don´t know if it is the right word, but I guess you can understand). Therefore, due to tax reasons, they need their own separate sequence of invoice numbers. For the same reason each sequence must be consecutive.
2) To eliminate problems with invoice number sequences, for instance if a therapist by mistake click on the button "create new invoice" I need the invoice number to be assigned when the invoice is printed and not when "create new invoice" is clicked.
I can´t just use "on commit" instead of "on creation" with the auto-enter serial, because, as it is right now, I create the invoice lines in a portal, and I guess it causes problems with the relation to the "patientId" field.
The database is created so that the user has to log in using their own initials, for instance TK.
I have 4 tables (related to this part): "Patient", "Faktura" (invoice), "FakturaLinier" (invoicelines), "Produkter" (products). They are related as you can see in the attached picture.
I guess I can create the 3 or 4 invoice numbers sequences with a calculation, but unfortunately I am not skilled enough yet to figure out how it should be done.
Help with some details will be very much appreciated as I am quite new to FileMaker.