Use JSON to work with complex data in custom fields

June 27, 2020. Stefan Willebrand.

Many times when we extend the use of Bricknode Financial Systems (BFS) by building applications on top of it using the API we end up with the need for more property fields. BFS has a lot of dedicated fields but when we need more we can use Custom Fields! Every object in BFS has Custom Fields, it is part of the Entity Base class.

Custom Fields store data in simple string format and in the old days we might have worked with for example semicolon separated values. During the last couple of years we have changed this behaviour and began working with JSON data in Custom Fields instead. This way the data in Custom Fields both become more easy to read and edit in the regular GUIs and for developers the data is more structured.

We have a plan to integrate a GUI editor for fields that contain JSON which will make the life easier for non-developers but for now GUI users can work with tools like Excel that can be converted to JSON format with tools like this one for example: http://beautifytools.com/excel-to-json-converter.php

Now onto an example. I am building a new application that will take care of recurring fees based on assets under management in BFS.

I do not want to set up a separate database for this application but instead use BFS for all my data needs. There are a few things that I need to take care of.

  • Storing some general settings data on the house
  • Store data about fee levels and for what period fees have been applied to individual accounts

I am calling this application RecurringAumFees and thus it is good practice to name the Custom Fields with this prefix.

To communicate with BFS I am using our NuGet package.

Let’s have a look at the class that I call RecurringAumFee_HouseSettings.

/// <summary>
///     This model is used to collect data from the House in the BFS instance from the custom field called
///     RecurringAumFees_HouseSettings
/// </summary>
public class RecurringAumFees_HouseSettings
{
    /// <summary>
    /// This is the interval of how often the fee should be applied
    /// </summary>
    public FeeInterval FeeInterval { get; set; }
    /// <summary>
    /// This is the date when fees were last executed
    /// </summary>
    public DateTime LastFeeExecutionDate { get; set; }
    /// <summary>
    /// This is the first date that fees should be executed
    /// </summary>
    public DateTime FirstFeeExecutionDate { get; set; }
}

The FeeInterval property is an enum looking like this:

public enum FeeInterval
{
    Monthly,
    Quarterly,
    SemiAnnually,
    Annually
}

As a GUI user I can put this data into an Excel sheet according to the template below.

RecurringAumFees_HouseSettings

I can then visit the conversion tool and import the Excel file and then extract the following data.

{
 "FeeInterval": "1",
 "FirstFeeExecutionDate": "3/31/20"
}

I navigate to the House View in Bricknode Broker.

On to the details section.

And create a new Custom Field where I paste the JSON formatted data.

The result:

Now in my application I can just collect the house data and deserialize the Custom Field values into a C# object to work with it like this.

public async Task GetRecurringAumFeesHouseSettings()
{
    var houseData = await _bfsLegalEntitiesService.GetHouseInformationAsync("[yourinstancename]");

    var recurringAumFeesHouseSettings = houseData.Result.First().CustomFields
        .First(t => t.FieldName == "RecurringAumFees_HouseSettings").Value;

    var recurringAumFeesHouseSettingsObject =
        JsonConvert.DeserializeObject<RecurringAumFees_HouseSettings>(recurringAumFeesHouseSettings);
}

The data that I got back looked like this.

In my humble opinion this is the absolute best way to work with complex Custom Fields in BFS and not have to create one Custom Field per value or use generic separators.