Jump to content
Salesforce and other SMB Solutions are coming soon. ×

auto-calculation referencing >= 2 fields

Jake Sterling

Recommended Posts

I often want to use a text or number field with an auto-calculation rather than a calculation field. I do this because I need to use the field as a key field in a relationship or simply because of the time that is saved when the calculation only has to be made the first time.


A problem arises when the calculation references 2 or more fields. For instance, if you create a field, FULL_NAME, which cancatenates the fields FIRST_NAME & LAST_NAME [i.e.: FIRST_NAME & " " & LAST_NAME], as soon as FIRST_NAME is filled in the calculation happens; but, because LAST_NAME is not filled in yet, it does not get included in the FULL_NAME field.


The solution is to surround the Cancatenation in an If calculation like so:


If (not IsEmpty(LAST_NAME), FIRST_NAME & " " & LAST_NAME, "")


This way, the calculation doesn't occur until the the field in the test section of the If calculation -- in this case LAST_NAME -- is populated. cool.gif

Link to comment
Share on other sites

Hi Jake,

In general it is wise to account for all circumstances when constructing formulae of this kind.


Whereas the expression you've devised will deal with the most common scenario, it will break if ever someone enters in the last name before entering the first name. Therefore the process will be more robust if you test for the presence of both names, viz:


Case(Length(FIRST_NAME) and Length(LAST_NAME), FIRST_NAME & " " & LAST_NAME)


Note that the Case( ) function is better suited to this role because it does not require a result-if-false, and the Length( ) function provides a simple and explicit test which already has the correct polarity for this statement (and therefore does not have to be preceded by 'not').


Even so, however, I question whether your choice of an auto-enter calc is wise in such a case as this. It's true that a calculation field will update whenever the fields it references are changed, and that this will use a CPU cycle or two each time. But that's a small price to pay for having your calculation (and any relationships which may depend upon it) remain *accurate*.


By way of example, if somone mis-spells the entry in the FIRST_NAME field and later corrects it, any relationship based on a concatenation within an auto-enter calc will be broken until/unless they also manually correct the concatenated field. By the the user detects the need to make the correction twice and does so, the CPU cycles have been expended anyway - along with extra human effort that could have been avoided (not to mention any fallout from the relationship having been broken in the first place).


All in all, I'd have said that the example you've offered provides a good illustration of a case where a conventional calculation field would be a better choice. wink.gif

Link to comment
Share on other sites

  • Create New...

Important Information

Terms of Use