External Source Model Field Types, Expressions, and Formulas

External Source Models support the use of expressions and formulas in laying out the Source Model and in calculations.

• Data Types

Data Types

The following data types are supported.

• Text
• Numeric
• Date
• Formula
• Constant Text
• Constant Numeric
• Constant Date

Change Constant Field Values without Clearing External Source Model Data

You can directly change values for the Constant fields available in the External Source Model (ESM) without clearing the model data. For example, if you have four constant fields along with other fields in your ESM and you want to change the values for two Constant fields, then you can directly change the values in the Expression column for these constant fields without clearing the model. You can view all the updated values in the Constant fields Source Data Load.

In addition, you can view all the changes made to the Constant fields and Formula fields in the Audit Log, thereby easily tracking all changes made to the model data at one place.

Let us take the following example to understand the details.

In any Source Model containing fields of ConstantText, ConstantNumeric, and ConstantDate type, you can change the existing cell values with new values. For example, in the following image, you have a Source Model containing

Department Name, Department ID, Department Date, and Department Manager fields that are of type ConstantText, ConstantNumeric, ConstantDate, and ConstantText respectively.

You can change the value of any Constant fields based on your requirement and update the fields with new values.

Once you change the required values in your source model, you can create a new calculation for your ESM and run the calculation to view the changes in the Source Data Load page.

While creating a new calculation, you must select ExternalSourceFormula as the value in the Type column and select your source model as the value in the Name column. After creating the new calculation, you must Save and Run it.

Once you run the calculation, to verify the updated values you can navigate to the Source Data Load page. You can view all the new values updated for the Constant fields in the Source Data Load page when you enter values in the respective Data Load fields, and click Refresh.

You can also view all the new values updated for the Constant fields in the Source Data Load page without creating a calculation. You can click the Load Data option, and then click Refresh to view the updated values.

Whenever you update any value for the Constant fields, you can track all the changes made to your model in the Audit log as shown in the image below.

How to Change Constant Field Values on the External Source Model Page in SpotlightXL?

1. In SpotlightXL, select the Model task and the External Source Model > Source Model subtask.
2. Select a model and navigate to the available Constant fields.
3. Select a Constant field and enter a new value in the Expression column.
   
   
   Note:

   You can update values in the Expression column for multiple Constant fields.
4. Click Save.

   

How to Create a New Calculation for ESM?

1. In SpotlightXL, select the Model task and the Source Mode > Calculation subtask.
2. Select any model and specify the required calculation name in the Name field.
3. In the Type column, select ExternalSourceFormula from the drop-down list.
4. In the Name column, select your source model name from the drop-down list.
5. Click Save, and then click Run.

How to Verify Changes to Constant Fields in the Source Data Load Page?

1. In SpotlightXL, select the Model task and the External Source Model > Source Data subtask.
2. Enter the required values in the Data Load fields and click Refresh.

How to Track Changes Made to the Model?

1. In SpotlightXL, select Manage > Application Administration > Audit Log.
2. Click the required cell in the Details column and navigate to the formula bar.
3. Verify all the changes made to the values of the Constant fields.

Limitations

• You cannot add new rows to the existing model or delete existing rows without clearing the model.
• You cannot change the Field column values from the existing model without clearing the model.