Transform Data in a Column

Transform Data in a Column

In the screen designer, you can dynamically transform data in columns. This can help when the data stored in the table, does not match the format you’re hoping to present to the user. In this example, we will highlight how we manage to convert a ‘duration’ stored in a Workcenter History table in Milliseconds, to Minutes, and also round the value to 2 decimal places. You can also dynamically add text to these cells like “uom” by changing the display type to text, rather than an integer or float.

Best Practice: IF you need to perform transforms on your data, whether in a Table, Form, Cards or otherwise - it is best if you do this at the ‘top level’ of the component. Meaning, while we give details on how to transform data within a single table cell, we suggest doing this at the Table (Data Transform) as your screen will perform better.

Instructions






  1. Navigate to the Screen Designer
  2. Locate teh Table Screen taht you wish to modify (open or create a new one)
  3. within the canvas area - select the table element
  4. After you have selected the table containing the column you want to modify - find the Additional Fields section and add the field you want to modify to the additional fields - in this example we've added "duration".
  5. Now 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 this to advanced using the </> to the top right, which then exposes additional properties for the field to be edited
    2. Dynamic Fields - this need to contain a reference to the payload from the table - in this example what we need is the “duration” data from the query
    3. 1
      2
      3
      4
      5
      6
      {
      "payload": [
      "data.duration"
      ],
      "context": []
      }
  7. Now you need to tell the system what you want to do with this payload data, now that it knows the specific data you’re referencing - In the “Actions” transform window, you can enter a jsonata transform that will use the data you’re providing it and format it/ perform calculations and even add labels, text, etc.
    1. 1
      $round($sum((data.duration)/60000),3) & " Min"

*since the data is stored in Fuuz in milliseconds, we want to convert that to show the user ‘minutes’ - so we take the data.duration divide it by 60,000 (which gives us minutes), then we use the $round function to limit the decimal places to 3.
We have also add the & “ Min” which adds to the end of the result of the first part of the expression.

In doing this operation we are not modifying the data as its stored in the table - only when displayed. Keep this in mind when building reports, if you aggregate raw data - it maybe a higher precision than what you are displaying and therefore may appear to be incorrect.


In this example, the “duration” data stored in the table is a float value - we need to change the display from ‘float’ to ‘text’ otherwise the “Min” we want to display will not appear in the table. The transform will still perform the calculation however. Find this option under General>Format for the cell.

End Result:


    • Related Articles

    • Display

      Screen Elements in this category help create a variety of elements and information to display on your screen.

    • Text (Display Text)

      The text display element can be used in the Screen Designer by dragging the element into the Screen Designer Canvas from elements under display. Within the 'Basic' editable properties in the Editor Panel, the text can be customized to have an default ...

    • Interaction

      Screen Elements in the Interaction category provide quick components that allow the user to interact with the screen.

    • Format a Cell Background Color based on Data

      In the screen designer, you can conditionally format the background color of a table cell by following these steps. This example covers the scenario when you have a Data Model that defines a color code - typically something like a record status. ...

    • Screen Widget

      A Screen Widget is a screen element that allows you to add screens saved as Screen Widgets to a new screen. Use A Screen Widget Element The very first thing you will want to do is build a screen in the Screen Designer and then make sure to either go ...