Custom data support in Platina API

Background

Platina is a highly customizable product that exists on the market for years. During the long period, Platina customers implemented large number of customizations by using various approaches including basic ones, like using single predefined custom fields provided by Platina, but also non-trivial which involve custom database tables, functions, procedures and UI forms with custom controls. Since Platina exposes part of its functionality through the API, custom data support becomes one of the major and prioritized features that allows to integrate with other external systems that customers may use.

The purpose of this article is to describe how custom data for Platina objects can be defined and managed via Platina API. This article also includes an information of how to make custom data for existing non-trivial customizations being supported by Platina API.

Custom data in Platina

Platina supports few different ways to define and manage custom data, however please be aware that some of the mentioned below options are not supported by Platina API yet:

Use bk-fields that can accept single value. Amount of such fields are limited and have predefined names, e.g. bkStrNN, bkIntNN, bkDateNN, bkFloatNN, where NN is an integer value in range 1-30 or 1-10 depending on field and object type. Such fields are capable to store custom data of type string, integer, date or float respectively.
[NOT supported by Platina API yet] Use EF-fields (extended fields) that could store a single value of string type. Such fields have custom defined names, which should always be prefixed with EF_. Platina supports any amount of fields of that kind per object while they have a unique name (however it is worth to mention, that the more fields are defined for object, the more it could affect the Platina performance in a bad way).
Define custom data tables and stored procedures in platina_custom (or other) database. Implement custom business logic and UI (this approach is usually used in non-trivial customization scenarios).
Store arbitrary custom data for object in JSON format (currently supported only by Platina API).

Managing custom data using Platina API

bk-fields

bk-fields storage and retrieval is supported by Platina API out of the box. All that is required, is to send a valid request to the corresponding endpoint that handles particular object metadata create, read or update. For example, for journalized cases such endpoints are:

POST /api/v7/diaries/{id}/journalized-cases to create a journalized case including custom bk-fields.
GET /api/v7/journalized-cases/{id} to read the object metadata including custom bk-fields.
PATCH /api/v7/journalized-cases/{id} to update the object metadata including custom bk-fields.

This is the simplest option available to specify a basic custom data for object. One of the additional benefits for using bk-fields is that data, specified there, becomes automatically searchable in Platina.

Reference data

There could be cases when external applications (API clients) require some predefined data being returned from Platina and which is usually available for all authenticated users and can be used as the data source for dropdown or list fields on UI. Such data may include list of Countries, supported PUL and Secrecy levels, Case or Deed Types, whatever. Platina API already exposes endpoints that can be requested for standard reference data as mentioned before, or are capable to return custom data of such kind.

There are two general endpoints that can be requested for getting a list of defined standard or custom Platina reference data:

GET /api/v7/reference-data for standard reference data.
GET /api/v7/reference-data/custom for custom reference data.

Both return response in the same format which includes the following JSON properties:

href URL to endpoint that returns specific reference data.
rel identifier of the reference data to return.
method HTTP method required to request the reference data.

Example response for standard reference data request:

{
    "_links": [
        {
            "href": "https://api-server/api/v7/reference-data/countries",
            "rel": "countries",
            "method": "GET"
        },
        ...
    ]
}

Example response for custom reference data request:

{
    "_links": [
        {
            "href": "https://api-server/api/v7/reference-data/custom/CustomDocumentPrefixes",
            "rel": "CustomDocumentPrefixes",
            "method": "GET"
        },
        ...
    ]
}

To expose custom reference data, Platina API uses custom views with special RefData_ prefix that should be created in platina_custom database as part of Platina customization process. The view name that follows RefData_ prefix would be treated by Platina API as an identifier of custom reference data.

For example, if user makes HTTP GET request to https://api-server/api/v7/reference-data/custom/CustomDocumentPrefixes endpoint, then Platina API searches for view named RefData_CustomDocumentPrefixes, and if view is found, queries data from it and returns result JSON format.

Implementation example of custom reference data for Platina API:

We have a custom data table named tDocumentPrefixes that stores custom prefixes for documents. For this example, the table structure and data is the following:

ID	Prefix
1	DEV
2	REQ
3	QA
4	PM

The task is to expose data from that table via Platina API as reference data. We decided to use CustomDocumentPrefixes as reference data identifier. The next step would be to create a new view named RefData_CustomDocumentPrefixes in platina_custom database:

USE platina_custom
GO

CREATE VIEW RefData_CustomDocumentPrefixes
AS
SELECT [id], [prefix] FROM tDocumentPrefixes
GO

When the view is created, the HTTP GET request to https://api-server/api/v7/reference-data/custom/CustomDocumentPrefixes should return the data from the created view:

