Extended calculated field formulas

InstaDB provides full SQL support, what makes possible to use new kinds of formulas. The most important are:

reference formulas

can be used to reach values of records pointed by the references using the arrow (->) syntax;

aggregation formulas

let for finding a count, sum, minimum or maximum of records referencing the current record; they can by written in the SQL or build with the dedicated editor;

other SQL formulas

almost all SQL functions and expressions are available in the InstaDB, so there is no limit of values you can calculate;

field formulas returning reference values

This feature dramatically increase the power of expression columns.

Sign Up Free

to test InstaDB in action

I accept Terms and Conditions

What do our clients say about InstaDB?

The usage of the platform is simple and intuitive. We were able to establish and develop a dedicated application by ourselves, without the need of engaging any IT specialists.

We are fully satisfied with the InstaDB platform provided by Atinea Sp. z o. o. and anticipate to use it in the future works. We believe that the platform is an excellent choice for everyone who needs an advanced but simple dedicated database system.

Lena Svensson
Group Leader, Associate Professor at Lund University

The InstaDB platform is a stable and reliable solution on which we can build our administration.

Łukasz Karasek
IT Director at Vestor Dom Maklerski S.A.

Atinea has deployed the InstaDB system at our company. It covers the complaints workflow. The workflow has been fully adapted to the Łucz-Bud procedures and keeps its high standards as delivered by the InstaBD platform.

Krzysztof Mizera
Vice President of P.H.U.B "Łucz-Bud" Sp. z o.o.

Our group successfully launched a customized database using InstaDB platform. We have implemented a database of antibodies, plasmids, siRNA, protocols and other resources used in the laboratory on a daily basis. The whole process of development was performed by a scientist without an IT background, what proves that the InstaDB platform is easy and intuitive.

We are deeply satisfied with the application built on the InstaDB platform. As scientists we must use tools which are reliable and efficient. InstaDB is definitely one of those tools.

Anna Hupalowska
Lab Manager at Professor Magdalena Zernicka-Goetz Group,
University of Cambridge

The Teatr Wielki - Polish National Opera has used the InstaDB platform of Atinea Sp. z o.o. since 2013. The applications implemented by Atinea are electrical equipment record system, computer hardware inventory system, room registration system and library management system.

All the above solutions have been implemented very carefully and according to the specific needs of different organizational units of The Teatr Wielki. It is worthwhile to emphasize that the InstaDB platform allows us to create our own applications and our IT department uses it for creating new modules for our own custom needs.

Krzysztof Płatek
Deputy Director for Administration, The Teatr Wielki - Polish National Opera

A&J Vacuum Services is a trusted worldwide leader in sales and service of high vacuum equipment. With over a decade of experience, A&J Vacuum Services is fully dedicated to the highest quality of customer satisfaction. Atinea has designed, implemented and deployed our workflow system using InstaDB technology. The system covers all key processes, providing functionalities of CRM, WMS and PTS.

Artur Stencel
President of A&J Vacuum Services, Inc.

Atinea has deployed the InstaDB system since 2015. It covers document workflow, archive and task management. The system has been implemented according to our directives – reflecting and enhancing our work with paper documents. The system works in Atinea's cloud and is under constant supervision of the company.

The implemented solution has greatly influenced the work of our entire team, noticeably improving the efficiency of our staff and reducing the number of problems. The system works well and we are pleased with the quality of hosting and technical support offered.

Iwona Majek
President's Legal Representative at Alpha Dam Sp. z o.o.

SharePoint formulas vs. InstaDB formulas

Here we present some examples of InstaDB expression syntax. If it's possible, the examples are accompanied by the corresponding MS SharePoint calculated field formulas for comparison.

InstaDB expression types not available in MS SharePoint

The examples below present formulas which are common in relational databases but are missing in the plain MS SharePoint.

Reference Formulas

The reference formulas are used to achieve data from related records pointed by references. InstaDB provides two kinds of reference formulas syntax: classic SELECT constructions and the arrows (->), which can be used alternatively, what is demonstrated.

All the below formulas are executed from the perspective of a record (they are context dependent). In the following cases the context is an Employees table record.

InstaDB formula Comment
			
SELECT `Departments`.`Name`
  FROM `Departments`
  WHERE `Departments`.internal_id = `Employees`.`Department`
			
		      

Gets the name of the Department to which the Employee is assigned.

			
`Employees`.`Department` -> `Departments`.`Name`
			
		      

The same as the example from the first row, but uses the InstaDB arrow syntax instead of select.

			
SELECT `Emp`.`Phone number`
  FROM `Employees` AS `Emp`
  WHERE `Emp`.internal_id
    = (SELECT `Departments`.`Head`
         FROM `Departments`
         WHERE `Departments`.internal_id = `Employees`.`Department`)
			
		      

