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.
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.
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 ;)
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.