External Source Model Field Types, Expressions, and Formulas
  • 3 Minutes to read
  • Dark
    Light
  • PDF

External Source Model Field Types, Expressions, and Formulas

  • Dark
    Light
  • PDF

Article summary

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

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 , 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.

DynamicPlanningWinter21ChangeConstant1.png

Note:
You can change values for multiple Constant fields at once.

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.

DynamicPlanningWinter21ChangeConstant2.png

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.

DynamicPlanningWinter21ChangeConstant3.png

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.

DynamicPlanningWinter21ChangeConstant4.png

Note:
If you have defined a map to move data from the External Source Model to another model, then you must execute those maps to view the changes related to Constant fields reflected in the other models.
Note:
For the fields having the Maps To filter set to “Value” in your Source Map, you cannot change the values for these Constant fields in your Source Model.

In Practice: Change Constant Field Values in the External Source Model Page

  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.

    DynamicPlanningWinter21ChangeConstant5.png

Create a new Calculation for your ESM

  1. In SpotlightXL, select the Model task and the Source Mode l > 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.

    DynamicPlanningWinter21ChangeConstant6.png

In Practice: 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 required values in the Data Load fields and click Refresh.

Note:
If you have not created a calculation for your ESM, then you can click the Load Data option, and then click Refresh to view the updated values.

DynamicPlanningWinter21ChangeConstant7.png

In Practice: 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.

    DynamicPlanningWinter21ChangeConstant8.png

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.


Was this article helpful?