1 Reply Latest reply on Aug 11, 2015 9:39 AM by philmodjunk

    Combining Two Names from Different Fields on Address Labels

    MarcSherrell

      Title

      Combining Two Names from Different Fields on Address Labels

      Post

      Hello,

      I'm trying to generate address labels from an Excel spreadsheet.  The labels are for parents of students.  In the original Excel database, each student has two rows (one per parent name).  In my labels, I'd like to have the label say "Jane & John Doe" or "Mary Smith and John Doe."  The students are identified by a unique ID number, so there is a  connection between the student and parent names.  Right now, each student has two records (one record per parent), and that results in two address labels per student.  Any help is appreciated.  I'm using FM 10 Advanced for Mac.

      Thanks in advance

        • 1. Re: Combining Two Names from Different Fields on Address Labels
          philmodjunk

          And in today's family situations with divorce, remarriage, separation.... two such records might not be enough and you might need more than one address label for some students. In a similar situation, I set up a "household" table with relationships that established a many to many relationship between children and Household records.

          But to stick with what you have, you can set up a case function to calculate the different ways to refer to the parents by name on your address labels. This field would be defined in the students table.

          Let (  [fname1 = Parents::FirstName ;
                     fname2 = If ( Count ( Parents::LastName ) > 1 ; GetNthRecord ( 2 ; Parents::FirstName ) ;
                     lname1 = Parents::LastName ;
                     lname2 = If ( Count ( Parents::LastName ) > 1 ; GetNthRecord ( 2 ; Parents::LastName )
                    ];

                    Case ( Count ( Parents::LastName ) = 1 ; fname1 & " " & lname1 ;
                                  lname1 = lname 2 ; fname1 & " and " & fname2 & " " & lname1 ;
                                  fname1 & " " & lname1 & " and " & fname2 & " " & lname2
                                ) // case
                  ) // let

          Note: when referring to fields from a related record, the data will come from the first related record. Thus, there's no need for getNthRecord to refer to the name in the first parent record.