[
    {
        "id": 1,
        "prefix": "DEV"
    },
    {
        "id": 2,
        "prefix": "REQ"
    },
    {
        "id": 3,
        "prefix": "QA"
    },
    {
        "id": 4,
        "prefix": "PM"
    }
]

Implementation example of custom reference data for Platina API with multi-language support:

In some cases, reference data should be returned considering the preferred language setting for requesting user. It is also supported by Platina API. To have a multi-language support, the RefData_ view should include langID column that stores a language identifier for each row (e.g. 1 for Swedish, 2 for English languages, etc.).

To demonstrate such scenario, let's expose the Platina's standard DISCARD_REASON hierarchy register values by custom identifier DiscardReasons via Platina API. Implementation is quite simple - just create the following view named RefData_DiscardReasons in platina_custom database:

USE platina_custom
GO

CREATE VIEW RefData_DiscardReasons
AS
SELECT reg.[registerID], reglang.[text], reglang.[langID]
FROM platina.dbo.tHierarchyRegister reg
INNER JOIN platina.dbo.tHierarchyRegisterLanguage reglang
	ON reg.[registerID] = reglang.[registerID]
WHERE reg.[registerName] = 'DISCARD_REASON'

If select data from the created view, it should look like follows:

registerID	text	langID
26001	Dubbelregistrerat	1
26001	Double registered	2
26001	Dobbeltregistrering	3
26001	Dobbelregistrert	4
26002	Felregistrerat	1
26002	Wrongly registered	2
26002	Fejlregistrering	3
26002	Feilregistrert	4
26003	Testdata	1
26003	Test data	2
26003	Testdata	3
26003	Testdata	4

Make an HTTP GET request to https://api-server/api/v7/reference-data/custom/DiscardReasons endpoint. Depending on the selected active language for the Platina user, which account is used by Platina API, the response may look as follows:

For Swedish language:

[
    {
        "registerID": 26001,
        "text": "Dubbelregistrerat"
    },
    {
        "registerID": 26002,
        "text": "Felregistrerat"
    },
    {
        "registerID": 26003,
        "text": "Testdata"
    }
]

For English language:

[
    {
        "registerID": 26001,
        "text": "Double registered"
    },
    {
        "registerID": 26002,
        "text": "Wrongly registered"
    },
    {
        "registerID": 26003,
        "text": "Test data"
    }
]

As it can be seen, it is quite simple to expose custom reference data via Platina API without doing any code changes in API itself. You can create as many views for custom reference data as required.

Arbitrary custom data for object in JSON format

Platina API provides an easy way store and retrieve custom data in JSON format. It may be very useful when external cliens want to associate and access additional metadata with the Platina object without any additional development efforts on Platina API side. At the moment of creation of this article the following endpoints can be used to manage custom data of such kind:

For documents: /api/v7/documents/{id}/custom-data
For deeds: /api/v7/deeds/{id}/custom-data
For journalized cases: /api/v7/journalized-cases/{id}/custom-data

Each of mentioned above endpoints accepts HTTP GET or PUT requests to retrieve or store the custom data for object respectively.

Arbitrary custom data usage example

This section describes a basic scenario for associating and retrieving custom data with Platina object. For example purposes, let's assume the object's custom data is the following:

{
    "customString": "Example string value",
    "customInt": 1234,
    "customFloat": 4.321,
    "customDate": "2024-08-20T00:00:00",
    "customArray": [1, 2, 3, 4, 5],
    "nestedData": {
        "nestedString": "Nested data string",
        "nestedArray": ["a", "r", "r", "a", "y"]
    }
}

Assuming that Platina already has a journalized case with ID that equals to MWz63LAym9Q, it is possible to send an HTTP PUT request with custom data object to /api/v7/journalized-cases/MWz63LAym9Q/custom-data endpoint. In case if custom data was successfully associated, the Platina API will respond with HTTP 200 OK response.

Now let's send an HTTP GET request to /api/v7/journalized-cases/MWz63LAym9Q/custom-data endpoint. Platina API shall respond with the same custom data JSON object that was sent with previous PUT request:

{
  "customArray": [
    1,
    2,
    3,
    4,
    5
  ],
  "customDate": "2024-08-20T00:00:00",
  "customFloat": 4.321,
  "customInt": 1234,
  "customString": "Example string value",
  "nestedData": {
    "nestedString": "Nested data string",
    "nestedArray": [
      "a",
      "r",
      "r",
      "a",
      "y"
    ]
  }
}

Tech details about storage of custom JSON object in Platina database

When a valid request to associate a custom data in JSON format with Platina object is received by Platina API, the Platina API splits the data and preserves it in tCustomData table in platina_custom database. The tCustomData table has the following columns:

