Display the names, but use a number

[Kevin_Ashbridge]

Hello, I'm sure there's a simple answer to this...

 

I have two tables, "Company" and "Jobs". They are linked by a relationship based on a companyID field present in each table (companyID is a unique serial number for each record in "Company").

 

On a form, I want to show a popup list of company names from the Company table and, when a name is chosen, put it's companyID into the Jobs table.

 

At present, I have placed the companyID field from Jobs on to the form, made it a Popup List-type, and assigned it a Value List based on entries in the Company table. The Value List dialog box reads:

 

Use values from First Field

companyID

 

Use values from Second Field

companyName

 

What I have works, of course. When I choose an item from the popup list, it places the companyID for that record into the Jobs table. But my list looks like this:

 

127 - Able Company

1 - Acme Widgets

30 - Baker Company

1022 - Charlie Company

 

I really want it to look like this:

 

Able Company

Acme Widgets

Baker Company

Charlie Company

 

How can I do this?

 

Many thanks.

Kevin.

[bikergeek]

Try this:

 

Make the value list only use company names.

Make the company selector field, "Name", the match field for a relationship. Call it pickID.

Define the ID field with an auto-enter calculation [replace] defined as

Evaluate ( pickID::ID ; Name )

It should populate with the correct ID when the associated name is chosen. Then it can serve as the match for your portal. It doesn't even have to be visible on the layout.

[Kevin_Ashbridge]

Hi, I've been trying your solution, but (I'm really sorry) I'm completely confused by your instructions and I'm getting nowhere. Do you have a moment to spell it out for me, as in (1) do this, (2) do that...

 

Many thanks,

Kevin.