Get two field values from related table selected from drop down list
I'm trying to set up a printed invoice layout, and am getting stuck retrieving certain values that I want to appear.
Essentially, on each invoice I want to display the name of the person at that company who should receive the invoice - it should appear in full at the top (e.g. To: John Smith), and first name only ("John") after the word "Dear".
This is my setup at the moment: My tables are Clients, Invoices and People. Clients-->Invoices has a one-many relationship, as does Clients-->People. In People, each person has a first and last name.
In Clients, I have a text field called InvoicePerson. On my main Clients layout, this gets populated using a popup menu, using a value list displaying the fields People:first and People:last (using only related records). Of course, this only STORES the first name, not the last name, into Clients:InvoicePerson.
So at the moment I can do my "Dear John", but I can't retrieve the last name of the person. I could set up two popup menus, but that seems inefficient and subject to human error.
I feel like there's a better way to do it, perhaps by creating a calculation field somewhere which combines first and last names, but I'm not sure if it should go in the People table, Clients, or where.
Any help gratefully received.