7 Replies Latest reply on Nov 6, 2013 1:39 PM by TommyHansen

    Creating an Invoice number using a script?



      Creating an Invoice number using a script?


      Hi All, I'm trying to figure out how to auto generate an invoice number at the time an invoice is printed rather than using the serial number auto formula that generates a number when the record is created.


      I have a data entry layout setup where we can fill in and tweak information as it comes in. It may take a month before we are ready to print out an invoice for a given group of records. We need the invoice numbers to be in order when we request to have them printed rather then when the record is created.


      Is there a way to add a button with a script to our invoice layout so that when we go to print a selected invoice we can push a button and generate a number?


      Thanks in advance for your help,




        • 1. Re: Creating an Invoice number using a script?

          We assign invoice numbers at time of printing here also.

          You can use a related table of Invoice Numbers and set a field in the related table to auto-enter a serial number for your invoice. If you use a script to print the invoice, the script can create the related record just before printing it and then the related record in this invoice numbers table will display the correct next number in the series.

          The relationship looks like:

          Invoices::__pk_InvoiceID = InvoiceNumbers::_fk_InvoiceID

          with "allow creation..." enabled for InvoiceNumbers

          A second field, InvoiceNumber is defined as an auto-entered serial number.

          Then a single line in your pirnt invoice script will generate the invoice number "on demand":

          Set Field [ InvoiceNumbers::_fk_InvoiceID ; Invoices::__pk_InvoiceID ]

          Please note that this method has built in safe guards that avoid a) assigning a new number each time the invoice is printed b) allowing two users to generate duplicate invoice numbers by performing this script at the same time.

          The auto-enter serial number field option insures that the numbers will be unique while the above script step does not change any data if the related InvoiceNumbers Record already exists.

          • 2. Re: Creating an Invoice number using a script?

            Thanks for the input PhilModJunk, I'm a bit of a newbee with Filemaker Pro so I'm having a litle trouble understanding your anwser.


            I have a text field for my invoice number and I've figured out how to make it auto-assign a serial number when a record is created.


            I've labeled it "INVOICE_NUMBER".


            Are you saying that I create another field called "InvoiceNumbers" and make that auto assign a serial number? 

            Now I relate both the fields "INVOICE_NUMBER" and "InvoiceNumbers"?


            I currently don't have any scripts in my layouts but I think I can figure out how to do that with the Filemaker help stuff?


            Thanks for taking the time to help me out with this,



            • 3. Re: Creating an Invoice number using a script?

              Open Manage | Database | Tables and use it to create a new Table named InvoiceNumbers.

              Then click the fields tab and define the two Fields that you need for this table: _fk_InvoiceID and InvoiceNumber.

              Unless your auto-enter settings include non numeric characters, I recommend that InvoiceNumber be of type Number rather than text. But text can work here if you need it to be text. (You may also want to specify text if your InvoiceNumbers include leading zeroes.)

              While in Fields, select your original table "invoices?" from the tables drop down and add an auto-etnered serial number there, _pk_InvoiceID unless you already have a serial number field already defined in this table.

              Now click the relationships tab and drag from Invoices::__pk_InvoiceID to InvoiceNumbers::_fk_InvoiceID (the pk and fk stand for primary key and foreign key, you do not have to label your fields this way, but it's a useful way to keep track of which field in your table serves as the primary key.)

              Double click this relationship line that you've just created by this drag option to bring up a dialog box for choosing options for this relationship. Click the "Allow Creation..." check box for the InvoiceNumbers table.

              Now you can click oK to dismiss the Manage | Database window and you can now select manage | Scripts to create a script for printing your invoice.

              This script could be as simple as:

              Set Field [ InvoiceNumbers::_fk_InvoiceID ; Invoices::__pk_InvoiceID ]
              Print [Restore ; no dialog]

              You can also just put a button on your layout named "assign invoice number" and set it to just perform the first step if you want to keep this extra simple.

              To display this InvoiceNumber on your layout, enter layout mode and use the field tool to add the InvoiceNumber field from the InvoiceNumbers table.

              Note: When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

              Also, you may find this Invoices demo file useful. It does not assign invoice numbers on demand, but it does demonstrate the basic design elements of a typical invoicing system in very simple fashion:  http://fmforums.com/forum/showpost.php?post/309136/

              1 of 1 people found this helpful
              • 4. Re: Creating an Invoice number using a script?

                Thanks a bunch PhilModJunk, I figured this out thanks to you! Kudos for putting that little bit about "setting up the set field", I would have not got it without that set of instructions.


                Thanks again for all your help, you all have been wonderful at helping me along the way.



                • 5. Re: Creating an Invoice number using a script?

                       If you create — say 10 invoices before printing — without printing (or pushing the magic button)... and then print invoice no. 10, this invoice will be given a serial number (invoice no.) +10 and not the next +1 ?

                  • 6. Re: Creating an Invoice number using a script?

                         No, the number is assigned at the same time that the invoice is printed. The order in which the records are created do not matter. Unprinted invoices will not yet receive an invoice number.

                         BTW, this thread is several years old. Threads where the original post is more than 3 months old no longer pop up into recent items when a new comment is posted to it. I only saw your question by accident. To get better visibility for your questions, start a new thread. You an always include a link to an older thread if such is needed to establish context.

                    • 7. Re: Creating an Invoice number using a script?

                           Ahhhh, I see what you're doing. Very simple and neat.

                           Much simpler (and faster I think) than the script I'm using.