Transform Data in a Column

Transform Data in a Column

In the Screen Designer, you can dynamically transform data in columns. This helps when the data stored in the table does not match the format you want to present to the user. In this example, we convert a duration stored in a Workcenter History table in milliseconds to minutes, and round the value. You can also dynamically add text to these cells (like a unit of measure) by changing the display type to text rather than an integer or float.

Note: Best practice — if you need to perform transforms on your data, whether in a Table, Form, Cards, or otherwise, do this at the top level of the component. While this article shows how to transform data within a single table cell, doing it in the Table's Data Transform performs better.

Instructions

1. Open the Screen Designer

2. Locate the table screen that you wish to modify (open or create a new one)

3. Within the canvas area, select the table element

4. With the table containing the column you want to modify selected, find the Additional Fields section and add the field you want to modify — in this example we've added duration

   

5. Go back to the canvas and select the column you're modifying

   

6. Scroll to the Advanced section:

   1. Find the Data option and flip it to advanced using the </> toggle at the top right, which exposes additional properties for the field

   2. Dynamic Fields — this needs to contain a reference to the payload from the table; in this example we need the duration data from the query:

      {
        "payload": [
          "data.duration"
        ],
        "context": []
      }

7. Now tell the system what to do with this payload data. In the Actions transform window, enter a JSONata transform that uses the data you're providing and formats it, performs calculations, and even adds labels or text:

   $round($sum((data.duration)/60000),3) & " Min"
   

Since the data is stored in Fuuz in milliseconds, we convert it to show the user minutes — we take data.duration and divide it by 60,000 (which gives us minutes), then use the $round function to limit the result to 3 decimal places. We also append & " Min", which adds the label to the end of the result of the first part of the expression.

Note: This operation does not modify the data as it is stored in the table — only how it is displayed. Keep this in mind when building reports: if you aggregate raw data, it may be higher precision than what you display and therefore may appear incorrect.

Note: In this example, the duration data stored in the table is a float value. You need to change the display format from float to text, otherwise the " Min" label will not appear in the table (the transform still performs the calculation). Find this option under General > Format for the cell.

---

End result:

See Also

• Table-Column-Conditional-Formatting
• Using-the-Transformation-Explorer
• Jsonata-Tutorial
• Numeric-Functions
• The-Screens-Screen

---

Source: support.fuuz.com