Value List - second values and relationships

[chikanyc]

Hi everyone,

I'm having trouble using Value Lists when a relationship between two tables is by UID, but I want the the Value List to be populated by a non UID.

I have two main tables:

(1) Users

- each user has a pk_UserID field (permanent)

- each user has a Fullname field (which can be edited later on for spelling etc.)

(2) Jobs

- each job has a pk_JobID

- a job can involve multiple contacts (not sure if this is relevant in presenting my issue)

In my Job layout, there is a portal to a different table where it creates a new record joining the Job with a User. (fk_UserID, f_Fullname, and fk_JobID) I

I want to be able to select the f_Fullname from the Fullname field in the Users Table. But the relationship between the Users and Jobs and my Line Items table is based on UserID b/c an ID # is stable. For the value list, I chose User ID as my first value list and Fullname as my second value list and then selected to ONLY DISPLAY SECOND VALUES. This works fine but when I select a Fullname, what appears for my value after all is said and done is the UserID. Is there a way for that field to display the second value?

Thank you so much...been trying to do this for two days now and getting nowhere.

[mz123]

There are a few ways to deal with this:

1. Use a Pop-Up menu instead of a drop-down

2. Overlap two fields so that the bottom field is what you have now, the field that requires the UID. The top field will be the related field "User Name" from your Users table. The bottom field can only be entered in Browse Mode and the top field can only be entered in Find Mode.

[chikanyc]

Thanks for the quick response Martha! I tried it with a Pop up menu and it works great. But is there a way I can have the field so it Auto-Completes Using the Value List?

Hmmm...with so many users it may get tedious to scroll thru..

[mz123]

Try the overlaying fields. That allows you to type the first few letters and get to the name you're looking for.

The other option would be a pop-up layout that comes up that shows the list of all the users, and then the user can search in that popup (the layout will probably be from the Users Table) and choose the right record, by clicking on some icon and the nthe script will take that user ID and put it in the record that you're on, in the back window.

Thoughts?

[chikanyc]

thanks so much for your help. in the end, i decided to overlap the two fields as you had initially suggested:)