4 Replies Latest reply on Feb 3, 2010 9:58 AM by philmodjunk

    This is gonna be a doosey - Sequential Invoice Numbers by Company

    dwhylanjr

      Title

      This is gonna be a doosey - Sequential Invoice Numbers by Company

      Post

      I sure hope someone is interested in helping me with this particular script challenge.  Here are the basics...

       

      1.  I have a database comprised of individual items that need to be billed to a variety of companies.  Some companies would have one item and some company might have three or four items.

       

      2.  I would like to have a script that finds all of the items in a date range (i.e. 1/1/2010...1/31/2010).

       

      3.  I would ten like to sort the times by customer (i.e. Company One; Company Two; Company Three).

       

      4.  I want to enter in a field the starting invoice number for that particular billing cycle (i.e. IN10-8643).  Each month would start with a different number because we might invoice outside of our billing cycle.

       

      5.  I then want Filemaker to assign a sequential invoice number, starting with the number I entered) to Company One's invoice (containing one or more items) and print then assign the next sequential number to Company Two and print and then go to Company Three and assign the next sequential number and print.

       

      Impossible?  Any help or suggestions would be GREATLY appreciated!

       

      David 

       

        • 1. Re: This is gonna be a doosey - Sequential Invoice Numbers by Company
          philmodjunk
            

          Is this for files shared over a network? If you might have two users doing this simultaneously, life get's complicated. If not, it's a lot easier to do.

           

          Item to keep in mind: This invoice number should be used purely as a label for identifying and finding invoices. Use a hidden serial number field to link to related tables that doesn't contain any such added meaning.

          • 2. Re: This is gonna be a doosey - Sequential Invoice Numbers by Company
            dwhylanjr
               We do run FilemakerPro 10 over a network.  But only one person will be doing this task at a time.  Got any ideas?
            • 3. Re: This is gonna be a doosey - Sequential Invoice Numbers by Company
              dwhylanjr
                

              I want to add something just for clarification to this challenge...

               

              Each item has a unique record number and each company has a unique record number.

              • 4. Re: This is gonna be a doosey - Sequential Invoice Numbers by Company
                philmodjunk
                  

                dwhylanjr wrote:

                1.  I have a database comprised of individual items that need to be billed to a variety of companies.  Some companies would have one item and some company might have three or four items.

                The standard approach here is to have two tables: an Invoices table where one record = 1 sales transaction and a related line items table where 1 record = one item sold. You can set up an invoice layout that includes a portal for logging the sale of each item sold under that invoice. You can also create a summary report based on the line items table for printing an invoice or for other reporting. There are numerous invoicing threads in this forum. If you click the advanced search link above and search under the key word "Invoice", you'll find a number of examples.

                 

                Here's a link to a simple tutorial on setting up summary reports that you may find useful. It uses greatly simplified invoice and line items tables in its example:

                Creating Filemaker Pro summary reports--Tutorial

                 

                2.  I would like to have a script that finds all of the items in a date range (i.e. 1/1/2010...1/31/2010).

                You can script a find for a specified date range, for a specific month (US date settings only) or you can use a special cMonth calculation field to mark all records dated with a date from the same month with a date that you can use for searches or sorts.

                Example of a scripted search using a date range:

                Enter Find Mode[]

                Set Field [YourTable::YourDateField; gDate1Field & "..." & gDate2Field] // gDate1Field and gDate2Field must be defined as global fields for this to work.

                 

                Example of searching for a specific month (US date settings only):

                Enter FInd Mode[]

                Set Field [YourTable::YourDateField; "5/2010"] //Finds all records in 5/1/2010...5/31/2010 range, You ca store month and year values in variables or global fields and use them in an expression here also.

                 

                A cMonth calculation might look like this:

                YourDateField - Day(YourDateField) + 1

                 

                It will compute a date for the first day of the month for all dates in YourDateField.

                 

                3.  I would ten like to sort the times by customer (i.e. Company One; Company Two; Company Three).

                 

                4.  I want to enter in a field the starting invoice number for that particular billing cycle (i.e. IN10-8643).  Each month would start with a different number because we might invoice outside of our billing cycle.

                 

                5.  I then want Filemaker to assign a sequential invoice number, starting with the number I entered) to Company One's invoice (containing one or more items) and print then assign the next sequential number to Company Two and print and then go to Company Three and assign the next sequential number and print.

                Add an auto-entered serial number field to your Invoice table for your invoice numbers. Also define a global field, (I'll call it gNextInvoiceNumber) and place it on a layout where you can enter a new next invoice number string in this field.

                In your invoicing script, use Set Next Serial Value[Invoices::InvoiceNumber; gNextInvoiceNumber] to update the serial number setting before it starts to generate new invoice records.