4 Replies Latest reply on Feb 6, 2012 1:56 PM by philmodjunk

    Serial numbers


      Serial numbers


      Just a brief overview of my invoice solution (not quite a solution yet though!)


      I have the usual customers,Line items, invoice and products tables.


      However,  I need to have certain serial numbers to be automatically created once certain stages of the process has been met.


      This is the existing process of how an order is placed by the user and what should happen behind the scenes with filemaker..

      On customer details entry layout (Layout - 'Customers detail', table 'Customersl')

      User clicks on 'quote' button to write up a quote - this then takes user from the 'customer' layout to the 'invoice' layout. This layout gets info from the invoice table but also customer details from the linked 'customers' table and 'line items' that are linked to the 'products' table. A quote date and number should now be created automatically. On this 'invoice' layout, there is an 'order' button to convert this quote to an order. When this is clicked, the quote number should be removed  (maybe a conditional format to change text to same colour as background)and a Job number should now be created. This should follow on from the last job number. There is also an 'invoice' button on this page to convert the order to an invoice. The invoice date and number is created and the invoice is locked from editing via the privilage sets. 


      If, on the original customer page, the 'order' button is clicked, then the quote numbers part is bypassed and a job number is created instead and we go from there. But this job number has to follow on from the last job. 


      I currently have job number, invoice number and quote number all in the same invoice table - this doesnt help if I want them to only be created at different times. should I have a quote table, invoice table and a job table all linked with the ogirinal foriegn keys from the invoice table that links to the line items table and product table?

      All the job numbers, quot enumbers and invoice numbers need to be sequential.

      Sorry if this isnt clear - it is quite hard to describe what I want to do and what I have currently...

      Previously Phil advised me how to hide buttons etc, but I think on reflection I shall have to use multiple layouts instead which is how I noticed this glitch!

        • 1. Re: Serial numbers

          I would assume that this is a multi-user system where you have more than one user creating records and changing their status from quote to order to invoice.

          I will also assume that if you go directly to "order" without issuing a quote to the customer that no quote number should be issued.

          Define one more serial number in your invoice table. I'll call it "PrimaryKey" and refer to it as Invoices::PrimaryKey

          Now add three additional tables to your system with just two fields defined in each: QuoteNumbers, OrderNumbers, InvoiceNumbers. I'll use QuoteNumbers in this example and leave it to you to figure out how to use the same method for the other new number series.

          Set up this relationship:

          Invoices::PrimaryKey = QuoteNumbers::ForeignKey

          and enable "allow creation...." for QuoteNumbers

          Add auto-entered serial number field, QuoteNumber to the QuoteNumbers table and set the next serial value to the next QuoteNumber you want to assign to an invoice record.

          Now, this one line script: Set Field [QuoteNumbers::ForeignKey ; Invoices::PrimaryKey]

          will create a new related quoteNumber record if one does not already exist.

          You can put the QuoteNumbers::QuoteNumber field on your Invoices layout to show the assigned QuoteNumber (if it exists) for that record.

          This method ensures that even if you have a group of users all creating new records and changing their status, that each record will get the next number in the series with no duplicates.

          • 2. Re: Serial numbers

            Hi Phil 

            Yup, your assumptions look pretty spot on..I had done most of what you suggesrted but got a bit bogged down  with the "

            Invoices::PrimaryKey = QuoteNumbers::ForeignKey" etc etc part! WIll test it out, Thank you again!

            • 3. Re: Serial numbers

              Hey Phil..

              Just to clarify a few thinbgs if you have a spare moment...

              Each of the new tables should have two fields in each, A foreign key, and a numbers field (eg Quote numbers).?

              So relationship I set up is Invoices master key=quote foriegn key, with the quote foregin key side of the equation set to allow new records.


              The quote number field is set up to autoenter etc...


              The one line script - this is added to the script for creating a new quote...?


              thank you

              • 4. Re: Serial numbers

                Yes. The three number fieilds would all be defined in separate tables and defined as auto-entered serial number fields.