3 Replies Latest reply on Nov 21, 2016 12:23 PM by melclift

    Joining Text Fields - calculation

    melclift

      Hi everyone,

       

      This probably has a really simple answer.

       

      I have 4 fields for names -  vendor name, vendor surname, vendor first name2, vendor surname2

       

      I have worked out how to join the fields based on the assumption that there is data in each field:

       

      Vendor Name & " " &Vendor Surname & " & " & Vendor firstname2 & " " & Vendor Surname2

       

      Giving the result:

      John Smith & Fred Jones

       

      My question is - if I don't have a second vendor, ie, no Fred Jones, how do I stop the "&" from showing in the resulting field?

       

      Many thanks,

       

       

      Mel

        • 1. Re: Joining Text Fields - calculation
          erolst

          Try this:

           

          Let ( [

            first = VendorName & " " & VendorSurname ;

            second = VendorName2 & " " & VendorSurname2

            ] ;

            Substitute ( List ( first ; second ) ; Char(10) ; " & " )

          )

           

          You could apply that method to the two pairs of name themselves first, then join the results of those calculations.

           

          On another note: consider your database design.

           

          The only place where you should have two name fields is in a table where you store entities that have names. But then you'd only have one set of name fields, because one record = one entity instance.

           

          The place where you could have two names is in a related table. But there you would a) use IDs, and b) if you could have two (or more) named entities, you'd probably create a join or a child table.

          • 2. Re: Joining Text Fields - calculation
            keywords

            Assuming you only have to check once (i.e. the two vendor2 fields go together—if you have one you'll have both) try:

             

            Vendor Name & " " &Vendor Surname & If ( IsEmpty ( Vendor fisrtname2 ; "" ; " & " & Vendor firstname2 & " " & Vendor Surname2 )

             

            • 3. Re: Joining Text Fields - calculation
              melclift

              This is exactly what I wanted - thank you!!