If you have a two column value list and you want the second column to be visible instead of the first, Set the value list ot show only the second column and format your field as a Pop-up Menu instead of a drop down list.
Thank you for your post.
I understand the portal is based upon the "Procedures" table, but I don't know the name of the first table. In addition, what is the key field(s) that links the two tables together?
It sounds like you are able to add records to the portal.
It sounds like your ID in Procedures performs a lookup into a third table. Correct?
One of your drop downs displays a calculation that puts the "ID" and "Name" together. Correct? When you select this calculation, you want to display another value in the field. Correct? This can be accomplished by a Value List. Pull down the File menu and select "Manage -> Value Lists...". Create a new Value List and name it "IDName". Select the option "Use values from field", and you will be prompted for a field. Select the field that you want the field to eventually show, and on the right side, check the box "Also display values from second field". Here, you select the calculation field. Below that, check the option "Show values only from second field". Then, select if you want to sort based upon the first or second values.
Now, you can apply this to the field in the portal, so when you select REAG001 from the list, Reagent 1 will display in the field.
Unfortunately, I don't have all the information to provide a complete answer, but this should get you pointed in the right direction.
Thanks for the suggestions, but I probably didn't make myself clear enough. The name of the first table is "Experiments" so each Experiment has a list of Procedures. The two tables are linked by the Experiment ID. So each Procedure points to an Experiment.
Yes I want to look up a third table, but not in the drop down menu. In the drop down menu I want the ID+Name, and then I want the field to display the Name after I pick from the list. Right now I can only get it to display the ID. So for example, the drop down menu has a list of "REAG001 ReagentName" and when I pick one, the field displays REAG001. However, I want it to connect to the Reagent Table (each Procedure has a list of ID's as fields, so it has a Reagent ID, then wouldn't I be able to use that ID to go into the Reagent table and extract its name?). Sometimes when I try this, the Reagent Name field changes to REAG001.
Why is that? And I don't think picking "show values only from second field" is right, because I want both fields to be shown in the drop down, but when I pick one I only want the name.
While the value lists are good for 90% of the requirements, they're definitely lacking severly in the last 10%. Remember, the value list can display 2 items, but will only enter 1 in the field. And it's always the primary field you specify. So, to end up with the 'Name' in the field, define your value list as 'Name + ID', or create a calculation field in the Procedure (?) table, = [Reagent ID & " " & Reagent Name], and define your value list to use that calculation field as the secondary & display field, with Name being the first.
So what you're saying is that there is no way for me to have the ID first (ID + Name) in the value list, and then display the second field? If I want to display Name then I have to use it as the first field?
Correct. But, the workaround would give you the same solution. Display the 'ID & Name' calculated field in the value list, but use 'Name' as the primary field.
Can you be more specific about how I would go about doing this? Right now I'm using the "Reagent ID" field with the drop down ID+Name. Would I need to change the ID field to the calculation? But then I still need an ID field as a key for other relationships.
Why can't I just use the ID to pull the name from the Reagent table? (it doesn't work)
In the table the value list is based on, where Reagent ID lives, create another field (ct_reagent_id_name), a calculation that = [Reagent ID & " " & Name]. In the value list, use 'Name' as the primary field, and ct_reagent_id_name as the secondary. Check the box to only display values from the second field.
This assumes that you have a table setup with 1 record per Reagent ID and Reagent Name, and no duplicates in the Reagent ID field.
Great I think it's working! But just to make sure, after I select it, I still set the Procedure's Reagent ID to the selected one right, even though it only shows the name? I'm trying to check that in the Procedure table (displaying Reagent ID), but I see a ? instead. Sometimes I see the ? when the field is too short to display the name, but I extended it and it's still a ?.
Also, is there some way to never have the ? appear and have a shortened version of the name?
Ok so that worked but I still need the actual Reagent ID serial # (key) to use in other tables. Why is it still displaying it with Reagent Name instead of Reagent ID?
you want two fields -- one to store the ID number after you select it, the other field to display name after the relation is formed. to save space you can place one field on top of the other, make the name field 'on top' but not enterable in browse mode, you click past this field into the id field (which should be displaying the foreign id - not the primary in the wrong table), then the drop down or pop up (tied to the id field) will allow you to make your choice, thereafter, with the id field set to the same color as the ground, you'll see only the name field (via the relationship).. that's a trick you'll need to remember...