Greetings all! Quick question - I'm sure its simple.

I have 2 tables - ORDERS and EMPLOYEES. when someone adds an order, I want the employee to have to pick who took the order. So, my 2 tables would look like:

ORDERS

Order PK

Date, Time, all relevant order info

EmployeeFK

EMPLOYEES

EmployeePK (Numeric - serial number - auto adds)

First Name

Last Name

I have a relationship between EmployeeFK and EmployeePK set to =.

All I want is a drop down box on my Order Entry Layout that when I click it, shows me First and last name. Once I pick someone, it displays both first and last names.

Right now I have my drop down looking at ORDERS::EmployeeFK. It's control style is a drop down list - and it displays a value list equal to EMPLOYEE::First name and EMPLOYEE::Last Name.

Is this right? I worry that the actually value the FK is holding is first name and not the PK of employee. Also, once you pick a name, all it shows is first name - not both.

Any help is appreciated! Thanks in advance!

You are right to be worried - this is not a good arrangement.

First, add a calculation field cFullName (result is Text) to the EMPLOYEES table =

First Name & " " & Last Name

or the other way round, if you prefer.

Next, change your value list to use values from field EMPLOYEES::EmployeePK, also displaying values from EMPLOYEES::cFullName.

Finally, change the relationship between the two table s to:

ORDERS::EmployeeFK = EMPLOYEES::EmployeePK

In order to show the selected employee's name you can either place the name field/s from EMPLOYEES on the Order Entry Layout (make them non-enterable, so that they won't be changed accidentally), or change the drop-down to a pop-up.

Note:

When defining the value list, you can select to "Show values only from second field" - but this requires employees' full names to be unique at all times.

Hey - thanks for the reply.

the pop up box is good stuff - I think I'll use that.

My one main question with what you wrote is what FMP puts in things.

I have my drop down field set to hold a numeric FK field. I tell it to show values in another table. So, i want that FK field to hold another table's PK value, but I want it to show First name and last name.

if I set it up that way, will it work or will it store First name in the FK slot?

To understand what's really happening, put another instance of EmployeeFK on the layout, and format it as edit box. Change the pop-up selection and observe what's being stored in the field.

OK - so I think I got it. It makes a bit more sense now.

one thing I can't work out is this: I have a FK that holds the PK of the other table. On my form, I want it to display info from the other table based on this relationship.

So, we have that Order table with the employee FK. I want to pick my employee from a drop down and have it display the full name of the employee I picked in a different box.

I made a Drop down box referencing that FK - and also displaying a "first & " " & Last" like you said. If I put a box next to it and have it show EMPLOYEES::Full Name - it never displays anything. Do i need to do a lookup here? Why won't it look up the full name based on what I picked?

Thanks again!!

Does this work for you?

example.zip

Wow - thats awesome you did that. Thanks a ton.

That makes sense - I'm going to set my stuff up that way.

I think my problem now is I am having relational issues. I need to examine my structure. Problem is that I have a root table called 'company' and essentially 2 branches off of it. I'm trying to relate one to the other and I'm getting all fubarred.

Thanks for the help - I think you got me fixed. Now I need to straighten the rest out....