1 2 Previous Next 15 Replies Latest reply on Jan 30, 2009 2:10 PM by TSGal

    creating a very simple invoice

    fjordseeker

      Title

      creating a very simple invoice

      Post

      i am (still) new to FM and have ben working on moving a customer DB from MS Access over to FM. basically i have gotten pretty far but have hit a wall... what has me stumped is how to create a simple invoice where the user can browse through the client records then under a tab for "print docs" click on a button called "generate invoice".

       

      all i want to have is a simple layout open where certain address fields from the currently active record autofill into respective fields on my invoice template, plus a text box that auto creates a new lines as needed if i have more than one itme on the invoice. the subtotals etc would be pushed down accordingly so that the invoice would adjust its layout if there were a lot of items.

       

      the text box for the items should be a table that provides space to manually fill in

      Qty

      Item (manual description)

      unit price (manually filled in amount)

      subtotal (calculated)

      tax (calculated)

      total (calculated) 

      Comments (where user can write in specifics to the order) 

       

      the header and footer of the layout would have the standard logo and other info.

       

      i would really appreciate a nice simple explanation or other resource. i read around the forum but nothing seems to fit a similar situation to mine, since i am not filling products in from a dbd but writing them in by hand. i played around with portals thinking this was the solution but i made more of a mess than solve my problem.

       

      many thx 

        • 1. Re: creating a very simple invoice
          mwc_1
            

          Look at the sample database that filemaker provides for purchase orders...this would get you a good idea, or maybe the complete solution.

           

          Filemaker -->File-->New Database-->Select from template(or starter solution) - show all and find the Purchase Orders.fp7 template.....

          • 2. Re: creating a very simple invoice
            TSGal

            fjordseeker:

             

            Thank you for your post.

             

            Actually, a portal would work best, so I'll create an example that should make the steps clear.

             

            A customer can have several invoices.  Several items can belong to an invoice, and several items can belong to several customers, so we have what is called a many-to-many relationship.  For simplicity, here are some sample tables:

             

            CUSTOMER

            CustomerID

            Name

            Address

             

            INVOICE LINES

            InvoiceID

            Description

            Price

            Quantity

            Extended Price (Price * Quantity)

             

            INVOICE

            CustomerID

            InvoiceID

             

            (Note: There will be additional fields, but they can't be created until we set up the relationships) 

            Subtotal = Sum (INVOICE LINES:: Extended Price)

            Tax = Round (Subtotal * .08; 2)

            Total = Subtotal + Tax

             

            ------

             

            Pull down the File menu and select "Manage -> Database...".  If I click on the "Relationships" tab, I see a graphical representation of the three tables.  Since I want to see customer information on each invoice, I want to connect the CUSTOMER table to the INVOiCE table.  This is done by clicking the "CustomerID" field in one of those tables and dragging it over and connecting to the "CustomerID" field in the other table.

             

            I also want the invoice to have several lines for items, so I connect the "InvoiceID" field from the INVOICE table to the "InvoiceID" field in the LINE ITEMS table.

             

            Since there will be only one Customer per invoice, I don't need to make any additional changes.  However, since I may want to add several parts/items to the Invoice, I need to make an additional change....

             

            Halfway on the line between the INVOICE and LINE ITEMS tables, there is an icon.  Double-click on this icon, and you will notice the left side corresponds to one of the two tables, and the right side corresponds to the other table.  In the lower half of LINE ITEMS table, put a check mark in the option "Allow creation of records in this table via this relationship".  This will allow you to add multiple products/items to the LINE ITEMS table while you are in the INVOICE layout.  Click "OK".

             

            Click on the Fields tab, and select the "INVOICE" table.  We can now add the remaining fields:

             

            Subtotal = Sum (INVOICE LINES:: Extended Price)

            Tax = Round (Subtotal * .08; 2)

            Total = Subtotal + Tax

             

            Click OK, and we can now set up the forms.

             

            Pull down the View menu and select "Layout Mode".  Switch to the layout "INVOICE".

             

            At the top of the window, there will be several icons.  If you put your cursor over these icons, after 1-2 seconds, a tooltip will appear describing each icon.  Towards the right edge, select the Portal tool.  On your layout, draw click down and draw diagonally a box.  When you let go, you will be prompted for a related table.  Select the LINE ITEMS table.  Then, it will prompt you for the fields you want displayed.  Select Item, Price, Quantity and Extended Price.

             

            At the top again, find the icon towards the right side that represents the field tool, and from the CUSTOMER table, we'll include the Name and Address.

             

            Pull down the View menu and select "Browse Mode".  Go to the CUSTOMER layout, enter a new dummy record and enter "1" for CustomerID, and the following information:

             

            Name: John Smith

            Address: 123 Main Street

             

            Now, switch back to the INVOICE layout, add a record and enter "1" into CustomerID.  You should now see information automatically appear in the Name and Address fields.

             

            In the portal, you should be able to put your cursor in the first field.  Enter information in the Quantity and Price field, and an extended price will appear.  Also, you will see your Subtotal, Tax and Total fields updated.  Add another item in the portal, and all fields should be updated appropriately.

             

            Basically, that's it!  Adding your logo and other information should be straight-forward.  If you need clarification for any of the above steps, please let me know.

             

            TSGal

            FileMaker, Inc. 

            • 3. Re: creating a very simple invoice
              fjordseeker
                

              thank you TSGal for the help. i haven't been quite able to get it working. i followed all of your steps closely but after i enter a customer ID and name & address then go to the INVOICE layout the fields for Customer ID won't let me enter any values. yes i did create a new record. all i can do on the INVOICE layout is enter data into the portal fields.

               

              assuming i figure it out, i suppose i can use your example to hook into my existing CSM db by adding only the INVOICE and the INVOICE LINE tables and link the customer names and addresses. seems to me to make a tale related all you need to do is connect at least 1 of the items in each tabel, or do i have that wrong?

               

              anyway, any more suggestions are greatly appreciated! thx

               

              fj 

              • 4. Re: creating a very simple invoice
                TSGal

                fjordseeker:

                 

                If you added a record into the portal, then the Customer ID is stored.  This field should NOT be in the portal.

                 

                Your assumption is correct.  To make a table related to another table, you need at least one "key" field that connects the tables together.

                 

                TSGal

                FileMaker, Inc. 

                • 5. Re: creating a very simple invoice
                  fjordseeker
                    

                  >>Pull down the View menu and select "Browse Mode".  Go to the CUSTOMER layout, enter a new dummy record and >>enter "1" for CustomerID, and the following information:

                   

                  >>Name: John Smith

                  >>Address: 123 Main Street

                   

                  did this... 

                   

                  >>Now, switch back to the INVOICE layout, add a record and enter "1" into CustomerID.  You should now see >>information automatically appear in the Name and Address fields.

                   

                   i did this also but here is the problem, i cannot enter the CustomerID. i cannot enter any of the fields outside of the portal (Cust ID, name and address) in the Portal i ONLY have qty, item, price, tax and subtotal as per your example.

                   

                  so I'm stuck there and don't know why at the moment.

                  • 6. Re: creating a very simple invoice
                    Campboy
                       Is your Customer Number field (on the invoice layout) from the Invoice table, or the customer table?
                    • 7. Re: creating a very simple invoice
                      fjordseeker
                         i have CustomerID on BOTH CUSTOME and INVOICE tables, and linked using the relationships tab (as described in the example)
                      • 8. Re: creating a very simple invoice
                        fjordseeker
                          

                        also, in the layout mode setting up the field in the customer layout allows me to source "CustomerID" from the table CUSTOMER or INVOICE, while the invoice layout only allows me to source the CUSTOMER table for the CustomerID field. (hope that makes sense) 

                        i tried both combinations with the same result that i am unable to enter the fields in the INVOICE in browse mode.

                        • 9. Re: creating a very simple invoice
                          TSGal

                          fjordseeker:

                           

                          Make sure you are using the Customer ID field from the INVOICE table.  That is what prompts the link to the Customer ID in the CUSTOMER table.  Does that make sense?

                           

                          TSGal

                          FileMaker, Inc. 

                          • 10. Re: creating a very simple invoice
                            Campboy
                              

                            If I understand your situation, you cannot get the customer data to populate on the invoice form when you enter the Customer ID.

                             

                            At the top of your invoice form, you should have fields (from the customer table) for Customer Name , address, etc.  The Customer ID field at the top of the invoice form will be from the Invoice table. 

                            • 11. Re: creating a very simple invoice
                              fjordseeker
                                

                              hi TSGal, when i source CustomerID from the INVOICE table in the CUSTMOER layout - which is possible - and then switch to the INVOICE layout and set up the CustID field i can only source the CUSTOMER table in the pulldown under "Display data from" ... 

                              if i do that i still cannot access any of the fields in browse mode that exit outside the portal (and also in the CUSTOMER layout) in the invoice layout

                               

                              conversely, i can also source CustID in the CUSTOMER layout from the CUSTOMER table. either way it's the same, i cannot access the fields in browse mode in the INVOICE 

                              • 12. Re: creating a very simple invoice
                                TSGal

                                fjordseeker:

                                 

                                On your INVOICE layout, go into Layout Mode (from the View menu).  Click on the field tool in the Status Area, and create a new field.  The default is showing fields from the INVOICE table.  At the top, change this from INVOICE to CUSTOMER (since this will be listed under Related tables).  Then, select the Name field and click OK.

                                 

                                TSGal

                                FileMaker, Inc. 

                                • 13. Re: creating a very simple invoice
                                  fjordseeker
                                    

                                  ah, i see what the problem was, i needed to check "Allow creation of records in this table...." in the relationships tab on BOTH sides of the invoice and customer tables. this part was not described by TSGal. 

                                   

                                  that seems to have fixed it! 

                                  • 14. Re: creating a very simple invoice
                                    fjordseeker
                                      

                                    my next question would be, do i need to use a CustomerID field when i devise an invoice layout for my existing Customer db, or how else can i autofill the data i need to fill into the invoices (addresses, names etc) without using a CustID? presently i have no customerIDs in my Customer db... 

                                     

                                    i imagined something more along the lines of browse to the customer you want in my Customer DB, then click on 'invoice' and that customer's info is autofilled into a layout for my invoice automatically, no typing of CustID into correspinding field necessary... 

                                     

                                    and how can i populate 860 records which previously had no IDs with unique id's? 

                                     

                                    thx to all for all your help on this, i feel i'm finally getting somewhere...! 

                                    1 2 Previous Next