Keys, drop downs, and Vaule Lists

[karatehero]

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!

[comment]

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.

[karatehero]

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?

[comment]

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.

[karatehero]

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!!

[comment]

Does this work for you?

example.zip

[karatehero]

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....