Finds the phone number of the head of the Department to which the Employee is assigned.

			
`Employees`.`Department` -> `Departments`.`Head`
  -> `Employees`.`Phone number`
			
		      

Exactly the same function as the example from the previous row, but uses the arrow syntax instead of select and subselect.

			
SELECT `Rooms`.`Floor`
  FROM `Rooms`
  WHERE `Rooms`.internal_id
  = (SELECT `Emp`.`Room`
      FROM `Employees` AS `Emp`
      WHERE `Emp`.internal_id
      = (SELECT `Departments`.`Head`
           FROM `Departments`
           WHERE `Departments`.internal_id
           = `Employees`.`Department`
        )
    )
			
		      

Finds the Department of the Employee, then it's head, then the Room, to which the head is assigned and finally the Floor on which the Room is located.

			
`Employees`.`Department` -> `Departments`.`Head`
  -> `Employees`.`Room` -> `Rooms`.`Floor`
			
		      

The same as the above example, but uses simplified arrow syntax.

			
SELECT `Contractors`.`E-mail`
  FROM `Contractors`
  WHERE `Contractors`.internal_id
  = (SELECT `Floors`.`Cleaning services company`
      FROM `Floors`
      WHERE `Floors`.internal_id
      = (SELECT `Rooms`.`Floor`
          FROM `Rooms`
          WHERE `Rooms`.internal_id
          = (SELECT `Emp`.`Room`
              FROM `Employees` AS `Emp`
              WHERE `Emp`.internal_id
              = (SELECT `Departments`.`Head`
                  FROM `Departments`
                  WHERE `Departments`.internal_id
                  = `Employees`.`Department`
                )
            )
        )
    )
			
		      

Follows the same sequence as in the above example, but extends it – finds the Cleaning services company responsible for the Floor and then it's e-mail address.

			
`Employees`.`Department` -> `Departments`.`Head`
  -> `Employees`.`Room` -> `Rooms`.`Floor`
  -> `Floors`.`Cleaning services company` -> `Contractors`.`E-mail`
			
		      

Once again the arrow syntax makes complex expression much easier for write (and read).

Aggregation Formulas

Aggregation functions are used to perform some mathematical operations on data from multiple record to obtain a single value as a result.

InstaDB lets to use SQL aggregation functions and the child sum expressions. The latter are simpler to use (it is possible to create aggregations without writing code, but the SQL aggregations are more generic and more powerful. A few examples of SQL aggregations are listed below.

InstaDB formula Comment
			
SELECT COUNT(1)
  FROM `Employees`
  WHERE `Employees`.`Department` = `Departments`.internal_id
			
		      

Calculates the number of Employees assigned tho the Department.

			
SELECT AVG(`Employees`.`Age`)
  FROM `Employees`
  WHERE `Employees`.`Department` = `Departments`.internal_id
			
		      

Counts the average age of Employees assigned to the Department.

			
SELECT SUM(`Sales`.`Transaction value`)
  FROM `Sales`
  WHERE `Sales`.`Employee` -> `Employees`.`Department`
  = `Departments`.internal_id
			
		      

Calculates the sum of values of transactions (Sales) performed by the Employees of the Department.

Note that the arrow syntax can be used as a part of other expressions.

Other SQL Formulas Examples

The examples in the table below are various formulas of mixed types. The purpose of them is to present the spectrum of opportunities given by InstaDB formulas, which are not accessible in standard MS SharePoint calculated field formulas.

InstaDB formula Comment
			
SELECT `Emp`.`Name`
  FROM `Employees` AS `Emp`
  WHERE `Emp`.`Sum of transaction values`
    = (SELECT MAX(`Employees`.`Sum of transaction values`)
       FROM `Employees`
       WHERE `Employees`.`Department` = `Employees`.internal_id)
			
		      

Finds the name of the Employee who has the highest sum of transaction values in the Department. The "Sum of transaction values" column may also be a dynamically calculated expression.

			
CONCAT(YEAR(`Orders`.`Date`),
       '/',
       LPAD(MONTH(`Orders`.`Date`), 2, '0'),
       '/',
       LPAD(IFNULL((SELECT COUNT(1)
                      FROM `Orders` AS `Ord`
                      WHERE YEAR(`Ord`.`Date`) = YEAR(`Orders`.`Date`)
                        AND MONTH(`Ord`.`Date`) = MONTH(`Orders`.`Date`)
                        AND `Ord`.internal_id < `Orders`.internal_id), 0) + 1, 4, '0')
           )
       )
			
		      

