6 Replies Latest reply on Jan 24, 2012 9:14 AM by OleThronborg

    Why a Join or Item list Table?



      Why a Join or Item list Table?



      I have done 2 files. One CUSTOMER and one ITEM. 

      I want every month no: 1 to automaticly send a Email report with an attatches PDF file to each Customer including the Item they ordered. The Items list can be between 1 up to 400 items.

      I have the Print layout in the Item table. and the CUSTOMER fiels related in the Header. But how do i performa a Email with the PDF file for every Customer?

      I really dont understand why i sometime ned a Join or Item list table? Where to find any info about how and why to use it?



        • 1. Re: Why a Join or Item list Table?

          Basically you want to use a join table whenever you need a many to many relationship.  It is much more efficient than listing each item a customer orders in that customer's record.  It also allows much easier manipulation of data in cases such as yours where you want a monthly report of current orders, since you can go to a layout based on customer and then just list their orders that are current.

          • 2. Re: Why a Join or Item list Table?

            Consider the basic "backbone" used in most invoicing systems:


            A single record in customers represents one customer that has placed an order with your business.

            A single record in Orders represents one order placed by one customer.

            A single record in LineItems represents one of possibly many items purchased on a single order.

            A Single record in Products represents one spcific item that you offer for sale to your customers.

            An order can list many products and a product can be ordered many times--producing a many to many relationship and thus LineItems functions as a "join" table between the two. While it's possible to use multiple fields or repeating fields in the orders record, either approach produces very complex and difficult links to products as you now have to link multiple fields in Orders to Products, in separate relationships--just to give one example on why Join tables are used in most many to many relationships.

            • 3. Re: Why a Join or Item list Table?

              Thank you very much for the clear answer.


              BUT. You can do this with only 2 Tables in FM. I dont understand why you can do a many to many relation in FM without using this teqnique?

              Is it not working or will ther be printing problem or is it a performance issue?


              About my other problem with sending many PDF files. Is the best way to make a Script that start in the Customers Table.

              a. Sort the Customer by ID and go to Post 1. Copy the Customer ID.

              b. Return to the Item list and perform another script that search the Items related to that Customer.

              c. Make the Email and PDF file and send.

              d. Loop this until i got to the last Customer.

              This is the only way i can think about, are there a better way?


              Best regards and thank you for the fast reply



              • 4. Re: Why a Join or Item list Table?

                I think the best way would be to start from your customer table, then go to related records, show only related records a Items layout with a list view.  You can then print out that list to PDF and attach it to the email.

                • 5. Re: Why a Join or Item list Table?

                  BUT. You can do this with only 2 Tables in FM

                  And exactly how would you do that with only two tables? I've done an invoicing system without using a related lineitems table and it was a real nightmare. You have to use either repeating fields or a set of individual fields so that the invoice can list multiple items purchased. Then, looking up prices and other data from the products table becomes a real mess. A repeating field of product ID's can't be used to look up unit prices in a corresponding repeating field of unit prices. You find that you need a separate Product ID field for each row of items listed in the invoice. This then requires separate parallel relationships for each product ID field to the matching Product ID field in Products:

                  Invoices::ProductID1 = ProductsTO1::ProductID

                  Invoices::ProductID2 = ProductsTO2::ProductID

                  Invoices::ProductID3 = ProductsTO3::ProdcutID

                  and so forth.

                  Imagine setting that up for a 15 item invoice. Then imagine the client saying "15 items to an invoice is not enough, can you add five more?"

                  With the lineitems join table, you can have any number of items for a given invoice that you want (and you can easily print multipage invoices listing them). All with a single relationship matching productID in line items to productID in Products.

                  That's just one issue, there are many more.

                  Try creating a 'sales report' listing how many items of each Product ID were purchased from all invoices created in a given date interval. How woule you do that if each invoice table used separate or repeating fields to list the items sold? With a separate line items table, a layout based on line items can easily be set up as this sales report since each item purchased is now a separate record.

                  • 6. Re: Why a Join or Item list Table?

                    You are my hero!

                    I dont understand my selv, why i thought is should be so complicated. This is of cource the way to go.


                    Thank you very very much.