Adding or Updating Data Source Rows

The WriteData function is used to pass data between KnowledgeKube and a data source table. Depending on how the expression is written it can either add a new row of data or update an existing one.

The function’s syntax is as follows:

WriteData( NewData, RowID, DataSource, GenerateIndex, DateFormatString, OptimiseIndex );

The GenerateIndex argument is optional, and DateFormatString is only mandatory if the data you want to write away contains at least one date column. OptimiseIndex is optional, but has no effect unless GenerateIndex is set to True.

NewData is a string containing a comma-separated list string containing the aliases of the columns you want to add data to.

RowID is used differently depending on whether you want to update an existing data row or add a completely new one. For the function to update a row, this argument must be a string containing the alias of your data table's primary key. To add a new row, leave this argument as an empty string.

DataSource is a string containing the name of the data source to which you want to use to write the data.

When using the Expression Editor interface, you can display a list of data sources available to your application by pressing the Alt and down arrow keys on your keyboard.

GenerateIndex is a Boolean that determines whether the data source's index should be rebuilt after the new data is added. If no argument is provided, this parameter will use a value of "False" by default.

DateFormatString is only needed if the data being written contains one or more date columns. The value used for this parameter must be exactly as written below:

"{0:yyyy-MM-ddTHH':'mm':'ss}"

OptimiseIndex is a Boolean that determines whether the data source index should be optimised. This has no effect if GenerateIndex is set to False. If no argument is provided, this parameter will use a value of "False" by default.

You can use the function's syntax to write the expression manually but there is also the option of using the Data Source Expression wizard to quickly generate the WriteData expression.

Before implementing either of these options make sure you have created a writeable data source, and its Fields you want to receive data have been given Aliases.

Use the implementation steps below to automatically generate the WriteData function.

Implementation:

  1. Open the Expression Editor.
  2. If the expression already has some content, place the keyboard cursor where you want the new statement to appear.
  3. Right-click anywhere in the main Expression panel.
  4. Select Create Data Source Expression.
  5. Use the Data Source drop-down menu to select the data source whose rows you want to add or update.
  6. If you want the WriteData statement to add new rows to the target table, select the Add New Entry to Your Data Source radio button. If you want it to update existing rows, select Update Your Data Source.
  7. Click Next.
  8. Using the check boxes, tick each of the fields you want your statement to pass values into.

If you click the check box at the top of the dialog, all of the fields in the source can be ticked, or unticked, at once.

  1. If you selected Update Your Data Source, use the Identity Alias Parameter drop-down menu to select the column that will identify the row to update. This is usually the primary key of the target table. Ensure the check box for this field is not ticked.
  2. Click Next.
  3. If you want to generate additional statements for assigning values to your data source's aliases before writing those values away, select This Data Source Expression Will Populate the Alias Values. Otherwise, you should leave the first radio button selected and ensure that the aliases get their values from elsewhere in your model.

If you do select the second radio button, you'll need to assign an expression to each of the aliases in the list. Each of these expressions will assign its result to the respective alias. You can either type an expression directly into the Value column for an alias, or select the alias and use the text box at the bottom of the dialog to write an expression - using the drop-down menu to pick a standard function if needed - before clicking the Apply button to copy what you've written into the alias table.

  1. Click Finish.
  2. Save the expression before closing the expression editor.