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

Title

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

Post

     Hi

     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.

     thanks

     Matt

      

Outcomes