Learn expressions for new Novulo users with a SQL background

[hans_van_uden]

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

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

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

Example 2. The information from the customer
in SQL:
select id, contact_name from contacts where id in (987)

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

Result

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

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

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

The corresponding expression
{
connectioncontacts,
<id, connection_from, connection_to>,
connection_from.id.equals(987),
id.desc
}

Result:

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:

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:

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

The corresponding expression
{
sales,
<number, description, dummy_customer.contact_name,
contact_person.connection_to.contact_name>,
dummy_customer.id.equals(987),
id.desc
}

Result

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.