Calculates the signature of an order in the form YYYY/MM/XXXX, containing the year and the month of the order and the four digit sequential number of order in the month.

			
IF(`Tasks`.`Author` -> `Employees`.`E-mail` = PARAM.user_login
    OR `Tasks`.`Assigned to` -> `Employees`.`E-mail` = PARAM.user_login,
  1,
  '')
			
		      

Checks whether the current user (PARAM.user_login) is an author of the Task or the Employee assigned to it. Row access control rules often depend on similar formulas.

			
SELECT MAX(`Projects`.`Last task` -> `Tasks`.`Finished on`)
  FROM `Projects`
  WHERE `Projects`.`Company` = `Companies`.`id`
			
		      

Finds the time when the last task in all Projects assigned to the Company was finished. The "Last task" column of the Project is also dynamically calculated in another field formula in the Project table.

			
SELECT GROUP_CONCAT(CONCAT(`Persons`.`First name`,
                           ' ',
                           `Persons`.`Last name`, 
                           ' ',
                           `Persons`.`E-mail`),
                   '\r\n')
  FROM `Persons`
  WHERE `Persons`.`Company` = `Companies`.internal_id
			
		      

Concatenate names and e-mail addresses of all the Persons related to the Company into single string (every Person will be displayed in the separated line).

			
IF((SELECT COUNT(`Persons`.internal_id)
     FROM `Persons` AS `P`
     WHERE `P`.`Last name` = `Persons`.`Last name`
       AND `P`.`First name` = `Persons`.`First name`
       AND `P`.internal_id <> `Persons`.internal_id)
    > 0,
  1,
  '')
			
		      

Checks whether exists some Persons with the same names.

			
IF((SELECT `Borrowings`.`Status`
     FROM `Borrowings` 
     WHERE `Borrowings`.`Document` = `Documents`.internal_id
     ORDER BY `Borrowings`.internal_id DESC
     LIMIT 1) = 'borrowed',
  (SELECT `Borrowings`.`Borrowed by`
    FROM `Borrowings`
    WHERE `Borrowings`.`Document` = `Documents`.internal_id
    ORDER BY `Borrowings`.internal_id DESC
    LIMIT 1),
  '')
			
		      

If the Document is currently borrowed by someone, returns the id of that Person. This calculated column may be of the reference type, co the result would be a link to that's Person record.

Similarities between InstaDB and MS SharePoint

The examples below present the four main categories of formulas which are common for SharePoint and InstaDB. The formulas of both type are presented for comparison.

Conditional Formulas

The following formulas are used to test whether statements are true or false.

MS SharePoint InstaDB Comment
			
=[A]>[B]
			
		      
			
`My Table`.`A` > `My Table`.`B`
			
		      

Check whether value of column A is greater than value of column B.

			
=IF([A]<=[B], "Correct", "Not correct")
			
		      
			
IF(`My Table`.`A` <= `My Table`.`B`,
  'Correct',
  'Not correct')
			
		      

Whether value of column A is less or equal to value of column B the result is "Correct". Otherwise it is "Not correct".

			
=OR([A]>[B], [A]<[C])
			
		      
			
`My Table`.`A` > `My Table`.`B`
    OR `My Table`.`A` < `My Table`.`C`
			
		      

Return true when value of column A is greater than value of column B or when value of column A is smaller then value of column C.

			
=IF(AND([A]>[B], [A]<[C]), "Correct", "Not correct")
			
		      
			
IF(`My Table`.`A` > `My Table`.`B`
    AND `My Table`.`A` < `My Table`.`C`,
  'Correct',
  'Not correct')
			
		      

As in the above example, but returns "Correct" or "Not correct" string instead of true/false.

Date And Time Formulas

The below examples show common date and time operations and manipulations.

MS SharePoint InstaDB Comment
			
=[A]+[B]
			
		      
			
DATE_ADD(`My Table`.`A`, INTERVAL `My Table`.`B` DAY)
			
		      

Adds B days to the date A (B must be an integer).

			
=DATE(YEAR([A]),MONTH([A])+[B],DAY([A]))
			
		      
			
DATE_ADD(`My Table`.`A`, INTERVAL `My Table`.`B` MONTH)
			
		      

Adds B months to the date A (B must be an integer).

			
=DATEDIF([A], [B],"d")
			
		      
			
DATEDIFF(`My Table`.`B`, `My Table`.`A`)
			
		      

Calculates the number of days between date A and B.

			
=TEXT([B]-[A],"h:mm:ss")
			
		      
			
TIMEDIFF(`My Table`.`B`, `My Table`.`A`)
			
		      

Calculates the number of hours, minutes and seconds between two times.

Mathematical Formulas

The most commonly used mathematical functions are presented in the below table.

