Trigger, routing and blocking conditions
Conditions in Auron Omni are SQL expressions. You will find conditions when creating:
- Triggers. To automatically process messages;
- Routing rules. To route messages to specific channels;
- Blocking rules. To block outgoing messages;
Conditions always define which messages a rule applies to.
Message views also use SQL expressions. In this case they are called filters. The same rules apply to these filters as well.
This document explains the main rules for writing conditions and filters.
Basic syntax and operators
If you are not familiar with SQL, here is a short overview of the basic syntax and operators.
SQL requires strings to be written using single quotes. Boolean values are 1 for true and 0 for false.
All SQL operators can be used. Find a full list of operators here.
A short list of useful operators:
| Operator | Example | Description | ||
|---|---|---|---|---|
| = | ToAddress = '+316211223344' | Equal to | ||
| < | Retries < 3 | Less than | ||
| > | Retries > 3 | Greater than | ||
| <> | IsDeliveryReport <> 0 | Not equal to | ||
| + | Retries + 1 < 3 | Addition for numbers or concatenation for strings | ||
| - | Retries - 1 < 3 | Subtraction | ||
| % | ID % 5 = 0 | Modulo | ||
| LIKE | Body LIKE 'QUIZ%' | Compare a string against a pattern. '%' means zero, one or multiple characters. '_' means 1 character | ||
| IN | ToAddress IN ('+31611223344', '+31611223355', '+31611223366') | Test if a field compares to one value out of the specified list | ||
| AND | ToAddress = 'email@yourdomain.com' AND FromAddress = 'email@yourdomain.com' | Combine expressions such that the expression is true if all parts of the expression are true | ||
| OR | ToAddress = 'sales@yourdomain.com' OR ToAddress = 'contact@yourdomain.com | Combine expressions such that the expressions is true if one part of the expression is true | ||
| NOT | Body NOT LIKE 'QUIZ%' | Reverse the expression | ||
| EXISTS | EXISTS (SELECT ID FROM Channels WHERE ID='SMPP1' AND Enabled <> 0) | True if a sub-query contains any rows |
Functions
You can also use SQL functions in your expressions. Find a full list of functions here.
A short list of useful functions:
| Function | Example | Description | ||
|---|---|---|---|---|
| SYSUTCDATETIME() | LastUpdate > DATEADD(d, -7, SYSUTCDATETIME()) | Returns the current date/time in UTC. The Auron Omni database records all date/time values in UTC. | ||
| DATEADD(datepart, number, date) | LastUpdate > DATEADD(d, -7, SYSUTCDATETIME()) | Add time to a given date and return the new value. The datapart can be: n - Minutes hh - Hours d - Days m -Months yy - Years The number can be negative to subtract. The date can be a data field or an expression that returns a date. | ||
| RAND() | RAND() < 0.5 | Generate a random number between 0 and 1 | ||
| CONVERT(datatype, expression) | CONVERT(INT, RAND() * 100) % 10 = 1 | Converts the expression into the specified datatype. Use it to convert date to string or floats to integers so you can use the modulo operator. | ||
| fnRouteProportional(randnr, lanes) | fnRouteProportional(RAND(ID), 4) | Returns a random number between 0 and lanes - 1. This function is intended to do proportional routing inside routing rules. When called as per example the returned number will be the same for the same ID. | ||
| fnNumQueued(channelid) | fnNumQueued(ChannelID) | Returns the number of queued messages on a given channel. |