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.
The examples below present formulas which are common in relational databases but are missing in the plain MS SharePoint.
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 


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

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

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

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

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. 

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

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 email address. 

Once again the arrow syntax makes complex expression much easier for write (and read). 
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 


Calculates the number of Employees assigned tho the Department. 

Counts the average age of Employees assigned to the Department. 

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. 
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 


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. 

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. 

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. 

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. 

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

Checks whether exists some Persons with the same names. 

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. 
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.
The following formulas are used to test whether statements are true or false.
MS SharePoint  InstaDB  Comment 



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


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


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. 


As in the above example, but returns "Correct" or "Not correct" string instead of true/false. 
The below examples show common date and time operations and manipulations.
MS SharePoint  InstaDB  Comment 



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


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


Calculates the number of days between date A and B. 


Calculates the number of hours, minutes and seconds between two times. 
The most commonly used mathematical functions are presented in the below table.
MS SharePoint  InstaDB  Comment 



Addition 


Subtraction 


Multiplication 


Division 


Exponentiation 


Rounding 


Combined mathematical operations 
Some common text manipulation functions are gathered in the below table.
MS SharePoint  InstaDB  Comment 



Changes text to uppercase. 


Changes text to lowercase. 


Combines given strings. 


More complex example of strings concatenation. 


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


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. 


Removes leading and trailing spaces. 


Repeats the dash ("") character 10 times. 
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.
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 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 multitable 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 bythereference 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 builtin easy to use SharePoint workflow management system. For every state you can define the following states, entry conditions and autocalculated 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 inapplication 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.