9 Replies Latest reply on Jun 14, 2011 1:24 PM by AndrewKirkby

    Calculating an invoice number

    AndrewKirkby

      Title

      Calculating an invoice number

      Post

      Hi all, I'm hoping you can help me.

      I have a hire database which generates quotes, invoices, delivery notes etc.

      What I want to do is generate a serial invoice number for each quote that turns into an invoice.

      Ive set up the database with a JOB ID number and a seperate field containing the invoice number. Obviously the issue is that there will be some records where a quote has not turned into a job and therefore an invoice won't have been generated.

      InvoiceNo is the invoice number

      ApplyInvoiceNo is a field which has a text value of yes or no in it.

      My invoice number should be in the format INV-0001 and the next invoice generated should be INV-0002 etc.

      I've had some success in getting the database to generate a text entry when I click yes in the ApplyInvoiceNo field which takes a value list of Yes or empty using If(IsEmpty(Apply Invoice Number); ""; "TEST") which obviously will put TEST into the invoice number field.

      However, I'm not sure how to look at all of the records and generate the next invoice number based on the values already stored in the table.

      Can anyone help?

      I'm very new to filemaker and have limited programming knowledge involving if statements etc. I think a Max operation might be necessary but I'm unsure of the syntax to look at all the records and find the max value then add one to it.

      Many thanks to whoever can help!

      Andrew

        • 1. Re: Calculating an invoice number
          philmodjunk

          Will this database be hosted over a network so that multiple users might be needing to create invoice numbers at the same time? (If there is even the most remote chance that this might be necessary in the future, answer yes.)

          • 2. Re: Calculating an invoice number
            AndrewKirkby

            There's a probable chance, yes and futureproofing it will be easier now than in a year or so.

            What do you suggest? 

            • 3. Re: Calculating an invoice number
              philmodjunk

              We can use a related field with an auto-entered serial number to generate the numeric part of your invoice number. That method will not produce duplicate values if two or more users are generating invoice numbers at the exact same time.

              Define a serial number field, QuoteID in your original table if you do not already have such a field defined. (If you don't have such a field, also use Replace Field contents with the serial number option to put a serial number in this field for all existing records and make sure to select the update data entry option when you do so.)

              Define a new table, InvoiceNumbers, with two fields:

              QuoteID
              InvoiceNumber.

              Define InvoiceNumber as an auto-entered serial number field.

              Relate your current table to this new table like this:

              Quotes::QuoteID = InvoiceNumbers::QuoteID.

              Enable "Allow creation of records via this relationship" for InvoiceNumbers.

              Now, add a button on your Quotes layout that performs this simple script:

              Set Field [InvoiceNumbers::QuoteID ; Quotes::QuoteID ]

              This button will create a related record in InvoiceNumbers for the current Quote record if one does not already exist. (If one already exists, nothing gets changed.)

              Now a calculation field such as: "INV & Right ( "000" & InvoiceNumbers::InvoiceNumber ; 4 )

              can be defined in Quotes to display your invoice number.

              • 4. Re: Calculating an invoice number
                AndrewKirkby

                Thanks so much for your reply, I'm nearly there.. How do I edit the script so that I can just paste in that line? I can add Set Field [InvoiceNumbers::QuoteID] but can't get the ; in for the rest of it. 

                • 5. Re: Calculating an invoice number
                  philmodjunk

                  Click the second specify button. The upper specify button allows you to choose the target field. The lower one opens up specify calculation where you can enter a calculation for the calculated result.

                  • 6. Re: Calculating an invoice number
                    AndrewKirkby

                    Ok, when I put in InvoiceNumbers::QuoteID ; Quotes::QuoteID into the Specify Calculation it returns "An operator (e.g. +,-,*,...) is expected here."

                    So close..!

                    • 7. Re: Calculating an invoice number
                      philmodjunk

                      Click the upper specify button and select InvoiceNumbers::QuoteID from the dialog that pops up.

                      Click the lower specify button and enter Quotes::quoteID

                      Do not enter the ; at all.

                      • 8. Re: Calculating an invoice number
                        AndrewKirkby

                        Ok, so close, got the value working and relationships are ok. Just the calculation field now. Invoice Number & Right ( "000" & InvoiceNumbers::InvoiceNumber ; 4 ) is what's in the calculation field but I'm getting ? as the value implying my calculation isn't working correctly..? Any ideas?

                        • 9. Re: Calculating an invoice number
                          AndrewKirkby

                          Scrub that, got it, did the same as i did with the specify calculation.. Removed the Invoice number & bit from the start... Thanks so much for your help! You've made my tax invoicing a lot easier..