5 Replies Latest reply on May 25, 2012 12:59 PM by DaleLong

    Edit concatenated name field updates individual name fields and vice versa

    DaleLong

      I'm wondering if anybody has a technique for achieving this? Basically, we have a concatenated name field that calculates a result like this:

       

      LastName, FirstName

       

      Currently, the concatenated name updates if you edit one of the individual name fields using a formula something like this:

       

      Let

      (

      [

      ~firstName = fname ;

      ~lastName = lname ;

      ~noFirstName = IsEmpty ( ~firstName ) ;

      ~noLastName = IsEmpty ( ~lastName ) ;

      ~separator = Case ( ~noFirstName or ~noLastName ; Null ; ", " )

      ] ;

       

       

      ~lastName & ~separator & ~firstName

      )

       

       

      This is essentially controlling for situations where we have only a first or last name, no insertion of a separator is to occur in this case.

       

       

      Is it possible to have this work in reverse using only auto-enter calcs? In other words, if you edit the concatenated name, the first and last name individual fields update accordingly. I've tried a few things, but so far I've only been able to get the updates to work one way or another, not both ways. The last thing I tried was something like

       

       

      lname =

      Let

       

       

      (

      [

      ~separator = ", " ;

      ~nameList = Substitute ( fullName ; ~separator ; ~carriagereturn ) ;

      ~firstName = GetValue ( ~nameList ; 2 ) ;

      ~lastName = GetValue ( ~nameList 1 )

      ] ;

       

      ~lastName

       

      )

       

      But I can't quite get it to work the way I want; the updates either don't happen at all, or will only happen one way if I toggle the Do Not Replace Existing Value option on one field or the other.

       

      I imagine I could probably get this to work using a script trigger; but I'm curious if somebody has a technique for this that works at the auto-enter calc level?

        • 1. Re: Edit concatenated name field updates individual name fields and vice versa
          MikeRauch

          Dale,

           

          When you have two or more auto-enter calcs that reference each other you're not going to get what you want.  They will update, but there is a dependency tree and which one updates like you think it should will depend on the 'firing' more-or-less of those dependencies.  I think you need a different approach.

           

          Have you considered a single data entry field and then calculate fname, lname, fullname, etc. from that?  So they enter it however they want and you clean it up as best you can within the calcs.  And is there a reason they need to be auto-enter calcs instead of calc fields?

           

          And, for what it's worth, I'd write your first calc like this...

           

          fname & Case ( not IsEmpty ( lname ) ; ", " ) & lname

           

          ...but that's just a style thing.  Good luck.  Hope this helps.

           

          Mike Rauch

          • 2. Re: Edit concatenated name field updates individual name fields and vice versa
            karendweaver

            The only disadvantage to a singe field - how does it handle hyphenated last names?  Especially if they aren't hyphenated.  I have seen last name entries with Jones-Smith, Jones/Smith, Jones:Smith, etc.  Parsing out a full name is much harder than concatenating the name.

             

            BTW - I use the List function for concatenated fields where not all fields may always have data

             

            Substitute ( List ( lname; fname ) ; "¶" ; ", " )

             

            Then I don't need to test for missing data.  Really helpful if you have middle name fields, too.

             

             

            Karen

            1 of 1 people found this helpful
            • 3. Re: Edit concatenated name field updates individual name fields and vice versa
              MikeRauch

              Karen,

               

              I agree that deconstructing a manully entered name can be difficult; hyphenated names, prefixes, suffixes, etc.  I figured since the OP wanted to have users be able to edit the concatenated name directly he already had that part figured out ;-)

               

              And I like your List usage there.  I'll put it in the tool-bag.

               

              Mike Rauch

              • 4. Re: Edit concatenated name field updates individual name fields and vice versa
                DaleLong

                Thanks for the info, I was kind of thinking that I may not be able to bend auto-enter calcs to my will in this way, but it's always worth asking.

                 

                A single field entry isn't possible, because the separate last and first name fields are imported from a MySQL table; the concatenated name is used by us to help with alphabetical last name sorting.  I changed it from a stored calc to an auto-enter calc because of the desire to edit the concatenated name directly, but again I want both the concatenated name and individual name fields to always agree with one another, and thus my desire to make the fields reference each other.  I can probably script trigger the desired functionality in, I'll just have to track down all layouts where the trigger is applicable I guess.

                • 5. Re: Edit concatenated name field updates individual name fields and vice versa
                  DaleLong

                  I had no idea the List function would work like that.  This warrants a trip back to the function study guide.  Awesome technique, thanks for sharing!