Adding a Column with Custom Content

This page describes how you can add a column to the grids which contains data calculated on the fly.

Use Case

We want to add a column with the title “PlayTime” which displays the played-out length of a piece of audio. We define this value as the time difference between start and stop, where start is the MarkIn value if the entry has a MarkIn, or the start of the audio otherwise, and likewise, stop is the MarkOut value if it exists, and the end of the audio otherwise.

Step by Step

Define the Custom Column in Admin

In the Digas Admin tool, navigate to Local Settings\DBM\CustomSearch (if do this in Global Settings it would affect all users, which may not be what you want).

Create a string value named #/PLAYTIME with the value ^PlayTime.

Restart DBM.

Display the column

In DBM, go to main menu → Settings → Columns. Under “Entry view”, scroll down to the “PlayTime” item, and click the empty rectangle to the left of this item. This places a checkmark into the rectangle, indicating that the new column is configured into the grid.

Click “OK”. The new column is added at the far right of the “ENTRIES” grid. You may want to drag it to a more prominent position. However, the column is still empty.

Create and Connect the Skeleton of an Event Script

Now we must create a script which generates the column content on the fly, and connect it to the “PreDrawGridField” event. Let’s start by creating a do-nothing script and connecting it to the event, so that we can put functionality into the script later and immediately see its effect.

We must decide for a name for the script. We call it “PreDrawGridField_PlayTime”.

Using Digas Admin, we navigate to Local Settings\DBM where we create the Macros folder (unless it already exists). In the Macros folder, we create the string value PreDrawGridField_PlayTime, and leave its content empty.

Then, in the same Macros folder, we create the string value InitMacroEvents, and set its content to

AddEvent("PreDrawGridField", "PreDrawGridField_PlayTime", "ENTRIES", "#/PLAYTIME")

If the InitMacroEvents value already exists, we add this command to a separate line after the last line of the existing content.

Restart DBM.

The PlayTime column remains empty, but behind the curtains, DBM calls the “PreDrawGridField_PlayTime” script every time it needs to redraw the “PlayTime” field in any grid row.

Populate the Event Script

We want the event script to do something meaningful. Let’s start simple and progress by and by.

Create Output

In Digas Admin, go to the Macros folder where the “PreDrawGridField_PlayTime” parameter was created, and set its content to

return { "mode": "text", "text": "Hello world" }

There is no need to restart DBM; just click the “refresh” button. Now the “PlayTime” column displays “Hello world” in all rows.

As you may have guessed, the point is to return a JSON statement with the fields “mode” and “text”, where “mode” is set to “text”, and “text” contains the actual field content. Creating a JSON statement can be a bit tedious, but helpers are at your disposal. Let’s refine this basic script a bit. This is the next version:

%js = json_Set("", "/mode", "text")
%js = json_Set(%js, "/text", "Hello world 2")
return %js

The “json_Set” function helps you to create and modify JSON statements. The parameters, in order, are: The original statement, the JSON path to the node you want to create or modify, and the value you want to set.

To insert a newline in the content of a configuration value, press Ctrl+Enter.

After clicking “refresh”, DBM will display the new text.

Retrieve MarkIn and MarkOut

For the new column to contain meaningful values, we first must get the MarkIn and MarkOut values from the database. The following lines do this, and display the retrieved values in the “PlayTime” column:

%mark_in = GetGridFieldContent($Grid, $RowIndex, "MarkIn", RAW)
%mark_out = GetGridFieldContent($Grid, $RowIndex, "MarkOut", RAW)
%text = string(%mark_in) + "/" + string(%mark_out)
%js = json_Set("", "/mode", "text")
%js = json_Set(%js, "/text", %text)
return %js

After another refresh, the “PlayTime column has dramatically changed. However, it might not be quite what you expected. Specifically, the value 21212121 which is displayed in many lines looks strange. In fact, this value means “not defined”, and other numbers are in milliseconds. For example, a value of 4401960 means 1 hour, 13 minutes, 21 seconds, and 960 milliseconds.

The “RAW” parameter is responsible for returning the values as milliseconds, rather than as a human-readable string like “01:13:21.960”. The milliseconds value is fine in this situation because it is much easier to use in arithmetic operations.

We must filter out the 21212121 values and replace them by 0 or by the total length of the audio:

