Use Self-References in Derived Columns

Since R2024b

You can self-reference the cells in derived columns by using the sfa_cell keyword. You can use sfa_cell to retrieve information about the cell, or about the row and spreadsheet that contain the cell. You can then adjust the output of the derived column cells.

For more information on derived columns, see Define Derived Values and Create a Derived Column.

Retrieve Cell Information

To access the derived column cell information and use it in the same derived column, use a self-reference:

Create or open a spreadsheet with at least one derived column.
Right-click the derived column and click Edit Formula.
In the derived column formula, use the sfa_cell keyword. The sfa_cell keyword returns a read-only SpreadsheetCell object for the derived column cell.
Get the cell information by retrieving property values or applying functions to the self-referenced cell.

For example, this code retrieves the description of each cell of the derived column.

myCell = sfa_cell;
myDescription = myCell.Description;

If you have a Requirements Toolbox™ license, you can also return the link information of the cell by using the getLinks function.

Adjust Cell Values

After using the self-reference to gather information from the cells, you can access the row and spreadsheet that contain the cell, and use the information about the cell, row, or spreadsheet to change the value of the cell.

For example, this code assigns the number of flags in the row that contains the cell to the cell value:

myCell = sfa_cell;
myRow = getRow(sfa_cell);
numberOfFlags = numel(getFlags(myRow));
sfa_derivedValue = num2str(numberOfFlags);

You cannot modify the cells, rows, or spreadsheets that you retrieve in the derived column formula.

Note

If you update the descriptions for cells, rows, or spreadsheets, you must refresh the derived cells to execute the script to use the new descriptions. To refresh the values, in Spreadsheet section, click Refresh Values.

Update Outputs in Derived Columns by Using Self-Referencing

Open Live Script

This example shows how to change the output of derived cells in Safety Analysis Manager spreadsheets by using self-referencing. If you update the column values, the derived columns automatically update the calculated value.

Inspect the Spreadsheet

Open the Safety Analysis Manager and the selfReferencingSpreadsheet.mldatx spreadsheet.

safetyAnalysisManager
safetyAnalysisMgr.openDocument("selfReferencingSpreadsheet.mldatx");

In this example, the spreadsheet calculates the final position of three objects moving in one dimension by using an initial position, velocity, acceleration, and time. After calculating the position, the spreadsheet checks if the final position is greater than 50, and if the row has a description.

The selfReferencingSpreadsheet.mldatx spreadsheet. The spreadsheet has four text columns and two derived columns. The text columns are white, and the derived columns are grey. The text columns contain numbers. the Final Position (m) derived column contains numbers. The Table Checks column contains textual descriptions.

View the Final Position (m) Column Code

To see the calculations used in the Final Position (m) column, right-click the column and click Edit Formula. The code retrieves the values in the first four columns and stores them by using the sfa_columnValue operator. If the values are empty, the code assigns the variables to 0. Then, the column calculates the final position, and assigns the calculated value to the derived cell.

% Retrieve values for calculation
x0 = str2double(...
    sfa_columnValue("Initial Position (m)"));
velocity = str2double(...
    sfa_columnValue("Velocity (m/s)"));
time = str2double(...
    sfa_columnValue("Time (s)"));
accel = str2double(...
    sfa_columnValue("Acceleration (m/s^2)"));

if isnan(x0)
   x0 = 0;
end
if isnan(velocity)
   velocity = 0;
end
if isnan(time)
    time = 0;
end
if isnan(accel)
    accel = 0;
end

% Calculate object final position
x1 = x0 + velocity*time + (1/2)*accel*time^2;
sfa_derivedValue = num2str(x1);

View Table Checks Code

Close the Column Formula Editor window and open the column formula for the Table Checks column. This column checks if each row has a description and if the final position is greater than 50, and outputs a message based on this evaluation. The code uses the getRow function and the sfa_cell keyword to get the row that contains each cell in the derived column.

myRow = getRow(sfa_cell);
x1 = str2double...
    (sfa_columnValue("Final Position (m)"));

if x1 > 50
    if myRow.Description == ""
        sfa_derivedValue = ...
    "Final position exceeds 50, " + ...  
    "but the row needs a description.";
    else
        sfa_derivedValue = ...
    "Final position exceeds 50, " + ...
    "and the row has a description.";
    end
else
    if myRow.Description == ""
        sfa_derivedValue = ...
    "Final position does not exceed 50, " + ...
    "but the row has a description.";
    else
        sfa_derivedValue = ...
    "Final position does not exceed 50, " + ...
    "and the row needs a description.";
    end
end

If you update the description, you must recalculate the derived column to use the updated description. To refresh the derived column values, in the Spreadsheet section, click Refresh Values.