5 Replies Latest reply on Dec 5, 2013 2:10 PM by philmodjunk

    simple related tables


      simple related tables


           I'm just learning about relating tables and trying to do a very simple prep list to track what we make each day and then create reports for the week and month. here's what I have so far...

      table one, prep list items

           item id - I should probably set this up as a serial number

           item name - text field

           batch size - this would be the "normal" batch size

           batch size unit - i suppose this might need to be a value list?


      table two, prep list calendar


           item id

           item name (I'd like to be able to have this pull from item name in the prep list)

           batch size (this is going to be pulled from the prep list table. it's the usual batch size)

           batch size unit (this would be pulled from somewhere)

           actual batch size (this is something that sometimes varies and we need to track)

           notes- just a text field to put notes

           So I want to use the prep list calendar to enter a date and grab a task  from the task list, then have it enter the batch size and then some notes, etc. then i want to be able to create a report to see what was done for the week.

           I've posted the file here: https://sites.google.com/a/sugarbakerycafe.com/shared-files/filemaker-database





        • 1. Re: simple related tables

               I can't get to your file without logging in so my answers do not reflect what you have in the file. There are other file sharing options that do not require this option. I use drop box.

               item id - I should probably set this up as a serial number >> absolutely!

               item name - text field >> Select a Unique Values Validation to make sure you don't accidentally give two items the same name.

               batch size - this would be the "normal" batch size

               batch size unit - i suppose this might need to be a value list? >>Yes, you can use Manage | Value List to set up a value list of custom values for your units

          table two, prep list calendar

               date >>You might want to use the drop down calendar option for this field to make it easier to select a date

               item id >>You can format this field on your layout to be either a drop down list or pop up menu of items. The value list can be set up with the "use values from a field option to list both item IDs and Item names from the Item table.

               item name, batch size, batch size unit >>All three of these should be looked up values (auto-entered). You can even not have item name as a field in this table at all.

               actual batch size (this is something that sometimes varies and we need to track)

               notes- just a text field to put notes

               And in Manage Database Relationships, you'll want to create a relationship linking the Item ID fields of these two tables. This is needed for the auto-enter setting on the three fields in your prep list calendar table and it will also be useful in many other areas such as putting a portal to your calendar table on your Item layout. This portal could then list each date and batch quantity scheduled for that item in your Calendar table.

               You may also find this Calendar demo file of interest: https://www.dropbox.com/s/e8d03xvwe8vtz85/Calendar.fp7

               If you are using FileMaker 12 or 13, use open from the file menu to open a copy of this file converted to the .fmp12 file format.

          • 2. Re: simple related tables

                 thanks for your help.

                 I've incorporated some of your changes and I"m still just a dummy.

                 thanks for the tip on drop box. I put a file here if you want to play with it:


                 I'll download the calendar file you posted too and see if that makes sense. I really want to learn how to do this as I think it will be very useful.

            • 3. Re: simple related tables

                   Ok, but do you have a question that needs an answer?

                   I'm quite willing to help you learn how to do this for yourself, but I do not intend to just give you a finished database file--that wouldn't teach you very much. wink

              • 4. Re: simple related tables

                     when I'm entering a new record in the prep list calendar, I can't partially enter an item name to get it to pull from the prep list table. I can get it to work with item id (when i had a different relationship formed), but I don't have all the item id's memorized if that makes sense. 

                     i feel like I'm so close to unlocking the KEY ;)

                • 5. Re: simple related tables

                       What I am first going to show you is the "simple to set up beginner level" method. It won't do all that you just asked for, but it does work and serves as the basis for more sophisticated approaches that can do all that you want.

                       Open Manage | Value Lists

                       Select your value list for Items or create a new one.

                       Select the "use values from a field" option.

                       Select your Items table in the drop down and then click the ItemID field to select it.

                       But now select the "also display values from second field" option.

                       Select your Item Name field in this list of fields.

                       If you stop here and click OK, a drop down list or pop up menu will produce a list with two columns of data. The ID number in the first column and the name in the second. But we can make this a bit nicer by selecting the "show values from second field" option to hide the ID numbers from the value list. (One of the reasons that I recommended specifying Unique Values for this name field is in order to avoid problems with this value list. If you have two item records with identical names, only one appears in the value list when you show only the name field.)

                       Now, if you use this value list on your calendar table's ID field, you can select an item by name and the value list will enter the ID number. If you select Pop up menu instead of drop down list as the format, you'll always see the item name displayed in the field and you won't need an item name field in your calendar table. (And there are layout tricks that can produce much the same effect for a drop down list that also does not require a name field in the Calendar table...)

                       This will allow you to select an item by name, but you won't have an auto-complete feature--the ability to type in a few letters and get a much reduced list of item names starting with those letters...

                       That's the feature that requires a more sophisticated approach where a secondary relationship and a script can be used to supplement FileMaker's shortcomings when this type of value list is set up.