Sugar Logic Walkthrough - Tracking a Field's History

Overview

Contacts in Sugar® may occasionally have their names modified, for example, if they get married and change their last name. When the contact's name is updated in Sugar, the historical name information is not available. This may result in failed searches for users who are not aware that the contact's name has changed. In order to prevent situations like this, we will go over how to create a calculated value field that will hold all revisions of the contact's name and allow searching based on the current and all previous values of the name field.

Studio's Sugar Logic formula builder allows users to create business logic for field calculations and dependencies without needing to access code. We will walk through an example to demonstrate the power and the process of Sugar Logic formulas.

Use Case

In this example, we will create a calculated value field called "Name History" to retrieve a contact based on this change log after the contact's name is changed to "Shawne Johnson". We will perform the search by using the contact's original first name of "Sean". Please note that you can use this same method to log changes for other field types as well to search for records using previous values.

Prerequisites

You will need to be familiar with the following actions which are not explained in this article:

• Adding fields with Studio
• Altering layouts with Studio
• Searching via Basic or Advanced Search

For more information about the above topics, please refer to the Studio documentation and the Search documentation in the Administration and Application Guides relevant to your Sugar product and version.

Solution

Creating the Field

1. Navigate to Admin > Studio > Contacts > Fields and click "Add Field".
2. Create a new TextArea field with the following values:
   • Data Type : TextArea
   • Field Name : name_history
   • Display Label : Name History
3. Check the "Calculated Value" checkbox, and click the "Edit Formula" button to enter the calculation.
4. In the popup Formula Builder window, add the following formula and click the Save button.
   

    ifElse(contains($name_history_c,concat($first_name," ",$last_name)),
    $name_history_c,concat($name_history_c," ",$first_name," ",$last_name))

5. Click the Save button on the Edit Field tab.
   

Adding the Field to Layouts

Once the Name History field has been created, it will need to be added to the Contact module's search and detail view layouts in Sugar. The field will be added to the search layout, so that you can perform a search based on the stored name values to pull up the specific record on the list view. It will also be added to the detail view layout so that you can check that the historical names are populating properly within the calculated field. You may remove the field from the detail view layout once you have confirmed that the Name History field is logging the changes and storing the previous values. The following steps will go over how to add the field to the Basic Search and the Detail View layout as an example.

Basic Search Layout

1. Navigate to Admin > Studio > Contacts > Layouts > Search > Basic Search.
2. Drag and drop the Name History field from the Hidden column to the Default column.
3. Click the Save & Deploy button.

Detail View Layout

1. Navigate to Admin > Studio > Contacts > Layouts > Detail View.
2. Drag the Name History field to the desired location on the detail view layout.
3. Once the layout is finalized, click "Save & Deploy" to preserve your changes and push the new layout.

Mass Updating the Field

When a calculated field is added to a module, the calculated field will appear blank in all of the historical records in your system. The calculated value is not updated automatically when added to existing records in your system and are updated only when the record in which they exist is edited and saved. For more information regarding updating calculated fields, please refer to the article Introduction to Calculated Fields. The steps below will go over updating the calculated values in numerous records of the Contacts module at once instead of one by one via the Mass Update function.

1. Navigate to the Contacts module and select "View Contacts".
2. Select the records on the list view you wish to update.
3. Choose "Mass Update" from the Actions dropdown menu to open the Mass Update panel below your records.
4. Then, without editing any of the fields available in the Mass Update panel, click on the Update button.
   

Performing this mass update will open and save all of the selected records without making any changes to the fields but will update the calculated value for the Name History field.

About the Formula

Formula Explanation

The ifElse() function looks to see if the condition in the first parameter is true. If it is true, then it will return the value in the second parameter. If it is false, then it will return the value in the third parameter.

For example, ifElse(Condition, True result, False result).
If we write ifElse(1=1, "Yes", "No") the string returned will be "Yes".
If we write ifElse(1=2, "Yes", "No") the string returned will be "No".

The concat() function pastes together all of the parameter strings and returns the result as one string. You can concatenate as many strings as you like as long as they are separated by commas within the function's parenthesis.

For example, concat("This", " ", "is", " ", "a", " ", "concatenation", " ", "test.")
This will return a value of "This is a concatenation test."

The contains() function is used to see if the string in the first parameter contains an occurrence of another string, the second parameter.

For example, contains("Mary had a little lamb", "little") will return true because the second parameter string, "little", is contained in first parameter string, "Mary had a little lamb".
If the formula was : contains("Mary had a little lamb", "wolf"), this will return false because "wolf" does not appear in the string "Mary had a little lamb".

Formula Breakdown

For our formula, we are first checking to see if the current Name History value already contains the current name of the Contact record. The contains() function will check to see if the first parameter string contains a copy of the second parameter string. For the purpose of this check, the contact's name is represented by combining the first name, space, and then the last name using the concat() function.

If the Name History field already contains the current value of the contacts name, it will not be added to the Name History - the Name History field is already up-to-date. By checking the value of $name_history_c, the formula is avoiding adding the current name to its history field when there has been no change.

If the current name that is in the Contacts Name field is not an existing value in the Name History field, then it will pull this "new" name into the calculated value field. The second use of the concat() function appends the new, current contact's name to the end of the existing Name History field's value.

Application

Now that the Name History field is set up properly, we will test this out using the following example:

1. Navigate to the Contacts module and select "Create Contact".
2. Enter in the first name as Sean and last name as Jones. Click "Save".
3. Edit the new contact record and change the first name to Shawn. Click "Save".
4. Edit the contact record again and change the first name to Shaun and the last name to Jones. Click "Save".
5. Edit the contact record one final time by changing the first name to Shawne and last name to Johnson. Click "Save".
   
6. Navigate to the Basic Search panel of the Contacts module to test out the search.
7. Enter %sean% into the Name field and click "Search".

   • Notice that this will not bring up the contact record "Shawne Johnson", which is the final name we changed the contact record to. This is because the search is looking for a contact record that contains the name Sean in the Name field and "Shawne Johnson" does not match this criteria.

     
8. Now, enter %sean% into the Name History field and click "Search".

   • Notice that this will bring up the contact record "Shawne Johnson" as the search looks for the name Sean based on the Name History field. Since the Name History field stored the various name changes we made, it was able to successfully pull up the correct Contact record as the name Sean is a value in this field.

By enclosing the search criteria with %[criteria]% as we did in this example, Sugar will search for the string you entered in all parts of the field. Keep in mind though, if you do two items (e.g. %sean%%jones%), the two strings will need to be in the proper order. So "jones" has to follow "sean" in the Name History field in order to bring up the proper result on the list view.

Now that you have created this field, you will be able to find the appropriate contact records in Sugar even with the many name changes they may go through as the history will be preserved.