☰ Docs map

# Examples of InstaDB expression columns

InstaDB provides full SQL support, what makes possible to use new kinds of formulas.

Here we present some very basic examples of InstaDB expression column formulas divided in categories.

### Conditional Formulas

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

```			```
`My Table`.`A` > `My Table`.`B`
```
```

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

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

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

```			```
DATE_ADD(`My Table`.`A`, INTERVAL `My Table`.`B` DAY)
```
```

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

```			```
DATE_ADD(`My Table`.`A`, INTERVAL `My Table`.`B` MONTH)
```
```

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

```			```
DATEDIFF(`My Table`.`B`, `My Table`.`A`)
```
```

Calculates the number of days between date A and B.

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

```			```
`My Table`.`A` + `My Table`.`B`
```
```

```			```
`My Table`.`A` - `My Table`.`B`
```
```

Subtraction

```			```
`My Table`.`A` * `My Table`.`B`
```
```

Multiplication

```			```
`My Table`.`A` / `My Table`.`B`
```
```

Division

```			```
POW(`My Table`.`A`, `My Table`.`B`)
```
```

Exponentiation

```			```
ROUND(`My Table`.`A`)
```
```

Rounding

```			```
(`My Table`.`A` + 123) / `My Table`.`B`
```
```

Combined mathematical operations

### Text Formulas

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

```			```
UPPER(`My Table`.`A`)
```
```

Changes text to uppercase.

```			```
LOWER(`My Table`.`A`)
```
```

Changes text to lowercase.

```			```
CONCAT(`My Table`.`A`, '.', `My Table`.`B`)
```
```

Combines given strings.

```			```
CONCAT(`My Table`.`A`, ' pieces of ',`My Table`.`B`, ' has been sent.')
```
```

More complex example of strings concatenation.

```			```
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(`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(`My Table`.`A`)
```
```

```			```
REPEAT('-', 10)
```
```

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

## InstaDB formulas not present in MS SharePoint

The tables below contain examples of formulas which are not accessible in the plain MS SharePoint, but can be used in InstaDB.

### Reference Formulas

The reference formulas are used to achieve data from records pointed by the references. InstaDB provides two kinds of reference formulas syntax: classic SELECT constructions and the arrows (`->`).

All the formulas are executed from the perspective of a record (they are context dependent).

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

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

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

```			```
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`),
'/',
'/',
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.

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

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

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