4 Replies Latest reply on Jul 29, 2010 3:38 PM by philmodjunk

    New to fileMaker and need some input



      New to fileMaker and need some input


      I am a yarn rep so i visti lots of shops and sell lots of product.

      so one company i sell for could have 15 different lines of yarn plus in those lines of yarn multiple colors.

      so I was thinking i create a database for each company i rep for and than have a table for each of the yarns with the colors in the table.  than these tables are joined back to the main table/database of the yarn company.

      I know i create invoices but i am not seeing the big picture of how it all works.

      i go into a shop and type in there information and than how do i bring in the tables for each of the companies.  I will need an invoice for each of the companies the shop purchases from.

      i was thinking this was a one to many if you think of it the way that you are in one shop.

        • 1. Re: New to fileMaker and need some input

          I'd structure tables like this:


          Were ---< stands for one to many. You have many lines for a given company, and many colors for each yarn line.

          You can, if you want, list the YarnLines in a portal on a Companies layout and be able to view colors records for a given line by clicking a button in the portal of yarn lines. You'd need to design a script to show the colors and it could either switch to a different layout of color information, show the colors in a second portal on the same layout or pop it up in a small floating window...

          Come to think of it, you could probably create a portal to Colors on your company layout and include fields from YarnLines to get a table of colors that includes YarnLine information.

          With that structure, you could design a summary report based on the Colors table that lists Yarn Line and Company information in subsummary layout parts that serve as subheadings over groups of yarn colors from the same YarnLine and from the same company.

          I realize I just threw a lot of ideas at you in one post and you're new to the subject. Feel free to select an idea that appeals to you and respond back with more questions and I'll then elaborate in my responses.

          • 2. Re: New to fileMaker and need some input

            What are portals? Do i use the already made invoice layout?  So i create a company database and than tables for the yarns and a table for the colors that link to the yarn

            • 3. Re: New to fileMaker and need some input

              if this was db2 i would create a company table with the company names as the key.  than a table with the company key on the yarn table with the yarn table having a foreign key, than the colors would have both the primary key and foreign key an the colors listed.

              i guess first things firs build the company tables and worry about the invoice later

              • 4. Re: New to fileMaker and need some input

                You've got the basic idea. Portals are a way to open a small window from a parent record that displays one to many related records in a child table. You can read more about it in the filemaker help system. It's a very useful tool in filemaker.

                I wouldn't base my relationship on the company name in filemaker or any other database system. I'd use a companyID code instead as companies do change their names from time to time and you don't want a name change to "break" the link between a company record and any related records in other tables. The best primary key to use in filemaker is a number field set up to auto-enter a serial number. This gives you a simple, unique identifier for each record in the table.

                If you look at filemaker's starter solutions, you'll find one for invoices and another for Inventory management. You may want to create a copy of those and look them over to see how they are set up.