What you need is a related table where you store the species name in one field and the species code in another field.
This is actually so simple you will be hitting yourself.
Then you need to create a relationship between the species field in this table and the species name in the "Species" table.
When you select a species from the dropdown the Species Number field from the "Species" table can be put on the layout and it will display the species number from the related species record.
The source for your species dropdown list should be the Species Name field in your species table.
Like in this example:
However with this technique you can run into trouble if you ever mistype a species name. It's better to use the Unique Serial number ID in the relationship like so:
Do keep in mind you will need to use a little trick to create a nice dropdown:
I made you two example layouts in that second file and the second layout might really be the best way to go.
There you have the ability to create new species directly from within the layout and you have the ability to add as many as you like.
This is the most flexible way to go.
If you don't want your users to mess around with the species codes you can just restrict the adding of species to the species table itself and set the species code field to not allow field entry in browse mode.
That way you can't edit the species code field.
Set up a related table of species names and codes. Selecting the species name from a value list set up with the species code as field 1 and the species name as field 2 will allow the user to select a species by name and yet the value list enters the code. You can then add the name field from this related table to automatically display the name in addition to the code entered by selecting that name from the value list.
And if your list of species is long enough that such a value list is not as easy to work with as you'd like, you might consider this approach:
FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7
This demo works with a contact name and contact ID, but that's just a case of renaming thsoe two fields. WIth this demo file, the name drop down is auto-complete enabled and scripting is included to handle duplicate names. WIth your value list, you aren't likely to have to deal with duplicate names, but the ability to auto-complete on the value list may help if your list of names is fairly long.
Thanks, guys, I'll try this stuff out tomorrow when I get back to work. I appreciate the help :)
Okay, I got this working - DaSaint's method worked best for me, thanks a ton :)
Now I have a new question, if you don't mind me just adding on to this post instead of making a whole new one:
I added a new field called "ROWID" which acts sort of like a serial number for each individual record (it'll be the row's unique identifier once the data from this form is exported into an Excel sheet). Currently, I have the "Auto-Enter" set to increment the number by one each time a new record is created, which is perfectly fine and straighforward.
The thing I run into is if I delete a record, that number doesn't go back down. So if records 1 through 5 had the respective ROWIDs 1-5, and I delete record 5, then create a new record, NOW record 5 has ROWID 6.
How can I make it assign a serial (or, ROWID) which will correspond to its actual record number?
EDIT: Nevermind, I figured it out - Get (RecordNumber) was what I wanted :)
Get ( RecordNumber) might easily be the wrong option here. This function returns the position of a record in the current found set. Perform a find, or sort the records into a different order and the value returned by Get ( RecordNumber ) changes.
That's okay, though, because the plan is to have all the records spit out into an Excel sheet once the field people are done entering them. To be honest, it wasn't completely crucial that I solve the ROWID problem - its entire purpose was really for Excel. Really, I just needed the field to exist, and if the numbers get jumbled when I sort the data later, it's not that big of a deal - I can always just clear the column in Excel once it's sorted how I want, and then re-enter the count from the top - it'd take like five seconds to fix.
Having said that, if you have an idea for how I can do it properly, and not make that little bit of extra work for myself later, I'm all ears :)