Column	Description
propID	`PropID` of the object which to associate the custom data with.
customDataIdentifier	Identifier for the stored custom data value.
kind	Kind of custom data. By default equals to `JSON`, but could be also `REF` or `REF_RO` which will be described in details in the next chapter.
customData	Custom data value for `JSON` kind, or `NULL` for `REF` and `REF_RO`.

As it was mentioned recently, Platina API splits received custom data object before storing it in the database. Such action is necessary for better handling of custom data that is associated with the object, which may include using SQL queries with JSON that require only portion of custom data, or support of non-trivial or existing customization scenarios which will be described further in this article. The data split is done for root custom data JSON object by its properties (all nested objects or arrays will be treated as JSON values). Below is an illustration of how the object's example custom data from parent chapter will be stored in tCustomData table:

propID	customDataIdentifier	kind	customData
#PROPID#	customString	JSON	"Example string value"
#PROPID#	customInt	JSON	1234
#PROPID#	customFloat	JSON	4.321
#PROPID#	customDate	JSON	"2024-08-20T00:00:00"
#PROPID#	customArray	JSON	`[1,2,3,4,5]`
#PROPID#	nestedData	JSON	`{nestedString":"Nested data string","nestedArray":["a","r","r","a","y"]}`

When Platina API receives a GET HTTP request to retrieve the custom data for an object, it queries associated data from tCustomData table by object's propID, then composes a JSON object from result and sends the composed JSON in response.

Support of existing and non-trivial customizations

This article already covered various options for handling custom data via Platina API, however there is still a gap in description of how the existing custom data or non-trivial customizations can be supported by Platina API. This chapter is inteded to close this gap and provide details and steps for implementing such support.

Let's consider the follwing example case: there is already an implemented customization that allows to specify for journalized case an additional information about organization that it currently belongs to. To not overcomplicate the example, custom data will include organization's Name andAddress that are stored in tExampleOrganization table of platina_custom database:

propID	name	address
#PROPID#	Example Organization	Example str, 123

Make custom data available via Platina API in read only mode

Let's begin with the requirement to make custom information about organization available via Platina API in read only mode (read only mode in this context means that such data can be retrieved but cannot be modified via Platina API) and be provided as a value of ExampleOrganization property of returned custom data JSON object.

There are two possible ways to implement this requirement:

By creating a custom view in platina_custom database with CustomData_ prefix (e.g. CustomData_ExampleOrganization) that selects data from custom table(s) and that has propID field that stores object's ID with which the returned custom data is associated.
By implementing a custom stored procedure which name should start with pCustomData_ and end with _Get, e.g. pCustomData_ExampleOrganization_Get.

Important: Despite of chosen approach, the final step would be to register a reference to the set of custom data in tCustomData table of platina_custom database (details about how to perform this are provided further in this article).

Note in case if both view and stored procedure were implemented for the same set of custom data, then Platina API will choose stored procedure as a source of custom data, which means that view will not be queried at all.

Create a `CustomData_ExampleOrganization` view

If follow the first option with custom view, then it is necessary to create a new view named CustomData_ExampleOrganization in platina_custom database:

USE platina_custom
GO

CREATE VIEW CustomData_ExampleOrganization
AS
SELECT [propID], [name], [address] FROM tExampleOrganization
GO

and register an ExampleOrganization custom data identifier in tCustomData table of platina_custom database as read only reference:

propID	customDataIdentifier	kind	customData
#PROPID#	`ExampleOrganization`	REF_RO	NULL

Create a `pCustomData_ExampleOrganization_Get` procedure

In more non-trivial customization scenarios (user permission checks, multi-language support, etc) the option with custom stored procedure may be preferable. In addition to procedure naming requirements, the procedure must accept the following parameters that will be provided by Platina API when calling the procedure:

@propID INT - PropID of the object for which to retrieve the custom data.
@usrPropID INT - PropID of the user that makes a request to Platina API.
@langID INT - LangID for the user that makes a request to Platina API.

Considering this, let's create a stored procedure that returns an organization custom data for object:

CREATE PROCEDURE [dbo].[pCustomData_ExampleOrganization_Get]
	@propID INT,
	@usrPropID INT,
	@langID INT
AS
BEGIN
	SELECT [name], [address] FROM tExampleOrganization
	WHERE [propID] = @propID
END

and register an ExampleOrganization custom data identifier in tCustomData table of platina_custom database as read only reference:

propID	customDataIdentifier	kind	customData
#PROPID#	`ExampleOrganization`	REF_RO	NULL

Note the custom data identifier registration step is the same as in case with custom view.

Add support to change the custom data via Platina API

