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.
1 of 1 people found this helpful
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.
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.
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.
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!