In filemaker, the key is in how you set up relationship in Manage | Database | Relationships.
Please note that I haven't had time to download and look at your file, but from the description, it sounds like you may need a portal on your "Snake" record that will list your "subsnake" records. Your patch sheet would appear to be a list of "Snake" records. Whether or not you need a portal, you'll need at least two and most likely more tables to make this work. At the very least you'll need one table where each row of your Patch sheet is a record in this table and a second table describes the information for each "subsnake".
I could be wrong here as I am unfamiliar with your industry and that can make your industry jargon a bit obscure at times.
Thanks for the reply! This seems to be exactly what I want to do. Create a relationship between the table that contains the main patch sheet and the 4 subsnake tables (red, green, blue, red). If the values of the inputsub field in the main patch table match criteria of the relationship between one of the subsnake tables, then it will create a value in said subsnake table the subsnake number and the instrument name and use a portal to display this information on the main patch sheet layout.
This all makes sense, the question is , how do I create a "conditional" relationship that will only pull information if a value starts with the subsnake color (R for red, or G for green, etc. and channel 1-12, so R1-R12, G1-G12, etc.) I know this is probably confusing as a lot of it is industry related and would make more sense for someone who is in the industry, but I really appreciate any tips or help!
What version of filemaker are you using? New to filemaker 11 is the ability to create a filter expression that limits what you see in the portal to only those related records meeting the filter expression's criteria. With older versions, you can get the same result, but need additional table occurrence boxes and relationships in the relationship graph.
I'd put color code in one field and channel in another. I can always write a calculation field to combine the the two codes for display purposes if I need to. That also allows you to use value lists for color and channel assignements to cut down on data-entry errors.
In filemaker 11, you can use a filter expression such as: Subsnake::ColorField = "R" to filter out all related records except those that are for the "red" subsnake or I can use MainTable::gColor = Subsnake::ColorField to set up a filtered portal where I can choose a color code in gColor and see only those matching records in the filter.
In older versions, (and it sometimes remains a good option for filemaker 11), you can build the condition into a relationship:
MainTable::SnakeID = SubSnake::SnakeID AND
MainTable::gColor = SubSnake::ColorField