2 Replies Latest reply on Apr 24, 2014 2:18 PM by MattClifton_1

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

    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

            

        • 1. Re: Get two field values from related table selected from drop down list
          philmodjunk

               You appear to have these relationships:

               People>-----Clients----<Invoices

               People::_fkClientID = Clients::__pkClientID
               Invoices::_fkClientID = Clients::__pkClientID

               Though your field names are likely different from mine.

               In People, define a text field, FullName, with an auto-entered calculation such as this:

               LastName & ", " & FirstName

               Set a unique values validation option on this new field.

               Then go to Manage | Database | Relationships and use the duplicate button (two green plus signs) to create a new table occurrence of People. Link it to Clients like this:

               Clients::_fkPeopleID = People 2::__pkPeopleID

               Format _fkPeopleID as a drop down list or pop up menu using a value list where field 1 shows data from People::__pkPeopleID and field 2 shows data from People::FullName. You can hide the first field to list only names if you want.

               Now you can put People 2::FullName, People 2::firstName or People 2::lastName on any Client or Invoice layout that you want and you'll see the name of the designated "invoice person" on that layout.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Get two field values from related table selected from drop down list
            MattClifton_1

                 Thanks, Phil.

                 Your instructions were clear, I understood them perfectly and it's exactly the solution I needed.

                 I got a strange result at first, everything linked correctly but I couldn't hide the personID field in the dropdown list - if I tried to do so, or even to simply sort by the second field (personFullName), I got a question mark. I found the error was that the FullName field calculation had been left as "number" and I needed to change it to "text".

                 Matt