6 Replies Latest reply on Dec 18, 2009 7:21 AM by one9lbhammer

    Committing invoice to order form



      Committing invoice to order form


      Filemaker 10.

      Mac OSX.


      I run a t-shirt business and am setting up a database to keep better track of my invoices.  I have all of the relationships working correctly, thus far, between customers, products, and invoices.  I am trying to create a button to commit each invoice record to a daily order form upon sale, as we do a large number of quotes from the same invoice form.  I can't for the life of me figure out how to do this.  

      I have tried sharing through a portal, and I've tried writing a script that sends the data from the portal on the invoice to a portal on the order form to no avail.  I can get the same script to work on a button from two fields on the invoice, but not between the invoice and the order form.

      I currently have a one to many relationship from order form to invoice with creation of files allowed on the order form from the invoice. 

      Any help would be much appreciated as this is one of the last things I need to figure out before I can get my boss to commit to buying Filemaker, I'm on the trial version now. 

        • 1. Re: Committing invoice to order form

          Not sure I follow what you're trying to do here so please correct me if I misunderstand.


          I read this as you make many quotes and only a few become sales and you want to post the actual sales to a different "form".


          I suggest something much simpler. Add a status field to your table and the invoice layout. Format it with a value list (can be radio buttons or Pop-up menu or...) to use the values "Quote" and "Invoice" (or "Sale" if you prefer).  When a quote becomes an actual sale, simply change the status field from "Quote" to "Invoice".


          If you need both a Quote and a Sale record (final sale could differ from original quote and you may want to document the differences), I'd consider duplicating a quote record (and any related line item records in a portal) to generate an Invoice. After duplicating, you can update the duplicated data to finalize the sale--setting the status field to "Invoice".


          Let me know if either approach works for you and any further questions you might have.

          • 2. Re: Committing invoice to order form

            I have a layout that is set up for summary reports that I use to generate a form to give to the person who orders the tshirts.  My problem is that every shirt I enter onto the invoice ends up in this report, whether or not it is an actual order.  I am trying to figure out a way to send only the shirts we need to order to the order form from the invoice form.


            Along the same lines, I have another layout for summary reporting for tax purposes.  For some reason, every line from the portal on my invoice causes a new line (same info) on the report.



            Customer 1 orders two types of tshirts. I only have the invoice total and calculation fields outside of the portal going to the report layout, but Customer 1 now has two lines containing the same info in the report.

            Customer 2 orders 4 different types of shirt.  He now has 4 lines (same info) in the report.


            Thanks for getting back so quickly as well! 

            • 3. Re: Committing invoice to order form

              Sounds like your report is functioning the way it was designed to work.


              I'm not sure I follow your examples. What do you want to see in the reports for your two examples? (You've told me what you get but not what you expect to get.)

              • 4. Re: Committing invoice to order form

                I only need one occurrence of each customer's invoice totals in my report, not a separate line showing the same exact info on several lines.  Here's what I'm getting for invoices with more than one shirt in the portal, regardless of how much each shirt is individually.  I only need the total to show up once, not once for every shirt on the invoice.


                name job    subtotal        tax        credit charge        art charge         grand total 

                Bob   Tshirt          $100      $7 $2 $20 $129

                Bob   Tshirt     $100          $7 $2 $20 $129 

                Bob   Tshirt          $100     $7 $2 $20 $129 


                It's showing the right numbers in the columns, I just can't figure out why it's showing them multiple times. 

                • 5. Re: Committing invoice to order form

                  You should point your layout at your Invoices table instead of the Line Items. You can change this by entering layout mode and taking a look at Layout Setup...


                  After such a change, you may have to double-click each field on your layout and re-select the desired table and field.


                  An alternate approach is to change the body of your layout into a "sub summary when sorted by InvoiceNumber". (Use your name for the invoice number in place of "InvoiceNumber".)


                  In Filemaker 10, this will show nicely as long as you sort your found set by the same field. In earlier versions, you'd have to preview or print the layout to see it.

                  • 6. Re: Committing invoice to order form
                       Thanks a TON! Pointing the layout to INVOICE instead of LINES fixed everything.