The previous chapter contains details of how to expose data from custom tables via Platina API. In many cases it is not enough, and usually it is also required to modify such data. This chapter contains a guide of how to make it possible.

Let's evolve an example from previous chapter and implement possibility to change the organization's data via Platina API an additionally storing the previous values in tExampleOrganizationHistory table of platina_custom database. Currently, there is only one option to achieve that - implement a custom stored procedure. Similarly to read only custom data procedure case, the stored procedure name should start with pCustomData_ but end with _Set, e.g. pCustomData_ExampleOrganization_Set. The stored procedure must accept the following default parameters:

@propID INT - PropID of the object for which to retrieve the custom data.

@usrPropID INT - PropID of the user that makes a request to Platina API.

Important Additionally, stored procedure must accept custom parameters that store custom data values from JSON object that will be sent in request body to Platina API. For better clarity, refer to example below where on the left there is a JSON object with custom data, and on the right - parameters of custom stored procedure that handles data from JSON object (such parameters should have @p prefix and corresponding data type):

JSON	Stored Procedure Parameters
{ "customInt": 123, "customFloat": 4.56, "customString": "custom value", "customDateTime": "2024-08-20T00:00:00" }	@propID INT, @usrPropID INT, @pcustomInt INT, @pcustomFloat FLOAT, @pcustomString NVARCHAR(MAX), @pcustomDateTime DATETIME

JSON

Stored Procedure Parameters

  {
      "customInt": 123,
      "customFloat": 4.56,
      "customString": "custom value",
      "customDateTime": "2024-08-20T00:00:00"
  }

  @propID INT,
  @usrPropID INT,

  @pcustomInt INT,
  @pcustomFloat FLOAT,
  @pcustomString NVARCHAR(MAX),
  @pcustomDateTime DATETIME

Assuming that custom data update request to update organization data for object looking as follows:

{
    "ExampleOrganization": {
        "name": "Example Organization",
        "address": "Example str, 123"
    }
}

Then custom stored procedure must accept parameters named @pname and @paddress (@p-prefixed JSON object's property names) and may look like follows:

CREATE PROCEDURE [dbo].[pCustomData_ExampleOrganization_Set]
    @propID INT,
    @usrPropID INT,
    @pname NVARCHAR(50),
    @paddress NVARCHAR(50)
AS
BEGIN
    IF NOT EXISTS (SELECT * FROM tExampleOrganization WHERE [propID] = @propID)
    BEGIN
        INSERT INTO tExampleOrganization ([propID], [name], [address])
        VALUES (@propID, @pname, @paddress)
    END
    ELSE
    BEGIN
        INSERT INTO tExampleOrganizationHistory ([propID], [timestamp], [name], [address])
        SELECT [propID], CURRENT_TIMESTAMP, [name], [address] FROM tExampleOrganization
        WHERE [propID] = @propID

        UPDATE tExampleOrganization SET
            [name] = @pname, [address] = @paddress
        WHERE [propID] = @propID
    END
END

By exploring the stored procedure code it is easy to identify, that it covers non-trivial customization scenario - stores the history of changes in separate table. When procedure is created, it is necessary to update already existing ExampleOrganization registration (which was added in example for previous chapter) from REF_RO to REF kind. That will instruct Platina API that it can accept request to change the referenced custom data:

propID	customDataIdentifier	kind	customData
#PROPID#	ExampleOrganization	`REF`	NULL

And finally let's also expose the history of changes in organization for object in ExampleOrganizationHistory property of custom data JSON object. To achieve that, let's create a custom procedure pCustomData_ExampleOrganizationHistory_Get:

CREATE PROCEDURE [dbo].[pCustomData_ExampleOrganizationHistory_Get]
	@propID INT,
	@usrPropID INT,
	@langID INT
AS
BEGIN
    SELECT [timestamp], [name], [address] FROM tExampleOrganizationHistory
    WHERE [propID] = @propID
    ORDER BY [timestamp] ASC
END

Since history data is not intended to be modifieable, we can register it as read only reference in tCustomData table:

propID	customDataIdentifier	kind	customData
#PROPID#	ExampleOrganization	`REF`	NULL
`#PROPID#`	`ExampleOrganizationHistory`	`REF_RO`	`NULL`

Registering custom data reference in `tCustomData` table

As mentioned earlier in this article, tCustomData table has a kind field that could be JSON, REF or REF_RO. To register a custom data identifier as reference to view or stored procedure as data source, the REF or REF_RO kind is used:

REF for registering a data source that supports both read and edit data.
REF_RO for registering a data source that only provides data that can be read.

Useful tips

Since REF and REF_RO custom identifier registrations shall exist for any object that could have associated custom data from custom tables, Platina sequences can be useful to automatically add such registrations on object creation.