Skip to main content
Upgrade mapping facilitates users upgrading from older to newer versions of templates without losing data. If a widget is removed or changes format, this section tells the upgrade mechanism what to do with it.
Video Learning Session Recording

​
Background

Important: Upgrade mappings are only relevant for inputs (input widgets, lookup widgets, or inputs/lookups within table columns) - it has nothing to do with equation widgets. They are only about how to transfer a user’s inputs from an older version of a template to its newer version.
If no upgrade mappings were set, the “Calculation Upgrades” mechanism would ONLY do a 1:1 copy of:
  • [referenceId in old template version] → [referenceId in new template version]
This means that ALL other data not matching referenceIds will be lost. If you want anything to happen on the upgrade other than a 1:1 copy of referenceIds, then you need to write an upgrade mapping for it.
Fun Quirk: Upgrade mappings are run in the order that they are entered in the JSON code. If you set a new value of a widget with a mapping, and then use that widget’s value in an upgrade mapping, it will use the new value instead of the old one. This is particularly important when checking if a widget exists.

​
Basic Structure

{
  "type": "sheetTemplateUpgrades",
  "attributes": {
    "mappings": [
      {
        "targetReferenceId": "", 
        "equation": [
          {
            "result": "",
            "condition": ""
          }
        ]
      }
    ],
    "sharedTables": [
      {
        "sourceSharedTableId": "",
        "targetSharedTableId": "",
        "mappings": [
          ["old_label", "new_label"],
          ["old_label2", "new_label2"]
        ]
      }
    ],
    "ignore": ["REFERENCEID1", "REFERENCEID2"]
  }
}

​
Key Parameters

KeyValueRequired?Description
typestringYesMust be "sheetTemplateUpgrades"
mappingsArrayYesMapping old widgets to new ones
sharedTablesArrayNoMapping old sharedTable labels to new ones
ignoreArrayNoReferenceIds to ignore during upgrade

​
Custom Functions

​
Available Functions

​
exists(referenceId)
function
Returns: boolean
Check whether a widget with given refId exists
​
input(referenceId)
function
Returns: string
Get the user inputted value
​
type(referenceId)
function
Returns: string
The type of a cell (e.g., input, lookup)
​
deepReplace(input, current, new)
function
Returns: object/matrix
Replace strings in nested keys/arrays
​
not(bool)
function
Returns: boolean
Reverses the boolean value

​
Mapping Types

​
Simple Widget Mapping

Map one widget to another with the same value: 
{
  "targetReferenceId": "new_widget",
  "equation": [
    {
      "result": "input(\"old_widget\")",
      "condition": "exists(\"old_widget\")"
    }
  ]
}

​
Conditional Mapping

Map different old widgets to the same new widget: 
{
  "targetReferenceId": "psi_y",
  "equation": [
    {
      "result": "input(\"psi\")",
      "condition": "exists(\"psi\")"
    },
    {
      "result": "input(\"psi_x\")",
      "condition": "exists(\"psi_x\")"
    }
  ]
}

​
SharedTable Mapping

Map lookup values from old to new sharedTables: 
{
  "sourceSharedTableId": "displaylc_au",
  "targetSharedTableId": "displaylc_eu",
  "mappings": [
    ["Strength: (1.35G)", null],
    ["Strength: (1.2G, 1.5Q)", null]
  ]
}

​
Table Mapping

​
Complex Table Transformations

When mapping tables with structural changes (e.g., adding columns), use the iterate function: 
iterate(1, vectorSubset(size(input("table")), 1), 1, 
  iterate(1, vectorSubset(size(input("table")), 2) + 1, 1, 
    (i == 1 ? matrixSubset(input("table"), j, i) : 
     i == 3 ? "NewColumn" : 
     matrixSubset(input("table"), j, i - 1)), 
    i), 
  j)

​
Example: Adding a Column

{
  "type": "sheetTemplateUpgrades",
  "attributes": {
    "mappings": [
      {
        "equation": [
          {
            "result": "iterate(1, vectorSubset(size(input(\"M_h,table\")), 1), 1, iterate(1, vectorSubset(size(input(\"M_h,table\")), 2) + 1, 1, (i == 1 ? matrixSubset(input(\"M_h,table\"), j, i) : (i == 2 ? (matrixSubset(input(\"M_h,table\"), j, i) == 0 ? \"Hill/Ridge\" : matrixSubset(input(\"M_h,table\"), j, i) == 1 ? \"Escarpment\" : matrixSubset(input(\"M_h,table\"), j, i)) : (i == 3 ? \"Upwind\" : matrixSubset(input(\"M_h,table\"), j, i - 1)))), i), j)",
            "condition": "not(matrixSubset(input(\"M_h,table\"), 1, 3) == \"Downwind\" or matrixSubset(input(\"M_h,table\"), 1, 3) == \"Upwind\")"
          }
        ],
        "targetReferenceId": "M_h,table"
      }
    ]
  }
}

​
Lookup Widget Mapping

​
Mapping to New Lookup Values

{
  "equation": [
    {
      "result": "\"Horizontal\"",
      "condition": "not exists(\"incline_flag\")"
    }
  ],
  "targetReferenceId": "incline_flag"
}
When setting an input widget to a string, it needs to be double-quoted! For instance, setting a widget to “No”, you’d enter "result": "\"No\""

​
Lookup to SharedTable Mapping

When mapping from a lookup widget to a sharedTable, you need entries in both sharedTables and mappings: 
// In sharedTables:
{
  "mappings": [
    [20, "20 MPa, Normal-weight"],
    [25, "25 MPa, Normal-weight"]
  ],
  "sourceSharedTableId": "dataTable:f'c",
  "targetSharedTableId": "concrete"
}

// In mappings:
{
  "equation": [
    {
      "result": "input(\"f'c\")",
      "condition": "exists(\"f'c\")"
    }
  ],
  "targetReferenceId": "concrete"
}

​
Best Practices

1

Order Matters

Remember that mappings execute in order - earlier mappings can affect later ones
2

Test Thoroughly

Always test upgrade mappings with real user data
3

Document Changes

Keep clear documentation of what each mapping does
4

Handle Edge Cases

Consider null values, empty tables, and missing widgets

​
Ignore List

List referenceIds that should be ignored during upgrade: 
{
  "ignore": [
    "deprecated_widget1",
    "old_calculation",
    "removed_feature"
  ]
}
“Ignore” only affects equation mappings - it has no effect on sharedTable mappings. Also, if you’ve included a mapping from a deprecated id to a new one, you don’t need to repeat it in the ignore section.

​
CI Integration

CI will advise that tables without any inputs should be mapped, however you can put them on the ignore list if they don’t need mapping.