7 Replies Latest reply on Dec 8, 2008 8:03 AM by Summerrrz

    Invoice - Relationships

    Justin1

      Title

      Invoice - Relationships

      Post

      Hi,

      I'm new to filemaker and ive spent too much time trying to work out how to make my invoice layout work. I've tried google and a quick search here.

       

      So I have..

      Products Table

      Product Code

      Product name

      Price 

      Description

       

      I have set up an invoice table. Currenly i type in a Client ID and it fills in the form bellow and copies in the clients address etc. But what I want to do is have space for 10 products further down where all i need to do is enter the product code and then it fills in the product name and price. I just need to enter in the quantity.


      How do i do this part of the layout? 

        • 1. Re: Invoice - Relationships
          raybaudi
            

          Hi Justin

           

          You'll need a new table "Invoice Line Items" with at least these fields:

          LineItemID
          InvoiceID
          ProcuctID
          Product name ( Lookup from Products :: Product name )
          Price ( Lookup from Products :: Price )
          Description ( Lookup from Products :: Description )

           

          So the relationships will be:

           

          Invoice :: InvoiceID <----> Invoice Line Items :: InvoiceID
          ( with allow creation of related record in the table: Invoice Line Items checked )

           

          AND

           

          Invoice Line Items :: ProductID <----> Products :: ProductID

           

          Place a portal showing records of this new table in the layout that you use to make an invoice.



          • 2. Re: Invoice - Relationships
            Justin1
               That all sounds like what i need to do. I cant get the portal working.
            • 3. Re: Invoice - Relationships
              Nadula
                 Can you explain how it's not working?
              • 4. Re: Invoice - Relationships
                TSGal

                Justin:

                 

                I assumed you would have responded by now.  Is it still not working?

                 

                First, go into "Manage -> Database" (File menu), and make sure your relationships are set up properly between the three tables.

                 

                Then, go into Layout Mode, click on the tool just below the oval tool on the left side of the screen, and draw your portal on the Layout.  Select the table you want displayed, and then select the fields you want displayed.  Add the Product ID to the portal from the current table.  Return to Browse mode, and enter a Product ID.  This should display the rest of the product information.

                 

                Let me know if you are still having difficulty.

                 

                TSGal

                FileMaker, Inc. 

                 

                 

                • 5. Re: Invoice - Relationships
                  Justin1
                    

                  I am still having difficulty.

                   

                  Just to go through things I have 3 Layouts and 3 Tables:

                   

                  • Invoices
                  • Products
                  • Clients
                   
                  On each table and layout I have the following fields.
                  Clients = Client ID, Name, Address, Address 2, City, Post Code, Phone, Mobile, and Email.
                  Products = Product Code, Product, Description, and Price
                  Invoices =  Client ID, Invoice No., Name (Auto), Address (Auto), Address2 (Auto), City (Auto), Post Code (Auto). - Think works for me. I type in the client ID and it display their address info.
                  Below that I want to be able to type in the Product Code and the Quantity and it gives me to price.
                   
                  I have set up the following values on this table: Product Code, Product, Quantity, Price, Subtotal, Tax and Total.
                   
                  I have set a relationship between:
                  Client ID:Clients = Client ID:Invoices
                  Product Code:Products = Product Code:Invoices
                   
                  And looksup for the fields: Name, Address, Address2, City, Post Code, Price, Product.
                   
                  But I still cant seem to get it going.
                   
                  It would also be good to have a user friendly Invoice Layout and a Printer Friendly Layout. I have worked out how to make a print button on the User Friendly one that runs a script to open the Print version, prints it, then close it. Although there is no information on that yet. 

                   

                   

                  • 6. Re: Invoice - Relationships
                    TSGal

                    Justin:

                     

                    Without trying to determine the exact cause, here is a post I answered a couple of weeks ago.  You can find the post at:

                     

                    http://fm.lithium.com/fm/board/message?board.id=FM-en-4&message.id=1355 

                     

                    ========================================= 

                     

                    Here is a simple database file that may give you some ideas.

                     

                    Create a table (which you may already have) INVENTORY with the following fields:

                     

                    PRODUCT ID (Text)

                    Description (Text)

                    Price (Number)

                     

                    Notice that PRODUCT ID is uppercase.  This will be a "key" field when relating to another table.

                     

                    Enter the following data:

                     

                    PRODUCT ID - Description - Price

                    1 - Product 1 - 1.00

                    2 - Product 2 - 2.00

                    3 - Product 3 - 3.00

                     

                    ------

                     

                    Next, create the table ORDERLINE with the following fields:

                     

                    ORDER ID (Text) 

                    PRODUCT ID (Text)

                    Quantity (Number)

                     

                    Switch to the Relationships tab, and attach ORDERLINE table to the INVENTORY table via PRODUCT ID fields.  When the relationship is created, double-click on the icon connecting the two tables and be sure to check "Allow creation of records in this table via this relationship" on both sides (left and right).

                     

                    Switch back to Fields tab, and create one additional field in ORDERLINE:

                     

                    Extended Price (Calculation: Number) =  Quantity * INVENTORY:: Price

                     

                    Do not enter any data.

                     

                    --------------------

                     

                    Create a table ORDER with the following fields:

                     

                    ORDER ID (Text)

                    Name (Text)

                     

                    (Note: The Name field would actually be a link into your Customer database, but for this example, let's leave it as a text field)

                     

                    Click the Relationships tab and connect the ORDER table to the ORDERLINE table using the ORDER ID field in both tables.  Again, double-click the icon connecting the two tables and check the options "Allow creation of records in this table via this relationship" on both sides (left and right).

                     

                    -------------------

                     

                    Exit out of Manage Database and go into Layout Mode (View menu).  Select the layout for ORDER, and on the left side of the screen, click on the portal tool (just below the oval tool).  Draw a fairly wide box with at least four lines.  You will then be prompted for the table. Select ORDERLINE, and click OK.  You are then prompted for fields.  Select PRODUCT ID from ORDERLINE, select Description from INVENTORY, select Quantity from ORDERLINE, Price from INVENTORY, and Extended Price from ORDERLINE.  Click OK.

                     

                    Go to Browse mode, and create a new record.  Enter a name "Kat4", and in the portal, enter "1" into the first field.  This looks up the information in INVENTORY, and the Description and Price will appear.  Enter a quantity of 2, and in the Extended Price, 2 will appear (2 * Price).  (You can always format the field to show currency).  In the next line, enter "2" into the PRODUCT ID, and the description and price appears.  Enter a quantity of 3, and Extended Price should display 6.

                     

                    Add a field to "ORDER":

                     

                    Total (Calculation: Number) = Sum (ORDERLINE::Extended Price)

                     

                    You can then display the total of the order before discounts and taxes.

                     

                    -----------

                     

                    This should hopefully give you a good starting point and understanding.  If you have any questions or want clarification, please contact me.

                     

                    TSGal

                    FileMaker, Inc.  

                    • 7. Re: Invoice - Relationships
                      Summerrrz
                         Thank you for this!  I was completely stuck, and you helped me figure out a lot based on this example.  :smileyvery-happy: