Creating, Editing, and Filtering External Source Model Formulas

This option is available for Power and Contributor users. Reviewer users cannot modify ESM formulas.

For a complete list of formulas available, see External Source Model Field Types, Expressions, and Formulas.

Viewing Existing ESM Formulas

In Practice

1. Login to Spotlight Web.

2. Select the Model task.

3. Scroll to find the External Source Model you want to view.

4. Click the gray text that says External Source Model.

   
   
   

   The list of existing fields appears. Scroll to find the fields with a formula associated with them.

5. Click a field to view its definition. The right pane refreshes to show the formula.

   

Editing Existing ESM Formulas

Before you can edit existing Source Model Fields with Formulas:

• You cannot modify existing fields if a Source Model Map exists because an existing Source Model Map contains mappings from the current list of fields.
• You cannot modify existing fields if the Source Model contains data. You must clear the model first.

In Practice

1. Login to Spotlight Web.

2. Select the Model task.

3. Scroll to find the External Source Model you want to view.

4. Click the gray text that says External Source Model.

   
   
   

   The list of existing fields appears. Scroll to find the fields with a formula associated with them.

5. Click a field to view its definition. The right pane refreshes to show the formula.

   
   
   

6. You can edit the existing formula by clicking inside of it and making changes. You can also select all the text, press Del, and then use the Functions and Operators drop-downs to create a new formula.

   
   
   

7. When done, click Set Formula. You can make changes to other fields and formulas before doing a final save on the list of fields.

8. When done making all changes to fields, click the Save button.

Creating New ESM Formulas using Model Manager

If your Source Model contains fields of type Formula, you can create the formulas using Model Manager's interactive formula builder.

In Practice

1. Login to Spotlight Web.
2. Select the Model task.
3. Scroll to find the External Source Model you want to view.
4. Click the gray text that says External Source Model.

The list of existing fields appears.

The right pane displays the formula builder options and editor. You can type in a formula where it says "Enter formula here..." or you can select from the drop-down list of functions and operators.

Example

In this example, we will create a unique Customer # by combining other fields together:

Customer # Preface - State - Phone Number

1. From the Functions list, select Concatenate. The basic syntax for this function is inserted into the editor.

   

2. To ensure that the formula will be assigned to the correct field, enter the field name before "Enter Formula."

   

3. Select the text Field 1 and press Del. Type @ to see a list of field names to insert. Select Customer # Preface.

   
   
   

4. Select the text Field 2 and replace it with the second field name to use: State.

5. Select the ... after Field 2 and replace it with the third field name to use: Phone Number. Clean up excess brackets as needed.

   Now the formula looks like this:

   
   
   

6. To add the dashes between each field in the formula, add a comma and " - " as follows:

   
   
   

7. Click Set Formula.

8. Click Saved Formulas to see the list of formulas created and saved so far.

   
   
   

9. When done making all changes to fields and formulas, click the Save button.