1 Reply Latest reply on Aug 29, 2014 6:16 AM by philmodjunk

    Group everything under one similar value



      Group everything under one similar value



           I'm quitte new to FileMaker and I want the following.

           I have a database containing different rows with lines of invoices. It looks like this:

           Invoice 1 | Productline 1 | Productinfo... (qty, cost..)
           Invoice 1 | Productline 2 | Productinfo...

           So if one invoice contains 100 products, it has 100 rows. Don't ask me why, it just is. Now I want to do the following: because 'Invoice 1' is the same everywhere, I want to create a form where you can look for 'Invoice 1', and it makes like a normal invoice, just with the product lines. I want to keep the normal format in the database but I want to be able to look for one general value (Invoice 1) and store there rest of the info in lines under it.

           Is this possible and if yes, how? In Excel I can do this with Pivot tables



        • 1. Re: Group everything under one similar value

               FileMaker is a relational database, not a spreadsheet program like Excel.

               I suggest that you set up related tables so that you have this basic structure typical of invoicing:

               Invoices----<Invoice Data>------Products

               Invoices::__pkInvoiceID = InvoiceData::_fkInvoiceID
               Products::__pkProductID = InoviceData::_fkProductID

               With this set up you can put a portal to InvoiceData on your Invoices layout. You have one record in invoices for each invoice and each item purchased, the individual rows of data in your current set up would be InvoiceData records listed in that portal. You'd access data about the product sold, such as the price by selecting the product in a drop down list or popup menu set up on _fkProductID.

               Note: In many cases, the InvoiceData table is named "lineItems" I've used the names found in the Invoices starter solution that comes with recent versions of FileMaker.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          Caulkins Consulting, Home of Adventures In FileMaking