a value list based on FKeys that displays a name

[dgroyal]

Hello.

I'm relatively new to FM, and am in the midst of redesigning a database for a school. It was set up based on names, and I am trying to get it set up around ID Keys instead.

One problem that I am having is that, from a data entry point of view, people want to see names. Take, for example, our student agents. Some of our students are referred by agents, and we want to keep track of which student was referred by which agent. Students and Agents are both separate tables. I linked them by creating an Agent ID FKey in the Student table. I then set up the field to be entered by selecting from a drop down value list. The list came from Agent:Agent ID PKey but displayed Agent:Agent Name.

The people who are entering this information, though, do not like the fact that after they enter it, it shows a number. This makes it harder for them to confirm that they have entered the right data.

Is there anyway to set up the value list / field so that they see names in the drop down list, are really selecting a number, but the field displays a name? I tried overlapping the ID Key field with the name field and making the name field non-editable in browse mode, but that didn't display the name until they had clicked off of the ID field, which is OK but still not ideal.

Thanks for your help! Please feel free to ask any questions, and I apologize if anything I said was not clear.

Dave

[aldipalo]

Take a look at this example. Perhaps it will help you.

You also want to set your value list to show 2 fields. The ID field and the Name field.

Show_only_Company_Name.zip

[dgroyal]

Thanks a lot! I've got to go teach pretty soon, but it looks like this will do exactly what I want.

[comment]

I tried overlapping the ID Key field with the name field and making the name field non-editable in browse mode, but that didn't display the name until they had clicked off of the ID field, which is OK but still not ideal.

That seems strange. Once they select a value, the focus should move to the next field in the tab order. If there's no such field, the drop down should drop again.

Does the attached work for you?

drop_and_move.fp7.zip

[Raybaudi]

... and so you'll never see the ID.

mod.zip

[comment]

... and you won't see duplicate names either...

[Raybaudi]

Neither with your way !

[comment]

Hm.

[Raybaudi]

The attached .png is true ONLY if values are sorted by ID...

But I do NOT think that the user will be happy to choose a text value based on an arbitrary order ( not alphabetical ).

[comment]

My point is that 'Show values only from second field' is not suitable where the second field can have duplicate values. Certainly, with student names that's a very real possibility.

Users being happy or not has very little to do with this. Ultimately, drop-downs are pop-ups are very limited, and for anything more sophisticated one must move to a scripted choice from a list or a portal.

[dgroyal]

I got it to work, thanks again!

[dgroyal]

Wow, thanks everyone for all of the responses. I used the first response (aldipalo's) and made a tiny field for the ID Fkey and grouped it with a regular sized field for the Agent Name. I made the group into a button, and had the button run a script that said "go to field ID Fkey".

The ID Fkey field was set up to come from a drop down value list of the ID PKey from the Agent table, with the second value of the Agent Name from the Agent table. And I had it show only the second field, so that the name would be presented alphabetically.

You're right that there is a risk of duplicate names using this method, but because these are the names of Agents (really, they are the names of Agencies) there isn't a high possibility of this. If we have two Agencies with the same name in the future, we will have to give them slightly different names.

Thanks again for all your help! I'll take a look at some of the other solutions tomorrow, as I'm sure I can learn a lot from them.

Take care,

Dave

[aldipalo]

If that does occur you can make a calculated field and concatenate 2 or more fields together that would set them apart. I.E., if they were from different cities then you would have:

Namefield &"-"&cityfield

Now you are keeping the integrity of your data but differentiating the data in the value list.

[comment]

I was trying to say (in my first response) that there's no need for a button.

[dgroyal]

Thanks, Comment.

I really like this solution. This is closer to what I had been trying to do initially. I'm still not sure why my first attempt didn't work.

I also like this solution better because of how the field can be deleted -- deleting the grouped button way doesn't seem like it deleting anything until you click off of the field.

Again, thanks everyone for all your help!

[eviwardonkey]

Wow, thanks everyone for all of the responses. I used the first response (aldipalo's) and made a tiny field for the ID Fkey and grouped it with a regular sized field for the Agent Name. I made the group into a button, and had the button run a script that said "go to field ID Fkey".

The ID Fkey field was set up to come from a drop down value list of the ID PKey from the Agent table, with the second value of the Agent Name from the Agent table. And I had it show only the second field, so that the name would be presented alphabetically.

You're right that there is a risk of duplicate names using this method, but because these are the names of Agents (really, they are the names of Agencies) there isn't a high possibility of this. If we have two Agencies with the same name in the future, we will have to give them slightly different names.

Thanks again for all your help! I'll take a look at some of the other solutions tomorrow, as I'm sure I can learn a lot from them.

Take care,

Dave

I am very much new to FMP, and I am trying to follow this. Is there any more step by step guide to setting this sort of relation up?

Specifically I am having problem with this little table idea...

Edited November 10, 2008 by Guest