We have introduced at new endpoint in our REST API called APIQuery. With this endpoint you can execute a predefined query against the database to get specific data.

Often when you need to get data from an API you need to get it from several endpoints which can involve many calls to the API where just to find out if there is data or not for a specific sub table or not.

To optimize that we invented the API Query where we at SmartTID, together with you, can define a query that will give you exact the data you need in each situation. When we add the query to the system you get a QueryKey in return that can be used every time you need to get these data.

So, if and when you need more specific data, please contact us at smartapi@smarttid.dk and we can create that query for you to use.

You need to be an approved partner or a customer to send this request and we will always get a confirmation from the customer to verify that we can create this query for their data.

Filter and pagination

Filters in the SmartAPI is either handled as filters within the API or filters handled by the database. This is handled internally by SmartTID based on what's best in the situation.

But with this APIQuery we have the option to choose since some queries with large datasets may benefit from a database filter while others by benefit from the "out of the box" API filter

FilterLocation

Choose between

API (default). The filter will be applied by the API on the final dataset returned from the database
Database. The filter will be passed to the database and handled there. This may require some info about the query behind the QueryKey to make this correct. This option is much more powerful and flexible with large datasets

Example

If the select behind the query would be

SELECT jl.EmployeeCode,
       jl.Date,
       jle.WorkTypeCode,
       jle.StartTime,
       jle.EndTime,
       jle.TotalTime
FROM dbo.JournalLine jl (NOLOCK)
     INNER JOIN dbo.JournalLineEntry jle (NOLOCK) ON jl.ID = jle.JournalLineID;

then an API filter could use any of, but only, the fields returned like

Date >= '20220101' AND WorkTypeCode = 'NORMAL'

the database filter could be the same but could also be more advanded and use other fields from the tables within the query. It's also a good idea to refer to the table aliases to avoid "Ambiguous column name"

jl.Date BETWEEN '20220101' AND '20220131' AND jle.WorkTypeCode = 'NORMAL' and jle.DimensionCode1 = 'P220'

sAction

EXECUTE is the only action supported

offset

IF you want to paginate the result set you can set the offset index for dataset

limit

The number og records you want returned after the offset

OrderFieldName

The field you want to "ORDER BY"

OrderDirection

Can be ASC for ascending (default) and DESC for descending

Filter

The actual filter which is a SQL style filtering both the API and Database filters

sQueryKey

The query key made by SmartTID

sParameter

New option from SmartTID version 7.0.

With sParameter you can send parameters to the API Query in a simple JSON format. It requires the API Query is prepared to handle the parameters.

And example to send a start- and end date.

[{"Parameter": "%STARTDATE%","Value": "20220101"},{"Parameter": "%ENDDATE%","Value": "20221231"}]

Return special formatet JSON

By default APIQuery returns a JSON array with all the fields from the SELECT statment. If you want the JSON to be formatet in a specific way we can obtain that like this

SELECT
    (
        SELECT jl.EmployeeCode,
               jl.Date,
               jle.WorkTypeCode,
               jle.StartTime,
               jle.EndTime,
               jle.TotalTime
        FROM dbo.JournalLine jl (NOLOCK)
             INNER JOIN dbo.JournalLineEntry jle (NOLOCK) ON jl.ID = jle.JournalLineID
        FOR JSON AUTO
    ) AS JSONResult;

Now the SQL statement from the query key is formatet as a JSON and retuned as a field named JSONResult. In this case only one field is retuned to the API and will be like this

[
    {
        "EmployeeCode": "1000",
        "Date": "2022-02-03T00:00:00",
        "jle": [
            {
                "WorkTypeCode": "NORMAL",
                "StartTime": "2022-02-03T07:00:00",
                "EndTime": "2022-02-03T19:00:00",
                "TotalTime": 12.0
            }
        ]
    },
    {
        "EmployeeCode": "1000",
        "Date": "2022-02-02T00:00:00",
        "jle": [
            {
                "WorkTypeCode": "NORMAL",
                "StartTime": "2022-02-02T07:00:00",
                "EndTime": "2022-02-02T19:00:00",
                "TotalTime": 12.0
            }
        ]
    }
]

Knowledge Base