Formulas | Mirror fields, picklist values, images

Mirror fields

A formula field can display the value of a field in another object. This can be helpful if you want to display a summary of fields from a number of different objects such as on the Team Member record, or if you want to include a field in an email template.

The output data type depends on the data type of the field you want to mirror, for example:

  • If the field you want to mirror is a number field with 4 decimal places, select the output data type of the mirror formula field as number with 4 decimal places.

  • If you want to display the value of a picklist field or a lookup field, select the output data type of the mirror formula field as text.

Warning

Salesforce has limits on cross object relationships, so if you create a mirror field between objects where there is no standard relationship, you may hit the limit. This can prevent the USA Country Pack package from being installed.

Example: Mirror FTE from the Employment Record to the Team Member record

To create a field to mirror the FTE value from the Employment Record to the Team Member record: 

  1. Go to Setup > Object Manager, and select the Employment Record object.

  2. From the sidebar, select Fields & Relationships.

  3. On the list of fields, view the details displayed in the Data Type column for the FTE field: Number (1, 3).

  4. Go to the Team Member object and create a new formula field, keeping the following details in mind: 

    • Select Number as the output data type for the formula field

    • Specify 3 decimals for the number

    • Insert the FTE field into the formula as shown in the following screenshot, by selecting Insert Field, and then selecting the Team Member object, the Current Employment field, and the FTE field from the Employment Record object: 

      Screenshot: Inserting the FTE field from Employment Record into a formula field in the Team Member record

Example: Mirror Basis from the Employment Record to the Team Member record

  1. Go to the Team Member object and create a new formula field, keeping the following details in mind: 

    • Select Text as the output data type for the formula field

    • Insert the TEXT function into the formula

    • Select the text value inside the TEXT function in the formula

    • Insert the Basis field into the formula to replace the text value as shown in the following screenshot: 

      Screenshot: Inserting the Basis field into a formula in the Team Member object

    The resulting formula looks like the following example:

    Copy

    Formula using the TEXT function

     TEXT( fHCM2__Current_Employment__r.fHCM2__Basis__c ) 

Picklist values

Picklist fields can be used in a more limited set of formula functions than other types of fields. If you attempt to use a picklist field in a formula in an unsupported way, an error message is displayed.

Functions with support for picklist fields in formulas include ISPICKVAL, CASE, and TEXT.

The ISPICKVAL function

The ISPICKVAL function enables you to select certain values within a picklist. The ISPICKVAL function works by specifying the custom picklist field and then specifying the picklist value as follows:

Copy

ISPICKVAL example

ISPICKVAL(picklist_field,"Picklist Value")

Example: Flag for issues to address based on reason for leaving

To create a checkbox on the Employment Record to indicate HR has actions to address based on a team member's reason for leaving:

  1. Go to the Employment Record object and create a new formula field, keeping the following details in mind: 

    • Select Checkbox as the output data type for the formula field.

    • Insert the ISPICKVAL function into the formula.

    • Select the text picklist_field inside the ISPICKVAL function in the formula.

    • Insert the Reason for Leaving field into the formula to replace the text picklist_field. 

    • Select the text text_literal inside the ISPICKVAL function in the formula, and replace it with the reason value in quotation marks, for example "Issues with Role"

    • If you want to include multiple options, insert the OR operator || and add another ISPICKVAL function with a different reason.

    The resulting formula looks like the following example:

    Copy

    Formula using the ISPICKVAL function and OR operator

    ISPICKVAL(Reason_For_Leaving__c, "Issues with Role") || 
    ISPICKVAL(Reason_For_Leaving__c, "Issues with Manager") || 
    ISPICKVAL(Reason_For_Leaving__c, "Other Work Issues")

The TEXT function for a picklist value

The TEXT function allows you to return a picklist value as text. Unlike ISPICKVAL, you do not need to specify the picklist value. The TEXT function is often used when creating mirror fields on different objects when the original field is a picklist, like in the Basis field example. To use the TEXT function, enter the picklist field in place of value in the following example:

