9 Replies Latest reply on Nov 5, 2014 10:45 AM by Willz

    Need Script for TWO sets of Invoice Numbers.

    Willz

      Title

      Need Script for TWO sets of Invoice Numbers.

      Post

      Do you have script example that creates separate set of new invoice numbers.
      1. with just a automatic new number. (this I already have working)
      2. with a prefix like “TE” for Tax Exempt for out of state invoice.

      Please Advise,

      Thank You

        • 1. Re: Need Script for TWO sets of Invoice Numbers.
          philmodjunk

          Is this one series of numbers or two?

          0001
          0002
          TE003
          0004

          Or this?

          0001
          0002
          0003
          TE001
          TE002

          • 2. Re: Need Script for TWO sets of Invoice Numbers.
            Willz

            Yes two sets of numbers like the SECOND set you ask

            • 3. Re: Need Script for TWO sets of Invoice Numbers.
              philmodjunk

              I was afraid of that. Such invoice numbers should NOT be used as a primary key in your invoice table. Care will need to be taken if this is a file accessed by multiple users that might be creating invoices at the same time.

              Is this the only two values in the series or have you just given two as an example? I know of a way to generate serial numbers "on demand" that could be used to create two different serial number series, but it's not the option to use if there might be more than just the two invoice types. If there are (now or as a possible future) more than these two invoice types a self join by type can be set up as a way to assign the next value in the sequence.

              • 4. Re: Need Script for TWO sets of Invoice Numbers.
                Willz

                Your questions:

                - It is multi user on server13, 2 or 3 people, but not likely at same time.

                - Yes only two values needed - 1. just serial number, and  2. serial number with prefix TE

                Attached is screen shot of the old script that was used but always had problems.

                1. problem when making new invoice it would not start with the most recent number, (maybe not knowing which set to use) had to click and click (sometimes 200 clicks) as it stepped thru existing numbers to get to the last one, then fm would start a new invoice with new number.

                Other solution I was planning to do was to just make a new field adjacent to the invoice number field that auto populates with if statement: if customer is not from VA, from out-of-state, thus TE would appear on the record.  Also used in a Report.

                Thanks PhilModJunk for your help and ideas.

                • 5. Re: Need Script for TWO sets of Invoice Numbers.
                  philmodjunk

                  This method should work even if you have 50 people creating new invoices and running this script to assign serial numbers all at the same time.

                  The method requires 3 auto entered serial numbers in three tables. The first is the real primary key and can stay fully hidden from your users. The other two are the two you want for this purpose.

                  Your tables and relationships would be set up like this:

                  InvoiceNumbsTE------Invoices_-------InvoiceNumbs

                  Invoices_::__pkInvoiceID = InvoiceNumbs::_fkInvoiceID
                  Invoices_::__pkInvoiceID = InvoiceNumbsTE::_fkInvoiceIDe

                  enable "allow creation of records..." for both the InvoiceNumbs tables.

                  Define a simple auto-entered serialnumber field in InvoiceNumbs called InvoiceNumber. Do the same for InvoiceNumbsTE but specify either TE0000001 as the next serial value or possibly 1TE if you want to put the "TE" on the end of the number instead of the beginning.

                  Now this script will generate the needed invoice number:

                  If [ Invoices_::Customer Location = "VA" ]
                     Set FIeld [ InvoiceNumbsTE::_fkInvoiceID ; Invoices_::__pkInvoiceID ]
                     Set Field [Invoices_::InvoiceNumber ; InvoiceNumbsTE::InvoiceNumber ]
                  Else
                     Set FIeld [ InvoiceNumbs::_fkInvoiceID ; Invoices_::__pkInvoiceID ]
                     Set Field [Invoices_::InvoiceNumber ; InvoiceNumbs::InvoiceNumber ]
                  End If

                  That should work. you might need to insert a Commit records between each pair of set field steps, but I don't think it's needed here.

                  How this works, The first set field step in each pair of set field steps generates a new record in the related table and thus the auto-entered serial number field, InvoiceNumber, will auto-enter a new unique serial number.

                   

                  • 6. Re: Need Script for TWO sets of Invoice Numbers.
                    Willz

                    I got it - I understand!!

                    This is mega fabulooosooo PhilModJunk

                    Thank You Thank You

                    WillzShire

                    • 7. Re: Need Script for TWO sets of Invoice Numbers.
                      Willz

                      PhilModJunk,
                      Whoops - a snag somewhere
                      Got this set up - just doesn’t put the new number into the field. Invoices_::Invoice Number.
                      See screen shots of script and chart.
                      What did I miss?
                      Please Advise, Thank YOU!!

                       

                      • 9. Re: Need Script for TWO sets of Invoice Numbers.
                        Willz

                        OH Nevermind - I figured it out  - forgot to put auto serial number in pkInvoice ID. Working now.

                        THANK YOU AGAIN

                        for the script!!!