9 Replies Latest reply on Jul 11, 2014 2:41 PM by philmodjunk

    New File Maker Pro user

    HollyGiovannetti

      Title

      New File Maker Pro user

      Post

      I am using the trail version of file maker pro 13 right now, to see if it fits our company's needs, before purchasing the software. We are a specialized wood working company. We do not have a show room on site to sell directly from our location, but we do sell through magazines, interior designers, etc, as well as people call and make orders. I have never used file maker before, and am having trouble deciding what different tables to use to set up the database, and then how to set up relationships. I have used the different supports, but am hoping you may be able to give me a clear idea.  So far, the tables I know I will need are: Products, parts inventory, & invoices. I know I need a contact table, but am unsure if I should have all the contacts together, or if I should have one for people who buy from us, and one for who we order parts from and one for who delivers orders for us. I then need to connect tables so that I can see which parts go with what products, which contact goes with which order, which product goes with which order. I am sorry if this is not the way you usually help others, but I am having trouble figuring it out from looking on line and watching tutorials. I do not have any one in my area who has used this program before and I am trying to learn it on my own.

        • 1. Re: New File Maker Pro user
          philmodjunk

               Note: FileMaker is much simpler to use than most other similar products, but the correct design of a relational database--regardless of the application used to create and use it, will be anything but simple and can require acquiring a significant amount of new knowledge/skills before you can effectively create your own database system.

               I strongly encourage you to invest time/dollars in training resources for FileMaker and relational database design. We'll be glad to offer assistance, but such help tends to be of best use when answering very specific questions. General, "I need a lot of help" posts often scare off potential assistance as such can "suck in" the helper into long time consuming posts trying to both answer questions and educate you about all things FileMaker. Thus, you may want to break down such a post into several smaller ones that focus on more tightly focused specific questions. In the mean time, your training materials can provide a more broad based foundation of knowledge and help you "learn the language" of Filemaker and Relational Database Design so as to help you ask better questions and better understand the answers offered.

               One good, free resource is: https://itunes.apple.com/us/book/filemaker-training-series/id787527886?mt=11

               And you may find this thread useful before starting a new thread in this forum should you choose to do so: Please Help Us to Help You...

               Now to provide a few of those specifics:

               A typical invoicing system will look like this:

               Contacts-----<Invoices----<InvoiceData (also called "Line Items")>-----Products

               Contacts::__pkContactID = Invoices::_fkContactID
               Invoices::__pkInvoiceID = InvoiceData::_fkInvoiceID
               Products::__pkProductID = InvoiceData::_fkProductID

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

               If you select "new from starter solution" from the File Menu, you can get a list of premade database examples including one called "invoices". It's design follows this same basic pattern but contacts are called "customers" and the fields used in the relationships are named differently. You can create such a file from that template and then examine its design as a source of ideas for how you might create your own database.

               Parts Inventory might be a table linked to Products, or Products can be set up as a table of both products produced/sold and the parts used to produce those products. Keep both options in mind as you work out the basic structure of your database.

               

                    I know I need a contact table, but am unsure if I should have all the contacts together, or if I should have one for people who buy from us, and one for who we order parts from and one for who delivers orders for us.

               99.99% of the time, it will be better to keep all contacts data in the same table. If needed, you can add a field that identifies a contact as being a member of one these groups. And with the contacts all in one table, you gain the option of having a contact be a member of more than one of these three categories.

          • 2. Re: New File Maker Pro user
            HollyGiovannetti

                 Going to try to clarify. Bear with me.

                 I need a table for contacts, one for assembled products, one for parts (inventory of pieces that go into assembled products), a table for customer orders (invoices I would think) and an order sheet so that when I need to order parts, I can link that to the "contact" we order from. As of now, my contacts table is a list of a bunch of different types (supplier, freight company, warehouse, direct customer, etc.). I am not sure if I should have "customer" in a table and all the rest in a different table. I am also unsure how to decide what should be a primary key and what should be a foreign key for each table. 

            • 3. Re: New File Maker Pro user
              philmodjunk

                   To repeat. All your contacts should be in one table. This makes for more flexible use of this data. There are a number of tools that you will learn how to use if you stick with this that will enable you to work with subsets of all the contact records such as all customers or all suppliers, etc.

                   But unless you only sell one product at a time with no exceptions, you will need a LineItems or InvoiceData table so that you can sell a customer multiple products and only need one invoice to do so.

                   

                        I need a table for contacts, one for assembled products, one for parts (inventory of pieces that go into assembled products),

                   Yet in many systems, this is actually one table, not two. Manufacturing a product reduces the parts inventory by the amounts consumed to produce it and increases the product inventory all via the same table. But how you manage inventory levels, as opposed to documenting the sale of products via invoices, is a significant database project all by itself. I suggest you hold off on inventory management until you have an invoicing system that works for you. The design of that invoicing system can then be used as the foundation for adding more components to your database in order to manage inventory.

              • 4. Re: New File Maker Pro user
                HollyGiovannetti

                     ok, I think I understand a what you've written under: A typical invoicing system looks like... and I will hold off on the inventory table for now. I like the idea of having products produced along with the parts to make that product in the same file, the problem is, I already a bunch of info in that table already. measurements for the product, the crate size for the product, If there is more then one crate, measurements for the different crates, etc.

                     my new question is, what information goes in the primary key vs. the foreign key? I mean is the information that I put in the tables the same for pkContatcsID as fkContactID. Does that make sense?

                      

                • 5. Re: New File Maker Pro user
                  bigtom

                       Have you considered starting with the FileMaker "Invoices" sample file and modifying the Layouts to work for you. I would advise this as most of the work to setup a basic inventory and invoicing database is done already.

                       I use Get (UUID) for keys. The UUID is set in the parent record(pk) and is the same in the child(fk) via the relationship. You can manually enter the key if you need to or have it auto enter depending on how the related record is created. The key value has to be the same for the records to be related.

                       It is fairly easy to move records from one table to another.

                        

                  • 6. Re: New File Maker Pro user
                    HollyGiovannetti

                         What would you use as the pk and fk for contacts? Company name? I guess the pkProductID would be the product number, would that be the fk in invoices to list out what an order is? Does the lineitem table need to have any information before what it needs to connect the invoice table and the product table?

                    • 7. Re: New File Maker Pro user
                      philmodjunk

                           While Get ( UUID ) works for a primary key, I suggest a simpler option.

                           For each _pk field, find it in manage | Database | fields, double click it to open field options and select the auto-enter tab. On that tab, select the serial number option. Do this for every __pk (primary key) field in your database. Serial numbers are easier to work with than UUIDs and there's no major reason to use them in this particular solution from what I see.

                           _fk fields should then just be defined as number field. They don't need any special options set up in field options though they are sometimes set up to auto-enter the value of a variable as a way to automatically link new child records to the current parent on another layout. But I suggest not going there until you have a basic set up working for you using a portal to the InvoiceData (or LineItems) table.

                      • 8. Re: New File Maker Pro user
                        HollyGiovannetti

                             Is setting up a portal hard? How do I decide what information goes in a portal? TY

                        • 9. Re: New File Maker Pro user
                          philmodjunk

                               Portals are the most common way to set up a layout so that from the context of a single record, you can view and edit data in a group of related records. It's very rare for me to develop a data entry layout that does not include at least one, if not many portals.

                               In the case of this invoicing example, It's very typical to put a portal to InvoiceData on the invoices layout so that you can select multiple products sold in the portal for a single invoice.

                               If you use "new from starter solution" to open an invoices template, you'll see that the desktop layout for invoices is designed in this manner.