AnsweredAssumed Answered

Creating multiple invoice number sequences and create on commit/print

Question asked by fysio2008 on Oct 13, 2013
Latest reply on Oct 16, 2013 by fysio2008


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.


     Both problems would not arise if there were only one therapist and the therapist did not accidently click on "create new invoice", but unfortunately the databse is not being used in a perfect world :)


     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.