Copy

The TEXT function

 TEXT( value ) 

Images

The IMAGE function in formulas can be used to display a picture in the formula field or in an email template:

Copy

The IMAGE function

IMAGE(image_url, alternate_text, height, width)

You can add conditions to display different images to different groups by adding with IF and CASE functions to the formula. See the Salesforce help article Use Images in 'Formula' Fields for examples.

If you are considering this method for displaying an image in Sage People WX, keep in mind WX has not been designed to include images in this way and some WX processes do not display the formula images correctly even if the formula field has been included in the relevant field set or performance review templates. Formula fields with images are known to work in the following WX processes: 

  • Recognition: images are displayed correctly in the Details view. The New Recognition dialog displays the field label only.
  • Performance Overview: images are displayed correctly in the Details section and as part of performance reviews and self performance reviews when editing or viewing them.

Other WX processes have known issues with formula fields displaying images: 

  • Time Off: Formula fields can be added to the field sets in the Absence object, but the New Time Off dialog only displays the field label, not the content of it. Absence approval dialog displays the field label, image, and the HTML code for the image.
  • Performance Review: Formula fields with images display correctly in the View Details section, but when editing a performance review or self performance review, the dialog displays field label, image, and the HTML code for the image.

The image can be an image of text if you need text presented with more formatting options than a text formula can provide. When considering this option, remember images of text can present accessibility issues for users with assistive technology such as screen readers, users who want to increase the size of text on their screens, and users who want to adjust the contrast between text and background.

The following screenshot displays an example of an image formula containing an image of text in the Performance Overview process in WX: 

Screenshot: A formula field holding an image of text

The example contains text with a heading style, text in a different color, different font sizes, a bulleted list, and bolded text.

Example: displaying an image with the image formula

Start by adding the image to static resources in the org for easy access:

  1. Go to Setup, and in Quick Find enter static and select Static Resources from the search results.

  2. Select New.

  3. In the Name text box, enter the text that should be used to identify the resource. This name can contain only underscores and alphanumeric characters, and must be unique in your org. It must begin with a letter, not include spaces, not end with an underscore, and not contain two consecutive underscores.

  4. In the Description text area, specify an optional description of the resource.

  5. For File, select Choose File to select the file you want to upload from your computer. 

    Note

    A single static resource can be up to 5 MB in size, and an organization can have up to 250 MB of static resources, total.

  6. Set the Cache Control to Public:

    • Public cache control specifies that the static resource data cached on the Salesforce server be shared with other users in your organization for faster load times.

    • Private specifies that the static resource data cached on the Salesforce server is not to be shared with other users. The static resource is only stored in cache for the current user’s session.

  7. Select Save.

    Sage People displays the details of the added static resource.

  8. Select View File and copy the URL from the browser's address bar.

Now you can create the formula field: 

  1. Follow the guidance to create a custom formula field for the object where you want the image to display, keeping the following details in mind:

    • Select Text as the output data type for the formula field.

  2. In the formula editor, select the Advanced Formula tab if it is not already selected.

  3. Insert the IMAGE function and paste the image URL into the image_url section of the function, for example:

    Copy
    Example IMAGE function
    IMAGE("/resource/1588769960000/PRInstructions",
    "Instructions for performance review")

    In the example, note: 

    • If you use an image hosted in the static resources, the URL does not need a Salesforce or your org's domain, just the part starting with /resource.

    • As the URL and the alternate text parameters are text values, they need to be surrounded by quotation marks.

  4. Set the appropriate field level security to your new custom field, then select Next.

  5. Add your new custom field to all relevant HR Manager portal page layouts, then select Save.

For the image to display in a WX process, you also need to add the formula field to a field set or performance review template: 

  • To use an image formula in Recognition add the formula field to the Details field set

  • To use an image formula in Performance Overview add the formula field to the WX Performance Overview Details Section field set. This field set is hosted in the Team Member object.