3 Replies Latest reply on Aug 2, 2014 2:00 PM by jetserkok

    How to find products in a product table through joined table



      How to find products in a product table through joined table



                How to find and select products on invoices through joined table

                     I have been searching online but can't seem to find the answer. Here it is: 

                     I have two tables, products and orders. Since 1 product can appear on multiple orders and 1 order has multiple products, I created a third table joining the two, named 'line items'. I have populated the table 'products' with some products. Now i wish to set up a lay-out for 'orders', lets say 'create new order': when i create a new order, i wish to select products from the product table How can i do that? 

                     1. Products (has primary key 'Products ID'): table that has products in it

                     2. Line items (joined table, has foreign key 'Product ID' and foreign key 'Order ID'): so this table has no data yet

                     3. Orders (has primary key 'Order ID'): table that has no data yet

                     A portal i created doesnt show anything. Please be aware that i'm trying to CREATE orders, so there are NO orders in my orders table, there are ONLY products. The point is to CREATE orders through a filemaker layout. So i'm basically trying to create a connection, while in the current situation there is none yet. On the internet i find dozens of examples like here: http://www.lynda.com/FileMaker-Pro-tutorials/Understanding-portals/83839/88089-4.html that do almost the same, but in this case, there are already orders in the system. The only thing they do is link order with products that are ordered already. 

                     Thanks for your help

                     See picture for my database.



        • 1. Re: How to find products in a product table through joined table

               I don't think you need the line items join table.  I would put a _fkProduct ID or the orders table to link to __pkProducts ID.  The __pkOrders is unique to each order-each order is a record of one customer, with multiple products.  Each customer record is one customer with multiple orders.

               So a portal on Customers can show related orders.  A portal or products could show orders related to this product, or customers who purchased this product (2 portals?). 

               When you want to create a new order, you create a new record, then populate the customer, then populate the products

          • 2. Re: How to find products in a product table through joined table

                 Sorry Steve but that line items table is needed. If you look at the Invoices starter solutions that come with FileMaker 12 and 13, these tables are called invoice data and are necessary to make up orders of multiple products that each link to the correct product record.

                 First detail: In this type of set up, one normally selects the "Allow creation of records via this relationship" for the LineItems table in the orders to lineItems relationship. This makes it possible to put a portal to Line Items on your orders layout and then be able to create new line items records by entering data into the blank "add row" that will appear in this portal.

                 Next detail: as a beginner's level start, you can then put _fkKardexID in the portal row and format it with a drop down list of values with a "use values from a field" value list. __pkKardexID would supply values for the first field and a product name or description field would be selected for field 2 so that you can use this value list to select a product by name/description, but the value list enters the ID number and links that LineItem record to a product record.

                 THis then makes two useful things possible:

                 a) data, such as a unit price can be copied from Products into a corresponding field in LineItems using auto-enter field options on that Lineitems field.

                 b) Fields, such as a name or description field, from Products can be included in the portal row to display more information about the selected product.

            • 3. Re: How to find products in a product table through joined table

                   Hey PhilModJunk, 

                   It works perfect, just as you described.