MS SharePoint InstaDB Comment
			
=[A]+[B]
			
		      
			
`My Table`.`A` + `My Table`.`B`
			
		      

Addition

			
=[A]-[B]
			
		      
			
`My Table`.`A` - `My Table`.`B`
			
		      

Subtraction

			
=[A]*[B]
			
		      
			
`My Table`.`A` * `My Table`.`B`
			
		      

Multiplication

			
=[A]/[B]
			
		      
			
`My Table`.`A` / `My Table`.`B`
			
		      

Division

			
=POWER([A], [B])
			
		      
			
POW(`My Table`.`A`, `My Table`.`B`)
			
		      

Exponentiation

			
=ROUND([A],0)
			
		      
			
ROUND(`My Table`.`A`)
			
		      

Rounding

			
=([A]+123)/[B]
			
		      
			
(`My Table`.`A` + 123) / `My Table`.`B`
			
		      

Combined mathematical operations

Text Formulas

Some common text manipulation functions are gathered in the below table.

MS SharePoint InstaDB Comment
			
=UPPER([A])
			
		      
			
UPPER(`My Table`.`A`)
			
		      

Changes text to uppercase.

			
=LOWER([A])
			
		      
			
LOWER(`My Table`.`A`)
			
		      

Changes text to lowercase.

			
=[A]&"."&[B]
			
		      
			
CONCAT(`My Table`.`A`, '.', `My Table`.`B`)
			
		      

Combines given strings.

			
=[A]&" pieces of "&[B]&" has been sent."
			
		      
			
CONCAT(`My Table`.`A`, ' pieces of ',`My Table`.`B`, ' has been sent.')
			
		      

More complex example of strings concatenation.

			
=LEFT([A],LEN([B]))
			
		      
			
LEFT(`My Table`.`A`, LENGTH(`My Table`.`B`))
			
		      

Extract a substring of length equal the length of column B value from the value of column A, starting from the left site.

			
=RIGHT([A], LEN([B])-2)
			
		      
			
RIGHT(`My Table`.`A`, LENGTH(`My Table`.`B`) - 2)
			
		      

Extract a substring of length equal the length of column B value diminished by 2 from the value of column A, starting from the right site.

			
=TRIM([A])
			
		      
			
TRIM(`My Table`.`A`)
			
		      

Removes leading and trailing spaces.

			
=REPT("-",10)
			
		      
			
REPEAT('-', 10)
			
		      

Repeats the dash ("-") character 10 times.

Final notes

Calculated fields as a subject of other formulas

InstaDB formulas have no length limit and in some cases can be extremely complex and difficult to read. Fortunately InstaDB lets for using any calculated field formula in any other formula.

Splitting the complex formulas into separate columns makes the complex expressions much simpler. It makes the code much more readable and let for reuse of some parts (columns) in many different formulas.

Reference as a result of a formula

The type of a formula does not depend on it's content. It means that the resulting value can be converted to any type (if possible), including the reference.

As the result it is relatively easy to obtain a column with dynamically calculated reference, what is extremely useful in many situations.

InstaDB provides a lot of functionalities not accessible in the MS SharePoint:

InstaDB field formulas (here called the expression columns) can use almost complete SQL syntax and SQL functions. Plus the aggregate functions generator. The result of an expression can be of any data type, including a reference!

InstaDB do not use SharePoint lists but the real multi-table relational data model. That increase the efficiency and makes implementation a lot easier.

With InstaDB you can in a single form enter data of a record and data of records which refer to it. For example you may easily create the order form, which contains forms for any number of order positions.

SharePoint reference selection widget doesn't work if the number of target records exceed 5000 (unless there is an index on searched column). InstaDB have no such limitation and the reference selection widget can process millions of target records with ease.

Automatically added filters allow for filtering with every field of the table. And the reference field filter can be expanded to the set of fields of target table. There is no limit of the depth of the by-the-reference filtering.

InstaDB offers fully dynamic access control, based not only on the individual user permissions but also on the state of a record. You can use SQL expressions to determine the access level for every single record. You can define access level for every single column of every table.

InstaDB has built-in easy to use SharePoint workflow management system. For every state you can define the following states, entry conditions and auto-calculated values.

InstaDB Pivots can be easily created by any user for any (accessible) data. A site collection administrator can create and save ready to use pivot reports for other users.

For better access management InstaDB provides (as an option) additional in-application roles. You can assign every user any set of roles, while the roles can determine range of permissions and available actions.

InstaDB engine provides it's own mechanism for attachments management. It solves the common problem of files with the same names. It is possible to upload any number of files with the same name and InstaDB will always be able to distinguish between them.

TRY IT NOW

Check InstaDB in action.

Register for free