%mark_in = GetGridFieldContent($Grid, $RowIndex, "MarkIn", RAW)
%mark_out = GetGridFieldContent($Grid, $RowIndex, "MarkOut", RAW)
if (%mark_in == 21212121)
{
  %mark_in = 0
}
if (%mark_out == 21212121)
{
  %mark_out = GetGridFieldContent($Grid, $RowIndex, "Duration", RAW)
}

%text = string(%mark_in) + "/" + string(%mark_out)
%js = json_Set("", "/mode", "text")
%js = json_Set(%js, "/text", %text)
return %js

Now the output looks much better, but the magic 21212121 remains in a few places where no duration is defined. Let’s remove these, and display the difference of both values in other cases:

%mark_in = GetGridFieldContent($Grid, $RowIndex, "MarkIn", RAW)
%mark_out = GetGridFieldContent($Grid, $RowIndex, "MarkOut", RAW)
if (%mark_in == 21212121)
{
  %mark_in = 0
}
if (%mark_out == 21212121)
{
  %mark_out = GetGridFieldContent($Grid, $RowIndex, "Duration", RAW)
  if (%mark_out == 21212121)
  {
    return
  }
}

%play_time = %mark_out - %mark_in
%text = string(%play_time)
%js = json_Set("", "/mode", "text")
%js = json_Set(%js, "/text", %text)
return %js

Format the Output

Now this is very close to what we want to see. Let’s remove the zeroes from the column and format the other values properly. The latter is a somewhat tedious task.

%mark_in = GetGridFieldContent($Grid, $RowIndex, "MarkIn", RAW)
%mark_out = GetGridFieldContent($Grid, $RowIndex, "MarkOut", RAW)
if (%mark_in == 21212121)
{
  %mark_in = 0
}
if (%mark_out == 21212121)
{
  %mark_out = GetGridFieldContent($Grid, $RowIndex, "Duration", RAW)
  if (%mark_out == 21212121)
  {
    return
  }
}

%play_time = %mark_out - %mark_in
if (%play_time == 0)
{
  return
}

%secs = %play_time / 1000
%milli = %play_time - %secs * 1000
%mins = %secs / 60
%secs = %secs - %mins * 60
%hours = %mins / 60
%mins = %mins - %hours * 60

function format_number(%num, %digits)
{
  %value = string(%num)
  while (strlen(%value) < %digits)
  {
    %value = "0" + %value
  }
  return %value
}

%text = format_number(%hours, 2) + ":" + format_number(%mins, 2) + ":" + format_number(%secs, 2) + "." + format_number(%milli, 3)
%js = json_Set("", "/mode", "text")
%js = json_Set(%js, "/text", %text)
return %js

By defining the “format_number” function in the script, we have avoided the need to repeat 4 times the code to emit at least 2 (or 3) digits.

Finally, we’re almost done. Refresh the display of DBM and enjoy your first custom column.

Make the New Column “Read Only”

Select a line in the “ENTRIES” grid, and click the “PlayTime” column in this line. An input field with a flashing cursor appears, indicating you can input text here. This, of course, is rubbish. We are going to disable this now.

With Digas Admin, edit the content of InitMacroEvents value which you have created or modified during the previous steps on this page, and add this line:

AddEvent("PreDrawGrid", "PreDrawGrid_PlayTime", "ENTRIES")

Then, in the same folder, create another string value named PreDrawGrid_PlayTime, and set its value to

return {"inplace-edit": { "#/PLAYTIME": "disabled" } }

You need to restart DBM to see that inline editing of the “PlayTime” field is now disabled.

The Forgotten Grids

What we created works find in the “ENTRIES” grid. But we have other grids too, and we want them to behave like the “ENTRIES” grid. So let’s return to the InitMacroEvents configuration entry, and modify it like this:

AddEvent("PreDrawGridField", "PreDrawGridField_PlayTime", "ENTRIES, SEARCH_RESULT, GROUP_MEMBERS, COLLECTION_MEMBERS", "#/PLAYTIME")
AddEvent("PreDrawGrid", "PreDrawGrid_PlayTime", "ENTRIES, SEARCH_RESULT, GROUP_MEMBERS, COLLECTION_MEMBERS")

After another DBM restart, all grids behave the same.