AnsweredAssumed Answered

Get two field values from related table selected from drop down list

Question asked by MattClifton_1 on Apr 24, 2014
Latest reply on Apr 24, 2014 by MattClifton_1


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.