# Learn expressions for new Novulo users with a SQL background

**URL:** <https://community.novulo.com/t/learn-expressions-for-new-novulo-users-with-a-sql-background/610>\
**Category:** \[Q&A\] General\
**Tags:** getting-started\
**Created:** [January 9, 2025, 1:13pm UTC](https://community.novulo.com/t/learn-expressions-for-new-novulo-users-with-a-sql-background/610 "2025-01-09T13:13:10Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![hans\_van\_uden](https://avatars.discourse-cdn.com/v4/letter/h/dbc845/32.png) [@hans\_van\_uden](https://community.novulo.com/u/hans_van_uden)\
**Post date:** [January 9, 2025, 1:13pm UTC](https://community.novulo.com/t/learn-expressions-for-new-novulo-users-with-a-sql-background/610/1 "2025-01-09T13:13:10Z")

</div>

**Introduction**  
Learning novulo expressions when you have a background in SQL is explained in this article.  
-The SQL shown is executed in SQL server management studio. (You need special permission to use the SQL server management studio on the novulo database).  
-The expressions are executed on the expression debugger that is available on every novulo application

 ![image](https://canada1.discourse-cdn.com/flex008/uploads/novulo/original/2X/f/f97e102a17cd41b8c561283f18b5c2f99305eae2.png)

**Information about the example data used**  
This is about an example with 3 tables

1. Sales with the fields  
-id: the primary key of the sales  
-createdat: when is the sales created  
-number: the number of the sales  
-description of the sales  
-dummy\_customer: the reference to the customer  
-contact\_person: the contact\_person at the customer

2. Contacts  
-id: the primary key of the contact  
-contact\_name: descriptive name

3. ConnectionContacts  
-id: the primary key  
-connection\_from: the foreign key to the customer (organization)  
-connection\_to: The foreign key to the contact\_person

**Example 1. The sales with a filled in customer of today**  
select top 100 id, number, description, dummy\_customer, contact\_person  
from sales  
where not dummy\_customer is null and createdat \> ‘2025-01-9’ order by id desc

Result  
 ![image](https://canada1.discourse-cdn.com/flex008/uploads/novulo/original/2X/e/e1e86fc160b0fead4681fbec8ca5891d29292032.png)

The corresponding expression is  
{  
sales,  
\<id,number,description,dummy\_customer,contact\_person\>,  
boolean:and([createdat.isgreater(date:now()),dummy\_customer.isnotnull()]),  
id.desc  
}

The curly braces indicate that it is about a table. Sales in this example  
The fields specified between the \< and \> sign indicate the fields that will be retrieved.  
Behind the retrieved fields there is the where expression. In this case an AND. The AND is a set of 2 expressions. The set is indicated by [and]. The 2 expressions are seperated by a comma.  
Behind the Where expression, the sorting is shown. On id descending.

Result  
 ![image](https://canada1.discourse-cdn.com/flex008/uploads/novulo/original/2X/1/191ee86aa61cb29bf90fe9e130d69cf798c0026c.png)

**Example 2. The information from the customer**  
in SQL:  
select id, contact\_name from contacts where id in (987)  
 ![image](https://canada1.discourse-cdn.com/flex008/uploads/novulo/original/2X/6/688a52d0cc6ea576d34733c9f08748743774386d.png)

The corresponding expression is:  
{  
contacts,  
\<id,contact\_name\>,  
id.in([987])  
}

Result  
 ![image](https://canada1.discourse-cdn.com/flex008/uploads/novulo/original/2X/9/91aca58f5497ce9c3a2c78282ca6ad1ba462f8b7.png)

**Example 3. The sales with the name of the customer filled in**  
in SQL you need to join the contacts table:  
select number, description, contacts.contact\_name from  
sales inner join contacts on sales.dummy\_customer = contacts.id  
where dummy\_customer=987  
order by sales.id desc

Result  
 ![image](https://canada1.discourse-cdn.com/flex008/uploads/novulo/original/2X/7/75ed4ffb8b1501b8beecf4f59deb27fe912b271e.png)

The corresponding expression is  
{  
sales,  
\<number, description, dummy\_customer.contact\_name\>,  
dummy\_customer.id.equals(987),  
id.desc  
}  
In the expression language the join is created automatically if you enter the dot after dummy\_customer and select the contact\_name field

Result  
 ![image](https://canada1.discourse-cdn.com/flex008/uploads/novulo/original/2X/9/94397212c19edf36d15376fe2afa7fe595055fc5.png)

**Example 4. The contactpersoon is defined in a in between record**  
**with a foreign key to the customer and the contact person**  
in SQL:  
select id, connection\_from, connection\_to  
from connectioncontacts  
where connection\_from = 987  
order by id desc

Result  
 ![image](https://canada1.discourse-cdn.com/flex008/uploads/novulo/original/2X/0/06af66f087ef950525ec0c84dee4a531c2a411b2.png)

The corresponding expression  
{  
connectioncontacts,  
\<id, connection\_from, connection\_to\>,  
connection\_from.id.equals(987),  
id.desc  
}

Result:  
 ![image](https://canada1.discourse-cdn.com/flex008/uploads/novulo/original/2X/b/b7dc68db7c8c7889392c3160b03fef89087cc38a.png)

**Example 5. The corresponding names for the in between record**  
in SQL:  
select cc.id, connection\_from, from\_c.contact\_name, connection\_to, to\_c.contact\_name  
from connectioncontacts as cc  
inner join contacts as from\_c on connection\_from = from\_c.id  
inner join contacts as to\_c on connection\_to = to\_c.id  
where connection\_from = 987 order by cc.id desc

Result:  
 ![image](https://canada1.discourse-cdn.com/flex008/uploads/novulo/original/2X/a/acd342f41cb6a1a82f8cc4fea56d998435df2b66.png)

The corresponding expression  
{  
connectioncontacts,  
\<id, connection\_from, connection\_from.contact\_name,  
connection\_to, connection\_to.contact\_name\>,  
connection\_from.id.equals(987),  
id.desc  
}

Result:  
 ![image](https://canada1.discourse-cdn.com/flex008/uploads/novulo/original/2X/a/a4f9e69c605a9745c435c060f017b4e007d714cb.png)

**6. The name of the customer and the name of the contactperon (even if it has not been assigned).**  
in SQL you need the left outer join:  
select number, description, contacts.contact\_name, p.contact\_name from sales  
inner join contacts on sales.dummy\_customer = contacts.id  
left outer join connectioncontacts as cc on cc.id=contact\_person  
left outer join contacts as p on cc.connection\_to = p.id  
where dummy\_customer=987

Result  
 ![image](https://canada1.discourse-cdn.com/flex008/uploads/novulo/original/2X/d/d3775caaa0a923aade27c379e9d854a5760083d0.png)

The corresponding expression  
{  
sales,  
\<number, description, dummy\_customer.contact\_name,  
contact\_person.connection\_to.contact\_name\>,  
dummy\_customer.id.equals(987),  
id.desc  
}

Result  
 ![image](https://canada1.discourse-cdn.com/flex008/uploads/novulo/original/2X/d/d3775caaa0a923aade27c379e9d854a5760083d0.png)

In the expression you do not have to worry about the join. When the contact\_person field is empty the contact\_name is represented as a NULL.